如何在存储过程中使用链接服务器？

[DataService]

如何在存储过程中使用链接服务器？

[点点星灯]

--訪問不同電腦上的資料庫(遠端只要聯好網就一樣)
--如果經常訪問或資料量大,建議用鏈結伺服器

--創建鏈結伺服器
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','遠端伺服器名或ip位址'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'用戶名','密碼'
exec sp_serveroption 'srv_lnk','rpc out','true'--這個允許調用鏈結伺服器上的存儲過程
go

--查詢示例
select * from srv_lnk.資料庫名.dbo.表名

--導入示例
select * into 表 from srv_lnk.資料庫名.dbo.表名

go
--以後不再使用時刪除鏈結伺服器
exec sp_dropserver 'srv_lnk','droplogins'

--如果只是臨時訪問,可以直接用openrowset
--查詢示例
select * from openrowset('SQLOLEDB'
,'sql伺服器名';'用戶名';'密碼'
,資料庫名.dbo.表名)

--導入示例
select * into 表 from openrowset('SQLOLEDB'
,'sql伺服器名';'用戶名';'密碼'
,資料庫名.dbo.表名)

[DataService]

这个方法我知道，在查询分析器中运行没有问题，但把这些写入存储过程中就行不通了，所以我才问在存储过程中如何使用连接服务器呀。

[点点星灯]

行不通,我想知道报什么错误啊,或有什么提示啊!!

是不是权限不够啊.

[DataService]

这是我写的存储过程，不知道是不是这个过程写的有问题，一检测语法就提示未能连接到服务器srv_lnk


CREATE PROCEDURE CopyRNDSSN
AS

EXEC sp_addlinkedserver 'srv_lnk','','SQLOLEDB','server'
EXEC sp_addlinkedsrvlogin 'srv_lnk','false',null,'uid','pwd'
--GO

DECLARE @NnumIndex INT, @NRNDSSN NVARCHAR(50), @NProductType NVARCHAR(2)
DECLARE TempCursor1 CURSOR LOCAL FORWARD_ONLY STATIC OPTIMISTIC FOR SELECT a.numIndex, a.strRNDSSN, b.strProductType FROM tblSFIS_SI_Shipping_Detailed a INNER JOIN tblSFIS_SI_KeyPartProgram b ON a.strMoNo = b.strMONo WHERE numCopyID = 0
OPEN TempCursor1
FETCH NEXT FROM TempCursor1 INTO @NnumIndex, @NRNDSSN, @NProductType
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO srv_lnk.www_memoright_com.dbo.S_SSNInfo (strSSN, strProductType) VALUES (@NRNDSSN, @NProductType)
UPDATE tblSFIS_SI_Shipping_Detailed SET numCopyID = 1 WHERE numIndex = @NnumIndex
FETCH NEXT FROM TempCursor1 INTO @NnumIndex, @NRNDSSN, @NProductType
END
CLOSE TempCursor1
DEALLOCATE TempCursor1
--GO

EXEC sp_dropserver 'srv_lnk','droplogins'
GO

[DataService]

up

[点点星灯]

--楼主，你的SQL的服务器是叫srv_lnk吗？？，你的数据库对应的是server吗？？？


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 提供程序。

[点点星灯]

请注意各参数的含义,不明白的查"联机帮助"

[Yisa]

试试下面代码: (已测试过)
create proc pjob
as
set nocount on
select * from openrowset('sqloledb', '要连接的服务器名';'sa';'密码', 库名.dbo.表名)
go

exec pjob
是真确的.

[DataService]

TO aw511(点点星灯):
你的这句不是写的很清楚吗,
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','遠端伺服器名或ip位址'
srv_lnk只是注册服务器的别名，也就是使用时引用的名称，真正指定服务器是在第4个参数上。不知我的理解是否正确。

TO Yisa():
我现在不是要读出远程服务器上的数据，而是要插入数据，为了安全我不能在远程服务器上去连本地服务器。

[Yisa]

这样应该可以了吧:
create proc pjob
as
set nocount on
insert into openrowset('sqloledb', '远程服务器名';'sa';'密码', 远程库名.dbo.远程表名)
select * from 本地表名
go

exec pjob

[DataService]

TO Yisa():
呵呵，可能是我说的不够清楚，因为我的本地表和远程表的结构不一样，所以我不能这样直接插入，必须先读出，在插入。不过还是要谢谢你先。

[DataService]

up

[DataService]

不会真的没办法了吧？？

[siwuge]

邦顶

[bugchen888]

1.在企业管理器中配置好linkedserver,并确认该linkedserver可以执行如下语句:
INSERT INTO OPENQUERY(srv_lnk,'select strSSN, strProductType from S_SSNInfo where 1=2')
values ('just test','test just')

2.将存储过程中对linkedserver的插入语句改成如上的语句.
INSERT INTO OPENQUERY(srv_lnk,'select strSSN, strProductType from S_SSNInfo where 1=2')
values (@NRNDSSN, @NProductType)