sql server 连接 mysql

SP_CONFIGURE 'show advanced options',1 
GO 
RECONFIGURE 
GO 
-- 为开启为关闭 
SP_CONFIGURE 'Ad Hoc Distributed Queries',1 
GO 
RECONFIGURE 
GO 
SP_CONFIGURE 'show advanced options',0 
GO 
RECONFIGURE


SELECT * FROM OPENROWSET('localhost','luanping';'root';'123456',
   'SELECT * FROM luanping.a_user') 



select * from OPENDATASOURCE('SQLOLEDB','server=127.0.0.1;User ID=root;Password=123456')





EXEC sp_addlinkedserver @server = 'MySqlTest3', @srvproduct='MySQL', @provider = 'MySQL ODBC 5.1 Driver', @datasrc = 'luanping'
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname='MySqlTest3',@useself='false',@locallogin='sa',@rmtuser='root',@rmtpassword='123456'
SELECT * FROM OPENQUERY(MySqlTest, 'select * from book ' )


--连接mysql


安装MySQL的ODBC驱动MyODBC


1、为MySQL建立一个ODBC系统数据源，例如：选择数据库为test ,数据源名称为 myDSN


2、建立链接数据库
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 表' )


Insert openquery(MySqlTest, 'select * from a_user') (id,user_name,pwd) values(16,'齐达内','99999')