参考:--连接mysql安装MySQL的ODBC驱动MyODBC1、为MySQL建立一个ODBC系统数据源,例如:选择数据库为test ,数据源名称为 myDSN2、建立链接数据库 EXEC sp_addlinkedserver @server = 'MySQLTest', @srvproduct='MySQL', @provider = 'MSDASQL', @datasrc = 'myDSN' GO EXEC sp_addlinkedsrvlogin @rmtsrvname='MySqlTest',@useself='false',@locallogin='sa',@rmtuser='mysql的用户名',@rmtpassword='mysql的密码'3、查询数据SELECT * FROM OPENQUERY (MySQLTest ,'select * from 表' )--来源网络
Select, Insert, Delete都执行没有问题,现在是请教如何执行存储,谢谢
用调用call 直接执行truncate呢
SELECT * FROM OPENQUERY (srv_lnk,'call Table2') 或者SELECT * FROM OPENQUERY (srv_lnk,'truncate table cc_data.sa.tblbacklog_sts1')报错相同如下: Cannot process the object "call Table2". The OLE DB provider "MSDASQL" for linked server "srv_lnk" indicates that either the object has no columns or the current user does not have permissions on that object. 不过应该不是权限的问题,因为在MySQl Query中可以执行call table2或者truncate table tblbacklog_sts1都是成功的. 不知道我这两个执行方式对不对,或者还是有其他执行方式
我在看别人发的执行存储的方法是exec srv_lnk.cc_data.sa.Table2就可以的,可是我的报错信息中 OLE DB provider "MSDASQL" for linked server "srv_lnk" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.5.16-log]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 '?=call "cc_data"."sa"."Table2";1' at line 1". 不知道为什么在?=call "cc_data"."sa"."Table2"后面多了个 ;1
链接数据库不能直接执行call table2(), 报语法错误。我尝试将存储过程改为 BEGIN Truncate table tblbacklog_sts1; select * from tblbacklog_sts1; END 然后继续用SELECT * FROM OPENQUERY (srv_lnk,'truncate table cc_data.sa.tblbacklog_sts1')来执行,成功! 感谢benluobobo的帮忙!
EXEC sp_addlinkedserver @server = 'MySQLTest', @srvproduct='MySQL', @provider = 'MSDASQL', @datasrc = 'myDSN'
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname='MySqlTest',@useself='false',@locallogin='sa',@rmtuser='mysql的用户名',@rmtpassword='mysql的密码'3、查询数据SELECT * FROM OPENQUERY (MySQLTest ,'select * from 表' )--来源网络
Cannot process the object "call Table2". The OLE DB provider "MSDASQL" for linked server "srv_lnk" indicates that either the object has no columns or the current user does not have permissions on that object. 不过应该不是权限的问题,因为在MySQl Query中可以执行call table2或者truncate table tblbacklog_sts1都是成功的.
不知道我这两个执行方式对不对,或者还是有其他执行方式
OLE DB provider "MSDASQL" for linked server "srv_lnk" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.5.16-log]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 '?=call "cc_data"."sa"."Table2";1' at line 1". 不知道为什么在?=call "cc_data"."sa"."Table2"后面多了个 ;1
BEGIN
Truncate table tblbacklog_sts1;
select * from tblbacklog_sts1;
END
然后继续用SELECT * FROM OPENQUERY (srv_lnk,'truncate table cc_data.sa.tblbacklog_sts1')来执行,成功!
感谢benluobobo的帮忙!