菜鸟请教有关在存储过程中无法创建远程连接的问题? 谢谢.
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是可以创建远程连接的.
谢谢!
[解决办法]
帮楼主试了好象也是不行，不过用两种方法解决：
（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
[解决办法]
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
