SQLServer的链接服务器技术小结

男娘i 2022-07-12 12:56 205阅读 0赞

**(一****) ** **使用 Microsoft OLE DB Provider For ODBC 链接****MySQL**

1．安装MySQL的ODBC驱动MyODBC

2．为MySQL建立一个ODBC系统数据源，例如:选择数据库为test ,数据源名称为myDSN

3、建立链接数据库

EXEC sp\_addlinkedserver @server = 'MySQLTest', @srvproduct='MySQL',

@provider = 'MSDASQL', @datasrc = 'myDSN'

EXEC sp\_addlinkedsrvlogin 　@rmtsrvname='MySqlTest',@useself='false',@locallogin='sa',@rmtuser='mys

ql的用户名',@rmtpassword='mysql的密码'

4、查询数据

SELECT \* FROM OPENQUERY (MySQLTest ,'select \* from 表' )

下面这个不行:　SELECT \* FROM OPENQUERY (MySQLTest ,'表' )

注意:不能直接用select \* from 链接服务器名.数据库名.用户名.表(或视图)，可能是个Bug.

create table \#tmp\_testdb\_username (username varchar(32));

set @str\_sql = 'SELECT \* FROM OPENQUERY (Mysql\_TestDB ,''select account from t\_users where firstlogintime between ''''' \+ @str\_startdate \+ '''''' \+  ' and ''''' \+ @str\_stopdate  \+ ''''''' ) ';

insert into \#tmp\_testdb\_username(username)

execute (@str\_sql)  as login  = 'queryuser';

**(二)  使用 Microsoft OLE DB Provider For ORACLE 链接ORACLE**

1、建立链接数据库

sp\_addlinkedserver '别名', 'Oracle', 'MSDAORA', '服务名'

EXEC sp\_addlinkedsrvlogin @rmtsrvname='别名　　',@useself='false',@locallogin='sa',@rmtuser='oracle用户名',@rmtpassword='密码'

2、查询数据

SELECT \* FROM 别名..用户名.表(视图)

注意:四部分名称全部用大写

3、执行存储过程

使用OPENQUERY:

SELECT \*　FROM OPENQUERY(别名, 'exec 用户名.存储过程名')

**(三****) ** **设置链接服务器以访问格式化文本文件(txt, excel)**

用于 Jet 的 Microsoft OLE DB 提供程序可用于访问并查询文本文件。

若要直接创建访问文本文件的链接服务器而不将文件链接为 Access .mdb 文件中的表，请执行 sp\_addlinkedserver，如下例所示。

提供程序是 Microsoft.Jet.OLEDB.4.0，提供程序字符串为"Text"。数据源是包含文本文件的目录的完整路径名称。schema.ini 文件(描述文本文件的结构)必须与此文本文件存在于相同的目录中。有关创建 schema.ini 文件的更多信息，请参见 Jet 数据库引擎文档。

\--Create a linked server.

EXEC sp\_addlinkedserver txtsrv, 'Jet 4.0','Microsoft.Jet.OLEDB.4.0','c:datadistqry',　　NULL,'Text'

\--Set up login mappings.

EXEC sp\_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL

\--List the tables in the linked server.

EXEC sp\_tables\_ex txtsrv

\--Query one of the tables: file1\#txt

\--using a 4-part name.

SELECT \*

FROM txtsrv...\[file1\#txt\]

**链接服务器excel**

EXEC master.dbo.sp\_addlinkedserver @server = N'My\_excel',  
@srvproduct=N'Excel',  
 @provider=N'Microsoft.Jet.OLEDB.4.0',  
 @datasrc=N'D:\\Temp\\my\_file.xls',  
 @provstr=N'Excel 8.0'  
   
SELECT \* FROM My\_excel...sheet1$

**(四)  链接SQL Server服务器:**

1、使用 ODBC 的 Microsoft OLE DB 提供程序

EXEC sp\_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER=\{SQL　　Server\};SERVER=远程名;UID=用户;PWD=密码;'

**　　EXEC sp\_addlinkedserver  
@server = '链接伪名',  
@srvproduct='',  
@provider = 'SQLOLEDB', --或者'MSDASQL'  
@provstr = 'DRIVER=\{SQL Server\};SERVER=IP地址,端口号;'  
\--UID=;PWD=;建了之后，再进去修改远程用户名及密码即可**

如果加上参数@catalog，可以指定数据库

exec sp\_addlinkedsrvlogin @rmtsrvname='别名　　',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'

2、使用SQL Server 的 Microsoft OLE DB 提供程序

exec sp\_addlinkedserver @server='别名　　',@provider='sqloledb',@srvproduct='',@datasrc='远程服务器名'

exec sp\_addlinkedsrvlogin　　@rmtsrvname='wzb',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'

然后你就可以如下:

select \* from 别名.库名.dbo.表名

insert 库名.dbo.表名 select \* from 别名.库名.dbo.表名

select \* into 库名.dbo.新表名 from 别名.库名.dbo.表名

例1、

此示例在 SQL Server 的实例上创建一台名为 S1\_instance1 的链接服务器，　　该服务器使用 SQL Server 的 Microsoft OLE DB 提供程序。

EXEC sp\_addlinkedserver @server='S1\_instance1', @srvproduct='',　　@provider='SQLOLEDB',@datasrc='S1instance1'

例2、

\--建立链接服务器

EXEC sp\_addlinkedserver 'xiaoming','','MSDASQL',NULL,NULL,'DRIVER=\{SQL　　Server\};SERVER=192.168.0.1;UID=sa;PWD=123;'

\--建立链接服务器登录映射

exec sp\_addlinkedsrvlogin　　@rmtsrvname='xiaoming',@useself='false',@locallogin='sa',@rmtuser='sa',　　@rmtpassword='123'

\--查询数据

select \* from xiaoming.schooladmin.dbo.agent

\--删除链接服务器登录映射和链接服务器:

exec sp\_droplinkedsrvlogin 'xiaoming' ,'sa'

exec sp\_dropserver 'xiaoming'

注意事项:

SET IDENTITY\_INSERT \[ database.\[ owner.\] \] \{ table \} \{ ON | OFF \}

所以不能通过连接服务器设置此属性

into 也存在这样的问题

select \* into xiaoming.northwind.dbo.tt from

xiaoming.northwind.dbo.tt

**(五****) ** **设置链接服务器以访问Access数据库**

使用用于 Jet 的 Microsoft OLE DB 提供程序

此示例创建一台名为 test的链接服务器。

说明本示例假设已经安装 Microsoft Access 和示例 Northwind 数据库，且　　Northwind 数据库驻留在C:。

USE master

\-- To use named parameters:

EXEC sp\_addlinkedserver　@server = 'test',@provider = 'Microsoft.Jet.OLEDB.4.0',　　@srvproduct = 'OLE DB Provider for Jet',@datasrc = 'C:Northwind.mdb'

\-- OR to use no named parameters:

USE master

EXEC sp\_addlinkedserver  'test', 'OLE DB Provider for Jet', 　　'Microsoft.Jet.OLEDB.4.0', 'C:Northwind.mdb'

使用

select \* from test...表名

**(六****) ** **　连接****SYBASE**

\--首先,你要在SQL服务器上装上访问sybase的客户端

\--创建链接服务器

exec sp\_addlinkedserver 'Sybase1', ' ', 'MSDASQL', NULL, NULL,'Driver=\{Sybase System11\};Database=hisdb;Srvr=10.211.135.12;UID=sa;PWD=1111;'

使用:

select \* from Sybase1.hisdb.dbo.table1

方法二