如何解决使用 sp_addlinkedserver 连接远程服务器数据库的并发问题？？？？

比如这样的一个存储过程∶
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
说明：
dbo.getconnstr() 函数是返回远程计算机数据库的连接字符串，如下：
CREATE FUNCTION dbo.getconnstr()  
RETURNS varchar(200)
AS  
BEGIN 
return  'DRIVER={SQL Server};SERVER=server1;UID=sa;PWD=;'
END
如果两个人同时调用这个存储过程dbo.test，当一个执行完毕，另一个还没有执行完毕的时候，执行完毕的就把server1这个连接关闭了，这样没执行完毕的就无法使用这个连接了。

如何解决这个问题，
当然：永远不关闭连接是一个选择，但是总觉得不好，也就是
exec sp_droplinkedsrvlogin  'server1','sa'
exec sp_dropserver  'server1'
这里如何可以判断这个连接正在使用，不应该被关闭？？？使用全局变量？？？那如果某个存储过程中间失败，那就永远不能关闭了？？


对sp_addlinkedserver 判断需要连接的服务器已经被连接看：
http://expert.csdn.net/Expert/topic/1511/1511647.xml
这个贴子。

37 个解决方案

#1

做触发器呀，SQL有是否链接的线程系统参数，直接使用就行了，触发器发现在还有其它线程链接的话，就不关掉数据，否则就关掉。
或者是你做个指针，用new 的方法另开一条线程，那么你另一条线程就管不着它了

#2

:)

#3

我总觉得...................................





EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21
..................

exec sp_droplinkedsrvlogin  'server1','sa'
exec sp_dropserver  'server1'

上面那样做不妥，理由如下：

#4

如果这个存储过程被频繁地调用，那个他就会重复执行addlink、 droplink和dropserver操作。能不能换一种方式，，，就像实现连接pooling一样。

#5

to: CoolSlob(昨日的友情，早已消失在黄昏的牛粪火中)
能实现数据缓存池的方法那当然是最好的，但是找不到实现的方法呀。

to : tenflee(阿牛哥)
做触发器呀，SQL有是否链接的线程系统参数，直接使用就行了，触发器发现在还有其它线程链接的话，就不关掉数据，否则就关掉。
我只看到有表的触发器，其他的触发器，也就是针对这个存储过程的触发器如何写呀？？
最好给出范列。谢谢。

或者是你做个指针，用new 的方法另开一条线程，那么你另一条线程就管不着它了
这个更晕了，sql中如何用指针呀，没听说过。

#6

同意CoolSlob

我觉得不关闭连接是最好的，因为
sp_addlinkedserver 
sp_droplinkedsrvlogin  
sp_dropserver  
也需要花费时间
但是不要用sa，加个其他用户，保护一下密码

#7

to 蝈蝈俊
  联结服务器最好不要动态建立效率不好，且也没有必要，你的服务器又不是手提

要不你可以做成界面，连远程服务名、用户名、密码由界面输入

如果你坚持你可以建立##全局临时表如：
每次调用的时候看看它是否存在：

if not exists(select 1 from master.dbo.sysservers where srvname = 'server1')
begin
  EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21
  create table ##全局临时表(flag int identity(1,1))     --如果不在就建立临时表
end
  insert ##全局临时表 default values  --插入一条

......你的复杂运算

  delete ##全局临时表 where id=(select min(id) from ##全局临时表)

if (select count(*) from ##全局临时表)=0
begin
  exec sp_droplinkedsrvlogin  'server1','sa'
  exec sp_dropserver  'server1'
  drop table ##全局临时表
end

#8

用sp_addlinkedserver 建立的连接，什么时间会自动释放？？？是不是不会自动释放？？（数据库从起这些不算）

那么我需要连接的地方都用：
if not exists(select 1 from master.dbo.sysservers where srvname = 'server1')
begin
  EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21
end
同时我不释放，是不是就会只有一个连接？？？其他请求的时候，也是用的这个连接？？？

#9

关注
印象：建立的连接是不会自动释放的。

我想可以建个表记录连接日志，记录登录的线程什么的信息

做个任务，定时清理连接。

去做试验我

#10

sp_addlinkedserver 建立的连接服务器，不会自动释放，包括重启，注意sp_addlinkedserver建立的是链接的服务器。
至于每次访问的连接，是怎么释放，没有研究过，不过我想，既然是通过OLE DB 连接的，会自动释放的

#11

to :  ghj1976(蝈蝈俊.net) 

我指的是在程序中做指针　：），不是在SQL中，SQL只有游标，没有指针

触发器是用也是用SQL代码实现的，表可以触发，代码之间也可以，但我自己不会写，我用的时间是抄我以前朋友给我的，按照他给我的框架改动一下而已。

#12

另外，还有个问题。建立连接后，
执行远程服务器的数据库的存储过程（或者函数）如何写？？

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

#13

exec('EXEC sp_addlinkedserver '''+@变量+''','''','''+@变量2+''',NULL,NULL,'+@connstrto21)

#14

如果用：
exec server1.database1.dbo.procedure1 参数1,参数2 output

方式来写的话，会报：
Server 'server1' is not configured for RPC.
错误。

#15

执行过程：

select * from openquery(别名,'exec aaaa')

函数：

select * from openquery(别名,'select * from aa(123)')

#16

那是因为，没有设置link服务器的选项。

sp_serveroption 'Your server name','rpc',true   --从给定的服务器启用 RPC
go

sp_serveroption 'Your server name','rpc out',true --对给定的服务器启用 RPC。
go

#17

带返回参数和输入参数的如何写呀？？
select * from openquery(别名,'exec aaaa')
这个没带参数呀。

还有这里的别名指啥？？ 数据库的连接别名？？也就是 server1 ？？？

#18

From OpenQuery(server1,'Exec 库名..过程名 12345,123')

#19

谢谢 enhydraboy(努力学习C#) 
执行远程服务器的存储过程可以了。
下面这样写就可以了。

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 sp_serveroption 'server1','rpc out',true 
exec server1.database1.dbo.procedure1 参数1,参数2 output

exec sp_droplinkedsrvlogin  'server1','sa'
exec sp_dropserver  'server1'
go

#20

to  pengdali(大力) :

From OpenQuery(server1,'Exec 库名..过程名 12345,123')
这样的写法仍然没法获得返回参数。
比如：
declare @a varchar(50),@b varchar(50)
exec server1.database1.dbo.procedure1 'ghj1976',@a output,@b output
我想把返回参数读取到@a @b 如何写呀。

#21

如果有输出变量：

From OpenQuery(server1,'declare @ int ; exec sp_executesql N''Exec 库名..过程名 12345,123,@输出变量'',N''@输出变量 int output'',@ output ; select @ 结果')

#22

谢谢：pengdali(大力) 不过你这样写法比较复杂，我采用了enhydraboy的方法，很简单，明了的就实现了。

#23

要调用存储过程，不能用MSDASQL
EXEC sp_addlinkedserver 'server1',N'SQL Server'
EXEC sp_addlinkedsrvlogin 'server1', 'false', NULL, 'SA', 'Password'
...
exec server1.database1.dbo.procedure1 参数1,参数2 output

#24

From OpenQuery(server1,'declare @a varchar(50),@b varchar(50) ; exec sp_executesql N''exec database1..procedure1 ''''ghj1976'''',@a output,@b output'',N''@a varchar(50) output'',N''@b varchar(50) output'',@a output,@b output ; select @a 结果1,@b 结果2')

#25

to :Rewiah(乘长风) 
有个问题：
EXEC sp_addlinkedserver 'server1',N'SQL Server'
EXEC sp_addlinkedsrvlogin 'server1', 'false', NULL, 'SA', 'Password'

这里的server1 是远程服务器的别名，但是远程服务器的名字（或者地址比如192.168.1.1）写到哪里呀？？？

#26

谢谢上面所有人的回复，我现在准备把所有的需要连接另外一台服务器的存储过程都写成下面的方式（不释放连接），连接前判断是不是连接的方式。
也就是下面的方式：
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
go

在并发处理方面，谁还有更好的方案。？？？？thanks

#27

EXEC sp_addlinkedserver 'server1',N'SQL Server',Null,'192.168.1.1'
EXEC sp_addlinkedsrvlogin 'server1', 'false', NULL, 'SA', 'Password'

#28

EXEC sp_addlinkedserver 'server1',N'SQL Server',Null,'[192.168.1.1]'
EXEC sp_addlinkedsrvlogin 'server1', 'false', NULL, 'SA', 'Password'

#29

那就没有并发性了，你干脆把
if not exists(select 1 from master.dbo.sysservers where srvname = 'server1')
    EXEC sp_addlinkedserver 'server1','','MSDASQL',NULL,NULL,@connstrto21
独立出来！


企业管理器-->右键你的实例(就是那个有绿色图标的)-->属性-->连接-->改最大并发连接数为0(无限制)

#30

如果ip还不行：

sqlserver服务器-->开始菜单-->SQLserver-->客户端网络实用工具-->别名-->添加-->写入别名如"大力"-->"网络库"选tcp/ip-->服务器名称写入远程ip或实例名-->OK了

#31

可以了，非常thanks.

不过我发现，连接两台数据库服务器，用sp_addlinkedserver ，效率非常差劲，

我客户端用程序单独连接两台数据库服务器，执行同样的处理，

和客户端连接一台数据库服务器，然后这个数据库服务器自己连接另外一台数据库服务器，就是用一致讨论的sp_addlinkedserver， 

两个效率相比，差很多，后一个老是超时。前一个就没问题。

我现在正在测试，用程序单独调用两个数据库服务器，和两个数据库服务器之间调用时，只互相只调用存储过程（减少服务器通讯的压力），看那个效率更高。

#32

不过真的，很多东西没法只通过存储过程互相调用，比如数据的迁移，真的好麻烦。

#33

是呀！是很麻烦的！效率不用讲了！特别是连接的时候！所以你要把连接独立开来！

#34

晕

星光闪烁阿ing...

#35

来晚了 ^_^

这样也可以用ip的
EXEC sp_addlinkedserver 
   'Test1', --名称
   '', 
   'MSDASQL',
   NULL,
   NULL,
   'DRIVER={SQL Server};SERVER=192.168.0.1;UID=user;PWD=password;'


连接服务器速度是够慢的,模拟过几个用户连接查询,速度不敢恭维

#36

mark,study

#37

up