先安装好MYSQL ODBC.然后配置一个连接上MYSQL的数据源.EXEC sp_addlinkedserver 'MySQL','','MSDASQL','数据源名字'; EXEC sp_addlinkedsrvlogin 'MySQL','false','sa','root','';SELECT * FROM OPENQUERY(MySQL,'SELECT * FROM test.tb') AS A JOIN tb AS B ON A.id=B.id
按照你的方法出现这个错误服务器: 消息 7399,级别 16,状态 1,行 1 OLE DB 提供程序 'MSDASQL' 报错。 [OLE/DB provider returned message: [Microsoft][ODBC 驱动程序管理器] 未发现数据源名称并且未指定默认驱动程序] OLE DB 错误跟踪[OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]。
-- mysql mysql> CREATE TABLE tb(id int,data VARCHAR(20)); Query OK, 0 rows affected (0.00 sec)mysql> INSERT tb VALUES(1,'mysql'); Query OK, 1 row affected (0.00 sec)mysql> INSERT tb VALUES(2,'mysql'); Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM tb; +------+-------+ | id | data | +------+-------+ | 1 | mysql | | 2 | mysql | +------+-------+ 2 rows in set (0.00 sec)-- sql serverCREATE TABLE tb(id int,data VARCHAR(20)); INSERT tb VALUES(1,'sql server'); INSERT tb VALUES(2,'sql server'); GOEXEC sp_addlinkedserver 'MySQL','','MSDASQL','MySQL'; EXEC sp_addlinkedsrvlogin 'MySQL','false','sa','root','liangck'; GOSELECT * 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 id data ----------- -------------------- ----------- -------------------- 1 mysql 1 sql server 2 mysql 2 sql server(2 row(s) affected)*/
EXEC sp_addlinkedserver 'MySQL','','MSDASQL','mysqlsource1'; EXEC sp_addlinkedsrvlogin 'MySQL','false','sa','root','123456'; mysqlsource1 是我的数据源(测试通过的)test 是MYSQL中,zx数据库中的表 SELECT * FROM OPENQUERY(MySQL,'SELECT * FROM mysqlsource1.zx.test ') 执行出现 服务器: 消息 7399,级别 16,状态 1,行 1 OLE DB 提供程序 'MSDASQL' 报错。 [OLE/DB provider returned message: [MySQL][ODBC 5.1 Driver][mysqld-5.0.22-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.test' at line 1] OLE DB 错误跟踪[OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ]。
SELECT * FROM OPENQUERY(MySQL,'SELECT * FROM zx.test')
http://blog.csdn.net/sdhdy/archive/2009/05/25/4215311.aspx
估计要用到这个:openrowset/opendatasource
EXEC sp_addlinkedsrvlogin 'MySQL','false','sa','root','';SELECT *
FROM OPENQUERY(MySQL,'SELECT * FROM test.tb') AS A
JOIN tb AS B
ON A.id=B.id
OLE DB 提供程序 'MSDASQL' 报错。
[OLE/DB provider returned message: [Microsoft][ODBC 驱动程序管理器] 未发现数据源名称并且未指定默认驱动程序]
OLE DB 错误跟踪[OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]。
Query OK, 0 rows affected (0.00 sec)mysql> INSERT tb VALUES(1,'mysql');
Query OK, 1 row affected (0.00 sec)mysql> INSERT tb VALUES(2,'mysql');
Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM tb;
+------+-------+
| id | data |
+------+-------+
| 1 | mysql |
| 2 | mysql |
+------+-------+
2 rows in set (0.00 sec)-- sql serverCREATE TABLE tb(id int,data VARCHAR(20));
INSERT tb VALUES(1,'sql server');
INSERT tb VALUES(2,'sql server');
GOEXEC sp_addlinkedserver 'MySQL','','MSDASQL','MySQL';
EXEC sp_addlinkedsrvlogin 'MySQL','false','sa','root','liangck';
GOSELECT *
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 id data
----------- -------------------- ----------- --------------------
1 mysql 1 sql server
2 mysql 2 sql server(2 row(s) affected)*/
2.然后在sql中链接sql 2000 SQL code--创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' --查询示例
select * from ITSV.数据库名.dbo.表名 ------------------------------------------------------------------------------------------------
EXEC sp_addlinkedsrvlogin 'MySQL','false','sa','root','123456';
mysqlsource1 是我的数据源(测试通过的)test 是MYSQL中,zx数据库中的表 SELECT * FROM OPENQUERY(MySQL,'SELECT * FROM mysqlsource1.zx.test ') 执行出现
服务器: 消息 7399,级别 16,状态 1,行 1
OLE DB 提供程序 'MSDASQL' 报错。
[OLE/DB provider returned message: [MySQL][ODBC 5.1 Driver][mysqld-5.0.22-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.test' at line 1]
OLE DB 错误跟踪[OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ]。
SELECT * FROM OPENQUERY(MySQL,'SELECT * FROM zx.test')