sqlserver 从mysql 提取数据报错？

Question

select ID, content1
 from openquery([odbc_link],                                    
 'SELECT id,cast( uncompress(content1) as char) as content1 FROM `dbname`.`tablename` 
 where id in(10633560)')

错误：[MySQL][ODBC 5.1 Driver][mysqld-5.1.45-mysql-amoeba-proxy-2.1.0-RC5]Unknown failure when converting character from server character set

[odbc_link]是连接服务器。

不是所有的数据都报错，只有少数的行会报这个错，而且这样的行的content1 的大小不大  不知道是什么错误，请指教

Answer 1

What's character set of that column in mysql?

Answer 2

utf-8  ，可是字符集  我在odbc 里都改成跟mysql 一致的了  还是不行；

这个列content1 是blob类型  我在mysql里取都可以  就是提取到sqlserver 这边 有几行就报这个错。

Answer 3

EXEC master.dbo.sp_addlinkedserver @server = N'MYSQLLINK', @srvproduct = N'Mysql', @provider = N'MSDASQL', @provstr = N'Driver={MySQL ODBC 5.1 Driver};Server=SZLPT1314;           Database=wison ;User=root;Password=wison;'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MYSQLLINK',@useself = N'False',@locallogin = N'sa',@rmtuser = N'root',@rmtpassword = N'wison'

之后就可以使用 

SELECT * FROM OPENQUERY (MYSQLLINK, 'SELECT * FROM wisontest.sssss')

来查询mysql数据库中的数据了．

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 4

但是奇怪的是，当我执行

INSERT INTO OPENQUERY (MYSQLLINK, ' SELECT * FROM wisontest.sssss') SELECT 1,'Wison'

的时候报错，

OLE DB provider "MSDASQL" for linked server "MYSQLLINK" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.1.60-community-log]Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'".

Msg 7343, Level 16, State 2, Line 1

The OLE DB provider "MSDASQL" for linked server "MYSQLLINK" could not INSERT INTO table "[MSDASQL]". 

还需要查询下原因

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 5

但是奇怪的是，当我执行

INSERT INTO OPENQUERY (MYSQLLINK, ' SELECT * FROM wisontest.sssss') SELECT 1,'Wison'

的时候报错，

OLE DB provider "MSDASQL" for linked server "MYSQLLINK" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.1.60-community-log]Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'".

Msg 7343, Level 16, State 2, Line 1

The OLE DB provider "MSDASQL" for linked server "MYSQLLINK" could not INSERT INTO table "[MSDASQL]". 

还需要查询下原因

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

发错地方了吧

Answer 6

utf-8  ，可是字符集  我在odbc 里都改成跟mysql 一致的了  还是不行；

这个列content1 是blob类型  我在mysql里取都可以  就是提取到sqlserver 这边 有几行就报这个错。

What's data type of the column in sql table? Is it unicode type? Tried cast it to nchar?

Answer 7

是想把读取的数据查到一个表里，可是读都报错，转换成NCHAR 也不行   是个别行，谢谢恢复。