use master go begindeclare @Cnt int select @Cnt=count(*) from dbo.sysservers where srvname='Sybase'if @Cnt>0 print 'Link Server Sybase already exists'end
怪事,sp_addlinkedserver 用在存储过程里面就报错,单独执行就没问题。存储过程: CREATE PROCEDURE dbo.test AS declare @connstrto21 varchar(200) select @connstrto21 = dbo.getconnstr() EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21 go select top 1 * from server1.database1.dbo.table1 exec sp_droplinkedsrvlogin 'server1','sa' exec sp_dropserver 'server1' go错误信息: Error 7202:Could not find server 'server1' in sysservers ,execute sp_addlinkedserver to add the server to sysservers 单独执行: declare @connstrto21 varchar(200) select @connstrto21 = dbo.getconnstr() EXEC sp_addlinkedserver 'database1','','MSDASQL',NULL,NULL,@connstrto21 go select top 1 * from server1.database1.dbo.table1 exec sp_droplinkedsrvlogin 'database1','sa' exec sp_dropserver 'database1' 就没有错误。说明:dbo.getconnstr() 是获得连接的函数。 CREATE FUNCTION dbo.getconnstr() RETURNS varchar(200) AS BEGIN return 'DRIVER={SQL Server};SERVER=database1;UID=sa;PWD=;' END
分开来这样可以的CREATE PROCEDURE dbo.test AS declare @connstrto21 varchar(200) select @connstrto21 = dbo.getconnstr() EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21 go exec testthen:select top 1 * from server1.database1.dbo.table1sql在执行select语句的前提是要在sysservers 中找到服务器 'test' 而此时还为运行sp_addlinkedserver,所以语句不能执行
CREATE PROCEDURE dbo.test AS declare @connstrto21 varchar(200) select @connstrto21 = dbo.getconnstr() EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21 go select top 1 * from server1.database1.dbo.table1 exec sp_droplinkedsrvlogin 'server1','sa' exec sp_dropserver 'server1' go 的错误在于预编译做语法及相关检查时,并没有执行exec sp_addlikedserver,也就不存在server1,所以检查时select top 1 * from server1.database1.dbo.table1及以后的语句时就会出错。 注意,只有执行一次EXEC sp_addlinkedserver以后,以后预编译才不会有错
少看了一个go,说得有点错误,其实就是加一句 CREATE PROCEDURE dbo.test AS declare @connstrto21 varchar(200) select @connstrto21 = dbo.getconnstr() EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21 go exec dbo.test --加入运行 select top 1 * from server1.database1.dbo.table1 exec sp_droplinkedsrvlogin 'server1','sa' exec sp_dropserver 'server1' go 就行了
如果想把所有的写在一个存储过程,可以这样改 CREATE PROCEDURE dbo.test AS declare @connstrto21 varchar(200) declare @str nvarchar(200) select @connstrto21 = dbo.getconnstr() if not exists(select 1 from master.dbo.sysservers where srvname = 'server1') EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21 select @str = N'select top 1 * from server1.database1.dbo.table1' exec sp_executesql @str exec sp_droplinkedsrvlogin 'server1','sa' exec sp_dropserver 'server1' go
to 蝈蝈俊老大!CREATE PROCEDURE dbo.test AS declare @connstrto21 varchar(200) select @connstrto21 = dbo.getconnstr() if exists(select 1 from master.dbo.sysservers where srvname = 'server1') begin exec sp_droplinkedsrvlogin 'server1','sa' exec sp_dropserver 'server1' end EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21 go----你都没有语句调用test下面的当然会错select top 1 * from server1.database1.dbo.table1 if exists(select 1 from master.dbo.sysservers where srvname = 'server1') begin exec sp_droplinkedsrvlogin 'server1','sa' exec sp_dropserver 'server1' end go
连接另外一台服务器后,如何执行另外一个数据库服务器上的存储过程呢??CREATE PROCEDURE dbo.test AS declare @connstrto21 varchar(200) declare @str nvarchar(200) select @connstrto21 = dbo.getconnstr() if not exists(select 1 from master.dbo.sysservers where srvname = 'server1') EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21这里如何写??? exec server1.database1.dbo.procedure1 参数1,参数2 output 这样写不行呀。 exec sp_droplinkedsrvlogin 'server1','sa' exec sp_dropserver 'server1' go
kao 现在事务处理又没问题了。 不过又有一个新的问题。 CREATE PROCEDURE dbo.test AS declare @connstrto21 varchar(200) select @connstrto21 = dbo.getconnstr() EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21 if (select count(*) from server1.database1.dbo.table1 where …… ) > 0 begin 这个判断语句如何写呀?? end exec sp_droplinkedsrvlogin 'server1','sa' exec sp_dropserver 'server1' go错误信息: Server: Msg 7202, Level 11, State 2, Procedure ghj1976_move_data_del, Line 14 Could not find server 'server1' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
CREATE PROCEDURE dbo.test AS declare @connstrto21 varchar(200) select @connstrto21 = dbo.getconnstr() EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21declare @str nvarchar(200) declare @a intselect @str = N'select @a=count(*) from server1.database1.dbo.table1 where …… ' exec sp_executesql @str,N'@a int output',@a outputif (@a > 0) begin --代码 end exec sp_droplinkedsrvlogin 'server1','sa' exec sp_dropserver 'server1'
go
begindeclare @Cnt int
select @Cnt=count(*) from dbo.sysservers where srvname='Sybase'if @Cnt>0 print 'Link Server Sybase already exists'end
CREATE PROCEDURE dbo.test AS
declare @connstrto21 varchar(200)
select @connstrto21 = dbo.getconnstr()
EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21
go
select top 1 * from server1.database1.dbo.table1
exec sp_droplinkedsrvlogin 'server1','sa'
exec sp_dropserver 'server1'
go错误信息:
Error 7202:Could not find server 'server1' in sysservers ,execute sp_addlinkedserver to add the server to sysservers 单独执行:
declare @connstrto21 varchar(200)
select @connstrto21 = dbo.getconnstr()
EXEC sp_addlinkedserver 'database1','','MSDASQL',NULL,NULL,@connstrto21
go
select top 1 * from server1.database1.dbo.table1
exec sp_droplinkedsrvlogin 'database1','sa'
exec sp_dropserver 'database1'
就没有错误。说明:dbo.getconnstr() 是获得连接的函数。
CREATE FUNCTION dbo.getconnstr()
RETURNS varchar(200)
AS
BEGIN
return 'DRIVER={SQL Server};SERVER=database1;UID=sa;PWD=;'
END
declare @connstrto21 varchar(200)
select @connstrto21 = dbo.getconnstr()
EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21
go
exec testthen:select top 1 * from server1.database1.dbo.table1sql在执行select语句的前提是要在sysservers 中找到服务器 'test'
而此时还为运行sp_addlinkedserver,所以语句不能执行
declare @connstrto21 varchar(200)
select @connstrto21 = dbo.getconnstr()
EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21
go
select top 1 * from server1.database1.dbo.table1
exec sp_droplinkedsrvlogin 'server1','sa'
exec sp_dropserver 'server1'
go
的错误在于预编译做语法及相关检查时,并没有执行exec sp_addlikedserver,也就不存在server1,所以检查时select top 1 * from server1.database1.dbo.table1及以后的语句时就会出错。
注意,只有执行一次EXEC sp_addlinkedserver以后,以后预编译才不会有错
CREATE PROCEDURE dbo.test AS
declare @connstrto21 varchar(200)
select @connstrto21 = dbo.getconnstr()
EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21
go
exec dbo.test --加入运行
select top 1 * from server1.database1.dbo.table1
exec sp_droplinkedsrvlogin 'server1','sa'
exec sp_dropserver 'server1'
go
就行了
CREATE PROCEDURE dbo.test AS
declare @connstrto21 varchar(200)
declare @str nvarchar(200)
select @connstrto21 = dbo.getconnstr()
if not exists(select 1 from master.dbo.sysservers where srvname = 'server1')
EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21
select @str = N'select top 1 * from server1.database1.dbo.table1'
exec sp_executesql @str
exec sp_droplinkedsrvlogin 'server1','sa'
exec sp_dropserver 'server1'
go
declare @connstrto21 varchar(200)
select @connstrto21 = dbo.getconnstr()
if exists(select 1 from master.dbo.sysservers where srvname = 'server1')
begin
exec sp_droplinkedsrvlogin 'server1','sa'
exec sp_dropserver 'server1'
end
EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21
go----你都没有语句调用test下面的当然会错select top 1 * from server1.database1.dbo.table1
if exists(select 1 from master.dbo.sysservers where srvname = 'server1')
begin
exec sp_droplinkedsrvlogin 'server1','sa'
exec sp_dropserver 'server1'
end
go
declare @connstrto21 varchar(200)
declare @str nvarchar(200)
select @connstrto21 = dbo.getconnstr()
if not exists(select 1 from master.dbo.sysservers where srvname = 'server1')
EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21这里如何写???
exec server1.database1.dbo.procedure1 参数1,参数2 output
这样写不行呀。
exec sp_droplinkedsrvlogin 'server1','sa'
exec sp_dropserver 'server1'
go
不过又有一个新的问题。
CREATE PROCEDURE dbo.test AS
declare @connstrto21 varchar(200)
select @connstrto21 = dbo.getconnstr()
EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21
if (select count(*) from server1.database1.dbo.table1 where …… ) > 0
begin
这个判断语句如何写呀??
end
exec sp_droplinkedsrvlogin 'server1','sa'
exec sp_dropserver 'server1'
go错误信息:
Server: Msg 7202, Level 11, State 2, Procedure ghj1976_move_data_del, Line 14
Could not find server 'server1' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
declare @connstrto21 varchar(200)
select @connstrto21 = dbo.getconnstr()
EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21declare @str nvarchar(200)
declare @a intselect @str = N'select @a=count(*) from server1.database1.dbo.table1 where …… '
exec sp_executesql @str,N'@a int output',@a outputif (@a > 0)
begin
--代码
end
exec sp_droplinkedsrvlogin 'server1','sa'
exec sp_dropserver 'server1'
又有新问题了,并发请求的问题。