sp_addlinkedserver_远程sql连接

gfdxy3322

　　Transact-SQL 参考



sp_addlinkedserver
　　创建一个链接的服务器，使其允许对分布式的、针对 OLE DB 数据源的异类查询进行访问。在使用 sp_addlinkedserver

创建链接的服务器之后，此服务器就可以执行分布式查询。如果链接服务器定义为 Microsoft® SQL
Server™，则可执行远程存储过程。

语法
　　sp_addlinkedserver
[ @server
=
] '
server
'



    
[ ,
[ @srvproduct
=
] '
product_name
'

]


    [ ,
[ @provider
=
] '
provider_name
'

]


    [ ,
[ @datasrc
=
] '
data_source
'

]


    [ ,
[ @location
=
] '
location
'

]


    [ ,
[ @provstr
=
] '
provider_string
'

]


    [ ,
[ @catalog
=
] '
catalog
'

]


参数
　　[ @server =
] '
server
'

要创建的链接服务器的本地名称，server
的数据类型为 sysname
，没有默
认设置。
如果有多个 SQL Server 实例，server
可以为 servername\instancename
。
此链接的服务器可能会被引用为下面示例的数据源：

SELECT *FROM    [servername\instancename.]pubs.dbo.authors.


如果未指定 data_source
，则服务器为该实例的实际名称。
　　[ @srvproduct =
] '
product_name
'

要添加为链接服务器的 OLE DB 数据源的产品名称。product_name
的数据类型为 nvarchar(128)
，
默认设置为 NULL。如果是 SQL Server
，则不需要指定 provider_name
、data_source
、location
、provider_string
以及目录。
　　[ @provider =
] '
provider_name
'

与此数据源相对应的 OLE DB 提供程序的唯一程序标识符 (PROGID)。provider_name

对于安装在当前计算机上指定的 OLE DB 提供程序必须是唯一的。provider_name
的数据类型为nvarchar(128)
，
默认设置为 NULL。OLE DB 提供程序应该用给定的 PROGID 在注册表中注册。
　　[ @datasrc =
] '
data_source
'

由 OLE DB 提供程序解释的数据源名称。data_source
的数据类型为 nvarchar(4000)
，
默认设置为 NULL。data_source
被当作 DBPROP_INIT_DATASOURCE 属性传递以便初始化 OLE
DB 提供程序。
当链接的服务器针对于 SQL Server OLE DB 提供程序创建时，可以按照 servername
\instancename
的形式指定 data_source，
它可以用来连接到运行于特定计算机上的 SQL Server 的特定实例上。servername
是运行 SQL Server 的计算机名称，instancename
是用户将被连接到的特定 SQL Server 实例的名称。
　　[ @location =
] '
location
'

OLE DB 提供程序所解释的数据库的位置。location
的数据类型为 nvarchar(4000)
，
默认设置为 NULL。location
作为 DBPROP_INIT_LOCATION 属性传递以便初始化 OLE DB
提供程序。
　　[ @provstr =
] '
provider_string
'

OLE DB 提供程序特定的连接字符串，它可标识唯一的数据源。provider_string
的
数据类型为 nvarchar(4000)
，默认设置为 NULL。Provstr
作为
DBPROP_INIT_PROVIDERSTRING 属性传递以便初始化 OLE DB 提供程序。
当针对 Server OLE DB 提供程序提供了链接服务器后，可将 SERVER 关键字用作
SERVER=servername
\instancename
来指定实例，以指定特定的 SQL Server 实例。servername
是 SQL Server 在其上运行的计算机名称，instancename
是用户连接到的特定的 SQL Server
实例名称。
　　[ @catalog =
] '
catalog
'

建立 OLE DB 提供程序的连接时所使用的目录。catalog
的数据类型为sysname
，
默认设置为 NULL。catalog
作为 DBPROP_INIT_CATALOG 属性传递以便初始化 OLE DB 提供程序。

返回代码值
　　0（成功）或 1（失败）

结果集
　　如果没有指定参数，则 sp_addlinkedserver
返回此消息：

Procedure 'sp_addlinkedserver' expects parameter '@server', which was not supplied.


　　使用适当 OLE DB 提供程序和参数的 sp_addlinkedserver
返回此消息：

Server added.


注释
　　下表显示为可通过 OLE DB
访问的数据源设置链接服务器的方法。对于给定的数据源，可以使用多种方法为其设置链接服务器，下表中可能有不止一行适用于一种数据源类型。下表也显示了用
于设置链接服务器的 sp_addlinkedserver
参数值。


远程 OLE DB 数据源


OLE DB

提供程序


product_name


provider_name


data_source



location


provider_string



catalog


SQL Server
用于 SQL Server 的 Microsoft OLE DB 提供程序
SQL Server (1)（默认值）
-
-
-
-
-


SQL Server
用于 SQL Server 的 Microsoft OLE DB 提供程序
SQL Server

SQLOLEDB

SQL Server 的网络名称（用于默认实例）
-
-
数据库名称（可选）


SQL Server
用于 SQL Server 的 Microsoft OLE DB 提供程序
-

SQLOLEDB

服务器名\实例名（对于特定实例）
-
-
数据库名称（可选）


Oracle
用于 Oracle 的 Microsoft OLE DB 提供程序
任何 (2)

MSDAORA

用于 Oracle 数据库的 SQL*Net 别名


-
-
-


Access/

Jet
用于 Jet 的 Microsoft OLE DB 提供程序
任何

Microsoft.Jet.OLEDB.4.0

Jet 数据库文件的完整路径名
-
-
-


ODBC 数据源
用于 ODBC 的 Microsoft OLE DB 提供程序
任何

MSDASQL

ODBC 数据源的系统 DSN
-
-
-


ODBC 数据源
用于 ODBC 的 Microsoft OLE DB 提供程序
任何

MSDASQL

-
-
ODBC 连接字符串
-


文件系统
用于索引服务的 Microsoft OLE DB 提供程序
任何

MSIDXS

索引服务目录名称
-
-
-


Microsoft Excel 电子表格
用于 Jet 的 Microsoft OLE DB 提供程序
任何

Microsoft.Jet.OLEDB.4.0

Excel 文件的完整路径名
-
Excel 5.0
-


IBM DB2 数据库
用于 DB2 的Microsoft OLE DB 提供程序
任何

DB2OLEDB

-
-
请参见用于 DB2 文档的 Microsoft OLE DB 提供程序
DB2 数据库的目录名
　　
　　(1 ) 这种设置链接服务器的方式强制链接服务器的名称与远程 SQL Server 的网络名称相同。使用 server
指定服务器。

(2 ) "任何"指产品名称可以任意。
　　data_source
、location
、provider_string
和 catalog

参数标识链接服务器指向的数据库。如果任一参数为 NULL 值，则不设置相应的 OLE DB 初始化属性。
<!-- NOTE-->
　　说明
  若要在 SQL
Server 6.x
版上使用 SQL Server 2000 版的 Microsoft OLE DB 提供程序，请在 6.x

版 SQL Server 上运行 \Microsoft SQL Server\Install\Instcat.sql
脚
本。此脚本对于在 SQL Server 6.x
服务器上运行分布式查询是基本的。

<!-- /NOTE-->　　在群集环境中，当指定指向 OLE DB 数据源的文件名时，应使用通用命名规则 (UNC) 名称或共享驱动器指定位置。

权限
　　执行许可权限默认授予 sysadmin
和setupadmin
固定服务器角色的成员。

示例

A. 使用用于 SQL Server 的 Microsoft OLE DB 提供程序

• 使用用于 SQL Server 的 OLE DB 创建链接服务器
  下面的示例创建一台名为 SEATTLESales
  的链接服务器，该服务器使用用于 SQL Server
  的 Microsoft OLE DB 提供程序。
  
  USE master
  GO
  EXEC sp_addlinkedserver
  'SEATTLESales',
  N'SQL Server'
  GO
  
• 在 SQL Server 的实例上创建链接服务器
  此示例在 SQL Server 的实例上创建一台名为 S1_instance1
  的链接服务器，该服务器
  使用 SQL Server 的 Microsoft OLE DB 提供程序。
  
  EXEC    sp_addlinkedserver    @server='S1_instance1', @srvproduct='',
  @provider='SQLOLEDB', @datasrc='S1\instance1'
  

B. 使用用于 Jet 的 Microsoft OLE DB 提供程序
　　此示例创建一台名为 SEATTLE Mktg
的链接服务器。
<!-- NOTE-->
　　说明
  本示例假设已经安装
Microsoft Access 和示例 Northwind
数据库，且 Northwind
数据库驻留在
C:\Msoffice\Access\Samples。

<!-- /NOTE-->
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = 'SEATTLE Mktg',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
'SEATTLE Mktg',
'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0',
'C:\MSOffice\Access\Samples\Northwind.mdb'
GO


C. 使用用于 Oracle 的 Microsoft OLE DB 提供程序
　　此示例创建一台名为 LONDON
Mktg
的链接服务器，该服务器使用用于 Oracle 的
Microsoft OLE DB 提供程序，并且假设此 Oracle 数据库的 SQL*Net 别名为 MyServer
。

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = 'LONDON Mktg',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'MyServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
'LONDON Mktg',
'Oracle',
'MSDAORA',
'MyServer'
GO


D. 将 data_source
参数与用于 ODBC 的 Microsoft OLE DB 提供程序一起使用
　　此示例创建一台名为 SEATTLE Payroll
的链接服务器，该服务器使用用于 ODBC 的 Microsoft OLE
DB 提供程序和 data_source
参数。
<!-- NOTE-->
　　说明
  在执行 sp_addlinkedserver
之前，必须在服务器上将指定的 ODBC 数据源名称定义为系统 DSN。

<!-- /NOTE-->
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = 'SEATTLE Payroll',
@provider = 'MSDASQL',
@datasrc = 'LocalServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
'SEATTLE Payroll',
'',
'MSDASQL',
'LocalServer'
GO


E. 将 provider_string
参数与用于 ODBC 的 Microsoft OLE DB 提供程序一起使用
　　此示例创建一台名为 LONDON Payroll
的链接服务器，该服务器使用用于 ODBC 的 Microsoft OLE
DB 提供程序和 provider_string
参数。
<!-- NOTE-->
　　说明
  有关 ODBC
连接字符串的更多信息，请参见 SQLDriverConnect
和如何分配句柄并与 SQL Server (ODBC) 连接
。


<!-- /NOTE-->
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = 'LONDON Payroll',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
'LONDON Payroll',
'',
'MSDASQL',
NULL,
NULL,
'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
GO


F. 在 Excel 电子表格上使用用于 Jet 的 Microsoft OLE DB 提供程序
　　若要创建使用用于 Jet 的 Microsoft OLE DB 提供程序以访问 Excel 电子表格的链接服务器定义，请首先在 Excel
中创建一个命名的范围以指定要在 Excel 工作表中选择的行和列。然后，可将此范围的名称引用为分布式查询中的表名称。

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyData\DistExcl.xls',
NULL,
'Excel 5.0'
GO


　　为了访问 Excel
电子表格中的数据，请将某个范围内的单元与某个名称相关联。通过将范围的名称用作表名称，可以访问指定的已命名范围。下列查询利用前面设置的链接服务器，
可访问称为 SalesData
的命名范围。

SELECT *
FROM EXCEL...SalesData
GO


G. 使用用于检索服务的 Microsoft OLE DB 提供程序
　　此示例创建一台链接服务器，并且使用 OPENQUERY 从为检索服务启用的链接服务器和文件系统中检索信息。

EXEC sp_addlinkedserver FileSystem,
'Index Server',
'MSIDXS',
'Web'
GO
USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'yEmployees')
DROP TABLE yEmployees
GO
CREATE TABLE yEmployees
(
id       int         NOT NULL,
lname    varchar(30) NOT NULL,
fname    varchar(30) NOT NULL,
salary   money,
hiredate datetime
)
GO
INSERT yEmployees VALUES
(
10,
'Fuller',
'Andrew',
$60000,
'9/12/98'
)
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'DistribFiles')
DROP VIEW DistribFiles
GO
CREATE VIEW DistribFiles
AS
SELECT *
FROM OPENQUERY(FileSystem,
'SELECT Directory,
FileName,
DocAuthor,
Size,
Create,
Write
FROM SCOPE('' "c:\My Documents" '')
WHERE CONTAINS(''Distributed'') > 0
AND FileName LIKE ''%.doc%'' ')
WHERE DATEPART(yy, Write) = 1998
GO
SELECT *
FROM DistribFiles
GO
SELECT Directory,
FileName,
DocAuthor,
hiredate
FROM DistribFiles D, yEmployees E
WHERE D.DocAuthor = E.FName + ' ' + E.LName
GO


H. 使用用于 Jet 的 Microsoft OLE DB 提供程序访问文本文件
　　此示例创建一台直接访问文本文件的链接服务器，而没有将这些文件链接为 Access .mdb 文件中的表。提供程序是
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, 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]


I. 使用用于 DB2 的 Microsoft OLE DB 提供程序
　　下面的示例创建一台名为 DB2 的链接服务器，该服务器使用用于 DB2 的 Microsoft OLE DB 提供程序。

EXEC sp_addlinkedserver
@server='DB2',
@srvproduct='Microsoft OLE DB Provider for DB2',
@catalog='DB2',
@provider='DB2OLEDB',
@provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=XYZ;Network Port=50000;Package Collection=admin;Default Schema=admin;'


<!-- RELATEDTOPICSLIST-->

来源：http://www.yesky.com/imagesnew/software/tsql/ts_sp_adda_8gqa.htm