SQL SERVER 链接服务器以及 openrowset-Toy模板网

这篇具有很好参考价值的文章主要介绍了SQL SERVER 链接服务器以及 openrowset。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

简单来说，通过链接服务器，让访问其它服务器，像跟访问自身数据库一样方便。

from [链接服务器][数据库].[所有者].Tabel

创建链接服务器两种方式 T_SQL 和SSMS

T_SQL

SQL SERVER 链接服务器以及 openrowset

–建立连接服务器

EXEC sp_addlinkedserver ‘远程服务器IP’,‘SQL Server’
–标注存储
EXEC sp_addlinkedserver
@server = ‘server’, --链接服务器的本地名称。也允许使用实例名称，例如MYSERVER\SQL1
@srvproduct = ‘product_name’， --OLE DB数据源的产品名。对于SQL Server实例来说，product_name是’SQL Server’
@provider = ‘provider_name’ ，–这是OLE DB访问接口的唯一可编程标识。当没有指定它时，访问接口名称是 SQL Server数据源。SQL Server显式的provider_name是 SQLNCLI（Microsoft SQL Native Client OLE DB Provider）。Oracler的是 MSDAORA，Oracle 8或更高版本的是OraOLEDB.Oracle。MS Access和MS Excel的是 Microsoft.Jet.OLEDB.4.0。IBM DB2的是DB2OLEDB，以及ODBC数据源的是MSDASQL
@datasrc = ‘data_source’， --这是特定OLE DB访问接口解释的数据源。对于SQL Server，这是 SQL Server（servername或servername\instancename）的网络名称。对于Oracle，这是SQL*Net别名。对于 MS Access和MSExcel，这是文件的完整路径和名称。对于ODBC数据源，这是系统DSN名称
@location = ‘location’ ，–由特定OLE DB访问接口解释的位置
@provstr = ‘provider_string’ ，–OLE DB 访问接口特定的连接字符串。对于ODBC连接，这是ODBC连接字符串。对于MS Excel，这是Excel 5.0
@catalog = ‘catalog’ --catalog的定义变化基于OLE DB访问接口的实现。对于SQL Server，这是可选的数据库名称，对于DB2，这个目录是数据库的名称

–创建链接服务器上远程登录之间的映射

EXEC sp_addlinkedsrvlogin ‘远程服务器IP’,‘false’,‘sa’,‘架构名’,‘访问密码’
–标注存储
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘远程服务器IP’, --要添加登录名映射的本地链接服务器
@useself = false, --当使用true值时，使用本地SQL或Windows登录名连接到远程服务器名。如果设为false，存储过程 sp_addlinkedsrvlogin的locallogin、rmtuser和rmtpassword参数将应用到新的映射中
@locallogin = NULL, --这是映射到远程登录名的SQL Server登录或Windows用户的名称。如果这个参数置为NULL，映射将应用SQL Server实例中的所有本地登录名
@rmtuser = ‘架构名’, --用来连接到链接服务器的用户／登录名的名称
@rmtpassword = ‘访问密码’ --用来连接到链接服务器的用户／登录名的密码

–查看链接服务器信息

select name,product,provider,data_source,query_timeout,lazy_schema_validation,is_remote_login_enabled,is_rpc_out_enabled
from sys.servers where is_linked=1
SQL SERVER 链接服务器以及 openrowset

配置链接服务器属性

exec sp_serveroption ‘别名’,‘name’,‘远程服务器IP’
–标注存储
EXEC sp_serveroption
@server = ‘server’ --配置属性的链接服务器的名称
,@optname = ‘option_name’ --要配置的选项
,@optvalue = ‘option_value’ --选项的新值
–附参数
参数描述
collation compatible 如果你确认SQL Server实例与远程SQL Server拥有相同的排序规则就启用这个设置。由于SQL Server不再需要对数据源之间的字符列执行比较操作，把它们假定为相同的排序规则，这样做可以提升性能
collation name 如果启用了use remote collation并且是非SQL Server的数据源，则collation name指定远程服务器排序规则的名称。这个排序规则名称必须是SQL Server所支持的
connect timeout 指定在超时发生之前到链接服务器的连接会尝试多少秒。如果数值为“0”，sp_ configure的remote query timeout的服务器值用来当作默认值
data access 如果启用，就允许分布式查询访问
lazy schema validation 如果设为true，则架构不会在查询开始时去检测远程表。尽管这样会减少远程查询的负载，但是如果架构发生了变化并且你没有进行架构检测，比如说查询中引用的对象不能与查询命令进行通信，就会生成错误
query timeout 指定查询等待的超时值（秒数）。如果这个值为0，则query wait选项使用sp_configure值
rpc 启用从服务器进行远程过程调用
rpc out 启用远程过程调用到服务器
use remote collation 指定是使用远程服务器排序规则（true）还是本地服务器排序规则（false）

–删除链接服务器属性

exec sp_dropserver ‘4’
–标注存储
EXEC sp_dropserver
@server = ‘server’ --从SQL Server实例中删除的链接服务器的名称
,@droplogins = ‘droplogins’ --如果指定droplogins，则在删除链接服务器之前要删除登录名映射

–查看链接登录名

select s.name linkedServerName,s.data_source linkedserver_source,s.is_linked,s.modify_date,
ll.remote_name,ll.local_principal_id,ll.uses_self_credential,
p.name localLoginName
from sys.Linked_logins ll
inner join sys.servers s on s.server_id=ll.server_id
left join sys.server_principals p on p.principal_id=ll.local_principal_id
where s.is_linked = 1

–删除链接服务器登录名映射

exec sp_droplinkedsrvlogin ‘远程服务器IP’,NULL
–标注存储
EXEC sp_droplinkedsrvlogin
@rmtsrvname = ‘远程服务器IP’ --登录名映射的链接服务器名称
@locallogin = NULL --这是从链接服务器删除的SQL Server登录或Windows用户映射的名称

SSMS

SQL SERVER 链接服务器以及 openrowset

选项

动态参数

如果为非零，则表明访问接口允许对参数化查询使用“?”参数标记语法。仅当该访问接口支持 ICommandWithParameters 接口并支持“?”作为参数标记时，才应设置此选项。如果设置了此选项，则允许 Microsoft SQL Server 针对该访问接口执行参数化查询。这种对访问接口执行参数化查询的能力会提高某些查询的性能。

嵌套查询

如果为非零，则表示访问接口允许在 FROM 子句中使用嵌套的 SELECT 语句。如果设置了此选项，则允许 SQL Server 将某些查询委托给需要在 FROM 子句中嵌套 SELECT 语句的访问接口。

仅零级

如果为非零，则只对访问接口调用 0 级的 OLE DB 接口。

允许进程内

如果为非零，则 SQL Server 允许将访问接口实例化为进程内服务器。如果未设置此选项，则默认行为是在 SQL Server 进程外实例化访问接口。在 SQL Server 进程外实例化访问接口，可防止 SQL Server 进程在访问接口中出错。在 SQL Server 进程外实例化访问接口时，不允许更新或插入长的引用列（text、ntext 或 image）。

非事务更新

如果为非零，则 SQL Server 允许更新，即使 ITransactionLocal 不可用时也是如此。如果启用此选项，对访问接口的更新将不可恢复，因为该访问接口不支持事务。

作为访问路径的索引

如果为非零，则 SQL Server 尝试使用访问接口的索引来提取数据。默认情况下，索引只能用于元数据而且从不打开。

禁止link访问

如果设置了非零值，则 SQL Server 不允许通过 OPENROWSET 和 OPENDATASOURCE 函数对 OLE DB 访问接口进行即席访问。如果未设置此选项，则 SQL Server 同样不允许进行即席访问。

存储过程中通过链接服务器访问远程服务器

执行存A服存储过程时会报 ‘未将[所用到的别名] 服务器配置为可用的RPC’ ,如何解决?
很简单，查看链接服务器的选项,将RPC RPC_Out 值由False 改为 TRUE 再调试，成功!
也有情况在这里无法更改,解决方案是:

 exec sp_configure 'show advanced options', 1; --默认是0    
 GO   
 RECONFIGURE WITH OVERRIDE; 
 GO

openrowset

OPENROWSET

包含访问 OLE DB 数据源中的远程数据所需的全部连接信息。当访问链接服务器中的表时，这种方法是一种替代方法，并且是一种使用 OLE DB 连接并访问远程数据的一次性的、特殊的方法。可以在查询的 FROM 子句中像引用表名那样引用 OPENROWSET 函数。依据 OLE DB 提供程序的能力，还可以将 OPENROWSET 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。尽管查询可能返回多个结果集，然而 OPENROWSET 只返回第一个。

语法

OPENROWSET ( ‘provider_name’

, { 'datasource' ; 'user_id' ; 'password'

    | 'provider_string' }

, { [ catalog.] [ schema.] object

    | 'query' } 

)

参数

‘provider_name’

字符串，它代表在注册表中指定的 OLE DB 提供程序的友好名。provider_name 没有默认值。

‘datasource’

字符串常量，它对应着某个特定的 OLE DB 数据源。datasource 是将被传递到提供程序 IDBProperties 接口以初始化提供程序的 DBPROP_INIT_DATASOURCE 属性。通常，这个字符串包含数据库文件的名称、数据库服务器的名称，或者提供程序能理解的用于查找数据库的名称。

‘user_id’

字符串常量，它是传递到指定 OLE DB 提供程序的用户名。user_id 为连接指定安全上下文，并将它作为 DBPROP_AUTH_USERID 属性传递进来以初始化提供程序。

‘password’

字符串常量，它是将被传递到 OLE DB 提供程序的用户密码。当初始化提供程序时，将 password 作为 DBPROP_AUTH_PASSWORD 属性传递进来。

‘provider_string’

提供程序特定的连接字符串，将它作为 DBPROP_INIT_PROVIDERSTRING 属性传递进来以初始化 OLE DB 提供程序。通常 provider_string 封装初始化提供程序所需的所有连接信息。

catalog

目录或数据库的名称，其中驻留着指定的对象。

schema

架构的名称或指定对象的对象所有者名称。

object

对象名称，它唯一地标识出将要操作的对象。

‘query’

是字符串常量，发送到提供程序并由提供程序执行。

–如果只是临时访问,可以直接用openrowset
–查询示例
select * from openrowset(‘SQLOLEDB’
,‘sql服务器名’;‘用户名’;‘密码’
,数据库名.dbo.表名)

–导入示例
select * into 表 from openrowset(‘SQLOLEDB’
,‘sql服务器名’;‘用户名’;‘密码’
,数据库名.dbo.表名)

错误7405：异类查询要求为连接设置ANSI-NULL和ANSI-WARNNINGS选项，这将确保一致的查询语意，请启用这些选项，然后重新发出查询
解决方法：
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

下面的方法可以参考：
方法一：用OPENDATASOURCE 方法去操作异地数据库
declare @i int
set @i=1
select * from OPENDATASOURCE(‘SQLOLEDB’,‘Data Source=IP地址;User ID=sa;Password=密码’).异地数据库名.dbo.表名
A inner join 本地数据库名…表名 B
on A.关联字段=B.关联字段 and A.字段名称=@i

使用用于 SQL Server 的 Microsoft OLE DB 提供程序访问 pubs 数据库中的 authors 表，该数据库在一个名为 seattle1 的远程服务器上。从 datasource、user_id 及 password 中初始化提供程序，并且使用 SELECT 语句定义返回的行集。

USE pubs

GO文章来源地址https://www.toymoban.com/news/detail-448064.html

SELECT a.*

FROM OPENROWSET(‘SQLOLEDB’,‘seattle1’;‘sa’;‘MyPass’,

‘SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname’) AS a

到了这里，关于SQL SERVER 链接服务器以及 openrowset的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！