`
JaHunter
  • 浏览: 88883 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

sp_addlinkedserver_远程sql连接

阅读更多

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_namedata_sourcelocationprovider_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_sourcelocationprovider_stringcatalog 参数标识链接服务器指向的数据库。如果任一参数为 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 提供程序
  1. 使用用于 SQL Server 的 OLE DB 创建链接服务器

    下面的示例创建一台名为 SEATTLESales 的链接服务器,该服务器使用用于 SQL Server 的 Microsoft OLE DB 提供程序。

    USE master
    GO
    EXEC sp_addlinkedserver 
        'SEATTLESales',
        N'SQL Server'
    GO
    
    
  2. 在 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
分享到:
评论

相关推荐

    SQL Server 远程连接服务器详细配置(sp_addlinkedserver)

    EXEC sp_addlinkedserver '远程服务器IP','SQL Server' --标注存储 EXEC sp_addlinkedserver @server = 'server', --链接服务器的本地名称。也允许使用实例名称,例如MYSERVERSQL1 @srvproduct = 'product_name' --...

    sp_addlinkedserver sp_addlinkedsrvlogin mssql Oracle

    sp_addlinkedserver,sp_addlinkedsrvlogin 此文件是增加链接服务器对象,远程登录,此文件包含两个部分,一个是链接sql的,一个是链接Oracle的,内容标住的很详细,那里稍微修改下就可以用!

    sqlserver 多库查询 sp_addlinkedserver使用方法(添加链接服务器)

    Exec sp_droplinkedsrvlogin ZYB,Null –删除映射(录与链接服务器上远程登录之间的映射) Exec sp_dropserver ZYB –删除远程服务器链接 EXEC sp_addlinkedserver @server=’ZYB’,–被访问的服务器别名 @...

    SQLServer2008新实例远程数据库链接问题(sp_addlinkedserver)

    主要介绍了SQLServer2008新实例远程数据库链接问题(sp_addlinkedserver),需要的朋友可以参考下

    sqlserver 不同服务器数据库之间的数据操作

    exec sp_addlinkedserver 'ITSV','','SQLOLEDB','远程服务器名或ip地址' exec sp_addlinkedsrvlogin 'ITSV','false',null,'用户名','密码' --查询示例 select * from ITSV.数据库名.dbo.表名 --导入示例 select * ...

    sql server中分布式查询

    sql server中分布式查询随笔(链接服务器(sp_addlinkedserver)和远程登录映射(sp_addlinkedsrvlogin)使用

    连接其它服务器数据库查询数据(sql server)

    exec sp_dropserver '链接名', 'droplogins ' --连接远程/局域网数据(openrowset/openquery/opendatasource) --1、openrowset --查询示例 select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码...

    SQL Server创建链接服务器的存储过程示例分享

    在使用 sp_addlinkedserver 创建链接 服务器后,可对该服务器运行分布式查询。如果链接服务器定义为 SQL Server 实例,则可执行远程存储过程。 http://msdn.microsoft.com/zh-cn/library/ms190479(SQL.90).aspx ...

    SQLSERVER简单创建DBLINK操作远程服务器数据库的方法

    本文实例讲述了SQLSERVER简单创建DBLINK操作远程服务器数据库的方法。分享给大家供大家参考,具体如下: --配置SQLSERVER数据库的DBLINK exec sp_addlinkedserver @server='WAS_SMS',@srvproduct='',@provider='...

    SQLSERVER 本地查询更新操作远程数据库的代码

    –PK select * from sys.key_constraints where object_id = OBJECT_ID(‘TB’) –FK select * from sys.foreign_keys where parent_object_id =OBJECT_ID(‘TB’) –创建链接服务器 exec sp_addlinkedserver ‘ITSV...

    深入SQL Server 跨数据库查询的详解

    表B b WHERE a.field=b.fieldSqlServer数据库:–这句是映射一个远程数据库EXEC sp_addlinkedserver ‘远程数据库的IP或主机名’,N’SQL Server’–这句是登录远程数据库EXEC sp_addlinkedsrvlogin ‘远程数据库的IP...

    sql server 复制表从一个数据库到另一个数据库

    /*不同服务器数据库之间的数据操作*/ –创建链接服务器 exec sp_addlinkedserver ‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘, ‘远程服务器名或ip地址 ‘ exec sp_addlinkedsrvlogin ‘ITSV ‘, ‘false ‘,null, ‘用户名 ...

    SQl 跨服务器查询语句

    select * from OPENDATASOURCE( ‘SQLOLEDB’, ‘Data Source=远程ip;...表名 或使用联结服务器: –创建linkServer exec sp_addlinkedserver ‘别名’,”,’SQLOLEDB’,’192.168.2.5′ –登陆linkServe

    经典SQL语句大全

    exec sp_executesql @sql 注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是...

    同一个sql语句 连接两个数据库服务器

    exec sp_addlinkedserver ‘逻辑名称’,”,’SQLOLEDB’,’远程服务器名或ip地址’ exec sp_...表名称 这是一个完整的sql语句 使用完成之后要,删除掉建立的虚拟连接 exec sp_dropserver ‘逻辑名称’,’droplogins’

    sql经典语句一部分

    exec sp_executesql @sql 注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是...

    数据库操作语句大全(sql)

    exec sp_executesql @sql 注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是...

    xls转mdb代码以及.exe执行软件

    /********************** EXCEL导到远程SQL insert OPENDATASOURCE( 'SQLOLEDB', 'Data Source=远程ip;User ID=sa;Password=密码' ).库名.dbo.表名 (列名1,列名2) SELECT 列名1,列名2 FROM OpenDataSource( '...

Global site tag (gtag.js) - Google Analytics