SQL SERVER连接MySQL

-- MySQL

 

mysql> create table tb(id int,data varchar(20),

            chs_data varchar(50) character set 'gbk') character set 'latin1';

Query OK, 0 rows affected (0.09 sec)

 

mysql> insert into tb values(1,'mysql','小梁爱兰儿');

Query OK, 1 row affected (0.05 sec)

 

mysql> insert into tb values(2,'mysql','小梁爱兰儿');

Query OK, 1 row affected (0.00 sec)

 

mysql> select *from tb;

+------+-------+------------+

| id   | data  | chs_data   |

+------+-------+------------+

|    1 | mysql | 小梁爱兰儿|

|    2 | mysql | 小梁爱兰儿|

+------+-------+------------+

2 rows in set (0.00 sec)

 

 

--SQL SERVER

CREATE TABLE tb(id int,data VARCHAR(20),chs_data NVARCHAR(20));

INSERT tb VALUES(1,'sql server',N'小梁爱兰儿');

INSERT tb VALUES(2,'sql server',N'小梁爱兰儿');

GO

 

EXEC sp_addlinkedserver 'MySQL','','MSDASQL','My_SQL';

EXEC sp_addlinkedsrvlogin 'MySQL','false','sa','root','liangck';

GO

 

SELECT *

FROM OPENQUERY(MySQL,'SELECT * FROM mytest.tb') AS A

    JOIN tb AS B

 ON A.id=B.id;

 

GO

EXEC sp_dropserver 'MySQL','droplogins';

DROP TABLE tb;

 

 

/*

id          data             chs_data             id          data               chs_data

----------- ------------ ------------------- ----------- -------------------- --------------

1           mysql            小梁爱兰儿          1         sql server           小梁爱兰儿

2           mysql            小梁爱兰儿          2         sql server           小梁爱兰儿

 

(2 行受影响)

*/