SQL Server 2008通过LinkServer连接MySQL

SQL Server 2008中需要访问MySQL的数据，可以通过调用MySQL的ODBC驱动，在SQL Server中添加LinkServer的方式实现。 步骤： 1.从MySQL网站下载最新的MySQL ODBC驱动：http://www.mysql.com/downloads/connector/odbc/， 2.在SQL Server所在的服务器安装，并在

SQL Server 2008中需要访问MySQL的数据，可以通过调用MySQL的ODBC驱动，在SQL Server中添加LinkServer的方式实现。

 

步骤：

1.从MySQL网站下载最新的MySQL ODBC驱动：http://www.mysql.com/downloads/connector/odbc/，

 

2.在SQL Server所在的服务器安装，并在ODBC中建立System DSN，命名为“MySQLODBC”。

 

3.在SQL Server建立Linkserver，可通过如下脚本实现：

<span>EXEC</span> master.dbo.sp_addlinkedserver <br><span>@server</span> <span>=</span> N<span>'S</span><span>erverName</span><span>'</span>,  --连接服务器名称<br><span>@srvproduct</span><span>=</span>N<span>'</span><span>mysql</span><span>'</span>,     --产品名<br><span>@provider</span><span>=</span>N<span>'</span><span>MSDASQL</span><span>'</span>,     --接口程序名<br><span>@datasrc</span><span>=</span>N<span>'</span><span>MySQLODBC</span><span>'     --odbc 中新建的连接名</span><br><br><span>EXEC</span> master.dbo.sp_addlinkedsrvlogin <br><span>@rmtsrvname</span><span>=</span>N<span>'</span><span>Servername</span><span>'</span>,--连接服务名<br><span>@useself</span><span>=</span>N<span>'</span><span>False</span><span>'</span>,<br><span>@locallogin</span><span>=</span><span>NULL</span>,<br><span>@rmtuser</span><span>=</span><span>NULL</span>,<br><span>@rmtpassword</span><span>=</span><span>NULL</span>

 

之后可以通过OPENQUERY的方式调用MySQL的数据：

<span>INSERT </span><span>INTO </span><span>OPENQUERY</span><span>(Servername,</span><span>'</span><span><span>select * from DBName</span><span><span>.tablename</span><span>'</span><span>)</span></span><br></span><span>SELECT </span><span>'</span><span>1</span><span>'</span><span><br><br></span><span>SELECT </span><span>*</span><span><br></span><span>FROM </span><span>OPENQUERY</span><span>(Servername<span><span>,</span><span>'</span><span><span>select * from DBName</span><span><span>.</span></span></span></span><span>tablename</span><span><span>'</span><span>)</span></span><span><span><span><span><br></span></span></span></span></span><span><br></span><span>UPDATE </span><span>OPENQUERY</span><span>(Servername<span><span>,</span><span>'</span><span><span>select * from DBName</span><span><span>.</span></span></span></span><span>tablename</span><span><span>'</span><span>)</span></span><span><span><span><span><br></span></span></span></span></span><span>SET</span><span> colname<span><span>=</span><span>'2</span><span>'</span></span><br></span><span>WHERE</span><span> colname</span><span>=</span><span>'</span><span>1</span><span>'</span><span><br><br></span><span>SELECT </span><span>*</span><span><br></span><span>FROM </span><span>OPENQUERY</span><span>(Servername<span><span>,</span><span>'</span><span><span>select * from DBName</span></span></span></span><span><span>.tablename</span></span><span>'</span><span>)</span>