sql2008 存储过程中跨库调用存储过程的问题

Question

exec sp_addlinkedserver   'httplog_server_lnk','','SQLOLEDB',@LOGDBServerName
exec sp_addlinkedsrvlogin  'httplog_server_lnk','false',null,@LOGDBUser,@LOGDBPASSWORD
exec sp_serveroption 'ask_server_lnk','rpc out','true'服务器别名创建后，
使用 exec httplog_server_lnk.a.dbo.sp_ask_update ''
单独执行这个另一个库的存储过程是没有问题的
但是在存储过程中执行这个就出现问题了消息 523，级别 16，状态 12，过程 AuthorAddExpLog，第 11 行
触发器返回了结果集并且/或正在使用 SET NOCOUNT OFF 运行，而另一个未完成的结果集处于活动状态。是不是同时去更新那个表了，或是在更新的时候死锁了

Answer 1

exec sp_addlinkedserver 'ask_server_lnk','','SQLOLEDB',@LOGDBServerName
exec sp_addlinkedsrvlogin 'ask_server_lnk','false',null,@LOGDBUser,@LOGDBPASSWORD
exec sp_serveroption 'ask_server_lnk','rpc out','true'
ALTER procedure [dbo].[sp_Ask_AllocateFund_Auto]
as
declare @amount int;
select @amount=SUM(Reward) from Ask_SourceRecord
where datepart(month,getdate()) - datepart(month,[CreateTime]) = 2declare 
@UserID int,
@UserName nvarchar(50),
@BestAnswerNum int,
@ClassID bigint,
@ClassName nvarchar(50),
@photo nvarchar(300),
@insertReturnID int;select @UserID=UserID,@UserName=UserName,@BestAnswerNum=a 
from v_Ask_MaxNowMonthBestAnswerselect @ClassID=AuthorClassID,@ClassName=AuthorClass,@photo=AuthorPic 
from ask_server_lnk.HHT.dbo.viewUserList 
where ID=@UserID
begin tran
insert into Ask_AllocateRecord(Amount,UserID,UserName,UserBestAnswerNum,ClassID,ClassName,photo)
values(@amount,@UserID,@UserName,@BestAnswerNum,@ClassID,@ClassName,@photo)--问题在这里（在这个库调用另一个库的存储过程，这个语句单独拿出来执行是可以通过的，但是我这里是在一个存储过程中跨库去执行的，这个跨库的过程在下面)
exec ask_server_lnk.HHT.dbo.sp_ask_blcReadXPPointAdd @UserID,222,@amount,0,0if @@ERROR > 0
begin
rollback tran;
end
else
begin
update dbo.Ask_SourceRecord
set IsExist=2
where datepart(month,getdate()) - datepart(month,[CreateTime]) = 1;
commit tran;
end;下面是调用的另一个库的存储过程
alter PROCEDURE [dbo].[sp_ask_blcReadXPPointAdd]
(
@UserID int,
@PutInType int,
@ReadXPPointINN int,
@ItemID int,
@RetState bit output
)
AS
set @RetState=0--默认失败
declare @Exp bigintif(@ReadXPPointINN>0)
begin
exec sp_ask_AddAuthorExp 4,@ReadXPPointINN,@UserID --这里又调用了一个 这个在下面
select  @Exp=[Exp] from AuthorExpAddItem where ID=4
set @Exp=@Exp*@ReadXPPointINN
update UserLstAut set ReadXPPointEarExp=ReadXPPointEarExp+@Exp,ReadXPPointEar=ReadXPPointEar+@ReadXPPointINN where UserID=@UserID
if @PutInType=1
begin
update ArticleSellList set [Exp]=@Exp where AuthorID=@UserID and PayMode=1 and ArticleID=@ItemID
end
set @RetState=1
INSERT INTO BalanceParticular(UserID,UserType,PointIN,PointType,Type,ItemID)Values(@UserID,2,@ReadXPPointINN,1,@PutInType,@ItemID)
end
alter PROCEDURE [dbo].[sp_ask_AddAuthorExp]
(
@ExpItem int=0,
@Num int=1,
@userID int=0
)
ASdeclare @Exp int
declare @NumC int
select @Exp=Exp,@NumC=Num from AuthorExpAddItem where ID=@ExpItem
if (@NumC>0)
begin
declare @uNum int
select @uNum=count(id) from AuthorExpAddLog where datediff(d,Indat,getdate())=0 and userID=@userID and ItemID=@ExpItem
if (@NumC<@uNum+@Num)
begin
goto Out
end
end--现在查到是这句有问题，AuthorExpAddLog这个表里有个触发器，INSERT后触发器执行去UPDATE另一个表的数据
insert into AuthorExpAddLog(ItemID,Num,userID)values(@ExpItem,@Num,@userID)
declare @AuthorExp bigint
declare @AuthorLevel smallint
select @AuthorExp=AuthorExp,@AuthorLevel=AuthorLevel from userList where id=@userID
set @AuthorExp=@AuthorExp+@Exp*@Num
update userList set AuthorExp=@AuthorExp where id=@userIDdeclare @suitLevel smallint
select @suitLevel=max(LevelNum) from AuthorLevel where ExpNeed<=@AuthorExp
if (@AuthorLevel<@suitLevel)
begin
update userList set AuthorLevel=@suitLevel where id=@userID
update AuthorFun set
Fun1=n.Fun1,
Fun2=n.Fun2,
Fun3=n.Fun3,
Fun4=n.Fun4,
Fun5=n.Fun5,
Fun6=n.Fun6,
Fun11=n.Fun11,
Fun12=n.Fun12,
Fun13=n.Fun13,
Fun14=n.Fun14,
Fun15=n.Fun15,
Fun16=n.Fun16,
Fun17=n.Fun17,
Fun18=n.Fun18,
Fun19=n.Fun19,
Fun20=n.Fun20,
Fun21=n.Fun21,
Fun46=n.Fun46
from AuthorLevel as n where UserID=@userID and n.LevelNum=@suitLevel
end
--经验值排行小更新
exec URAuthorExp @userID--这里又调用一个，这个没问题
Out:

Answer 2

问题应该就是那条插入语句，insert后去执行触发器了，然后就出了这个错消息 523，级别 16，状态 12，过程 AuthorAddExpLog，第 11 行
触发器返回了结果集并且/或正在使用 SET NOCOUNT OFF 运行，而另一个未完成的结果集处于活动状态。但是我把exec ask_server_lnk.HHT.dbo.sp_ask_blcReadXPPointAdd @UserID,222,@amount,0,0
这一句拿出来单独执行，就不会有这个问题，奇怪死了，同样都是执行了相关的存储过程的

Answer 3

你找一下这个SET NOCOUNT OFF是不是在触发器里边，这个有时候会引起问题。改为 ON 试试 。

Answer 4

回楼上。。触发器里是SET NOCOUNT NO的刚刚把过程搬到服务器上去，执行就通过了，奇怪，还是不知道在本地为什么会出那个错
总之解决了