SQL Server的链接服务器技术小结

2024-08-31 00:50:46

字体：大中小

来源：转载

供稿：网友

一、使用 Microsoft OLE DB PRovider For ODBC 链接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='mys

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

3、查询数据

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

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

注意：不能直接用select * from 链接服务器名.数据库名.用户名.表(或视图)

四部分名称查询数据，可能是个Bug.

二、使用 Microsoft OLE DB Provider For Oracle 链接ORACLE

1、建立链接数据库
sp_addlinkedserver '别名', 'Oracle', 'MSDAORA', '服务名'
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname='别名

',@useself='false',@locallogin='sa',@rmtuser='oracle用户名

',@rmtpassword='密码'

2、查询数据

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

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

3、执行存储过程

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

三、设置链接服务器以访问格式化文本文件

用于 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:/data/distqry',
    NULL,
    'Text'
GO

--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
GO

--List the tables in the linked server.
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a 4-part name.
SELECT *
FROM txtsrv...[file1#txt]

四、链接SQL Server服务器:

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

EXEC sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL

Server};SERVER=远程名;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',@rmtp

assword='密码'

然后你就可以如下：
select * from 别名.库名.dbo.表名
insert 库名.dbo.表名 select * from 别名.库名.dbo.表名
select * into 库名.dbo.新表名 from 别名.库名.dbo.表名
go

例1、

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

EXEC sp_addlinkedserver @server='S1_instance1', @srvproduct='',
@provider='SQLOLEDB',

@datasrc='S1/instance1'

例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'
go
--查询数据
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
GO
-- 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'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
   'test',
   'OLE DB Provider for Jet',
   'Microsoft.Jet.OLEDB.4.0',
   'C:/Northwind.mdb'
GO
使用
select * from test...表名