MSSQL和MYSQL直接链接:
先去mysql官网下载驱动:
附带一个链接:http://download.youkuaiyun.com/detail/handsome1234/9485209
32 mysql-connector-odbc-5.3.6-win32.msi
64 mysql-connector-odbc-5.3.6-winx64.msi
安装之后
-- 方法一、在系统odbc数据源中预先添加一个DSN名为testmysql的数据源,提前在电脑上安装mysqlodbc驱动,并配置好dsn
EXEC sp_addlinkedserver @server = 'testmysql11', @srvproduct='MySQL', @provider = 'MSDASQL', @datasrc = 'testmysql'
EXEC sp_addlinkedsrvlogin @rmtsrvname='testmysql11',@useself='false',@locallogin='sa',@rmtuser='your user id',@rmtpassword='1234'
SELECT * FROM OPENQUERY(testmysql11, 'select * from data_account_address ' )
方法二、
EXEC sp_addlinkedserver
@server='MySQLTestKKK',
@srvproduct = 'AAAA_BBB_db_main', --MYSQL的这里是数据库名,貌似填错也没关系
@provider = 'MSDASQL',
@datasrc = NULL,
@location = null,
@provstr = 'DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=youhostname.mysql.rds.aliyuncs.com;DATABASE=your db;UID=root;PORT=3306;',---MySQL ODBC 5.3 Unicode Driver 这里是变化的,应该是随着,安装驱动版本的不同,在ODBC里显示的名称不同,稍微有点变化,但是要以ODBC显示出的默认名称为准。否则有可能报错“OLE DB 访问接口 "MSDASQL" 返回了消息 "[Microsoft][ODBC 驱动程序管理器] 未发现数据源名称并且未指定默认驱动程序"。”
@catalog = NULL
--HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\MySQL ODBC 5.1 Driver
go
EXEC sp_addlinkedsrvlogin
@rmtsrvname='MySQLTestKKK',
@useself='false',
@rmtuser='your user id',
@rmtpassword='1234';
测试:
SELECT * FROM OPENQUERY(MySQLTestKKK, 'select * from data_account_address ' )
SELECT *
FROM OPENQUERY(Servername,'select * from DBName.tablename')