菜鸟请教有关在存储过程中无法创建远程连接的问题? 谢谢.

[disneyzhang]

CREATE PROCEDURE test_JO_D1124
AS

exec sp_addlinkedserver 'srv_link','','SQLOLEDB','OC_CHINA'

exec sp_addlinkedsrvlogin 'srv_link','false',null,'sa','12345'


SET REMOTE_PROC_TRANSACTIONS ON

BEGIN DISTRIBUTED TRANSACTION


update A
set A.PR_M_UDF8_NV='computer2'
FROM srv_link.jmidb.dbo.INM_PUR_M as A
WHERE (A.PR_M_PRNO_C = 000001)


update dbo.INM_PUR_M
set PR_M_UDF8_NV='computer1'
FROM dbo.INM_PUR_M
WHERE (PR_M_PRNO_C = '000002')


COMMIT TRANSACTION

exec sp_dropserver 'srv_link','droplogins'

GO


建立上述存储过程,用来更新远程数据库及本地数据数据库的数据保存时出下面提示:

Error 7202: Could not find server 'srv_link' in sysservers,Execute sp_addlinkedserver to add the server to sysservers


请问是什么原因,我试过在SQL Query Analyzer是可以创建远程连接的.
谢谢!

[gc_ding]

帮楼主试了好象也是不行，不过用两种方法解决：
（1）用两个存储过程
CREATE PROCEDURE test_JO_D1124_1
AS
exec sp_addlinkedserver 'srv_link','','SQLOLEDB','OC_CHINA'
exec sp_addlinkedsrvlogin 'srv_link','false',null,'sa','12345'
go

CREATE PROCEDURE test_JO_D1124_2
AS
SET REMOTE_PROC_TRANSACTIONS ON
BEGIN DISTRIBUTED TRANSACTION
update A
set A.PR_M_UDF8_NV='computer2'
FROM srv_link.jmidb.dbo.INM_PUR_M as A
WHERE (A.PR_M_PRNO_C = 000001)

update dbo.INM_PUR_M
set PR_M_UDF8_NV='computer1'
FROM dbo.INM_PUR_M
WHERE (PR_M_PRNO_C = '000002')
COMMIT TRANSACTION
exec sp_dropserver 'srv_link','droplogins'
go

(2)直接用openrowset(临时访问)
CREATE PROCEDURE test_JO_D1124
AS
SET REMOTE_PROC_TRANSACTIONS ON
BEGIN DISTRIBUTED TRANSACTION
update A
set A.PR_M_UDF8_NV='computer2'
FROM openrowset('SQLOLEDB','OC_CHINA';'sa';'12345',jmidb.dbo.INM_PUR_M) as A
WHERE (A.PR_M_PRNO_C = 000001)

update dbo.INM_PUR_M
set PR_M_UDF8_NV='computer1'
FROM dbo.INM_PUR_M
WHERE (PR_M_PRNO_C = '000002')
COMMIT TRANSACTION
GO

[zjcxc]

CREATE PROCEDURE test_JO_D1124
AS

exec sp_addlinkedserver 'srv_link','','SQLOLEDB','OC_CHINA'

exec sp_addlinkedsrvlogin 'srv_link','false',null,'sa','12345'

EXEC('
SET REMOTE_PROC_TRANSACTIONS ON

BEGIN DISTRIBUTED TRANSACTION


update A
set A.PR_M_UDF8_NV=''computer2''
FROM srv_link.jmidb.dbo.INM_PUR_M as A
WHERE (A.PR_M_PRNO_C = 000001)


update dbo.INM_PUR_M
set PR_M_UDF8_NV=''computer1''
FROM dbo.INM_PUR_M
WHERE (PR_M_PRNO_C = ''000002'')


COMMIT TRANSACTION
')
exec sp_dropserver 'srv_link','droplogins'

GO

[disneyzhang]

非常感谢gc_ding(施主,给个妞泡好么), 邹老师的回复.

[kourr2004]

路过学习：）

[kempinfo]

路过学习：）