Executing Remote Stored Procedures in Triggers

Hi,

I've been scratching my head over this problem for quite a while. I have two SQL SERVER 2005 servers running on the same network, lets call them ServA and ServB. ServB is configured as a linked server on ServA as LinkedServB. On ServA, there is a database called DatabaseA, in which there is a table called TableAA, on which I wrote a trigger on delete.

In that trigger, I want to update a table, lets call it TableBB, in DatabaseB on ServB.

So the trigger looks like this:

CREATE TRIGGER triggerAfterDelete
ON TableAA
AFTER DELETE
AS
BEGIN

SET NOCOUNT ON;

Update [LinkedServB].[DatabaseB].[dbo].[TableBB]
set [SomeColumn] = 'SomeValue'
where [SomeOtherColumn] = 'SomeOtherValue'

END

When I delete something from TableAA, the trigger fires, and on trying to update, an error is raised which is:

Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Now the same query works as a separate query. But inside the trigger it does not. I've tried to use try-catches, nested transaction, named transaction, saving transactions, distributed tran, checking @.@.error, running the query using openquery, running the query using sp_executesql, but they have all given me some error or other.

Selects in the trigger work fine, but updates, inserts and deletes do not work.
And like I mentioned, as a stand alone query, they work fine, as a query, in openquery, or in sp_executesql

Any help would be much appreciated.

Thanks in Advance

Vinit Pandya
Senior .NET Developer

You need to SET XACT_ABORT ON to avoid the error. Otherwise the provider has to support nested transactions for this to work and SQL Server does not support nested transactions. For details on the behavior of distributed queries in transactions, see the BOL topic below:

http://msdn2.microsoft.com/en-us/library/aa213080(SQL.80).aspx

|||Hi Umachandar,

Thanks for your reply. I've actually tried that too, and also other settings which I read helped other people such as:

SET XACT_ABORT ON;
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

But still no luck. Any other ideas ?

Thanks.

Vinit Pandya
Senior .NET Developer

Executing a stored procedure that uses linked server from vb.NET

I can't execute my stored procedure from .NET (I think it might be
because the stored proc has joins in it via linked servers). I get
timeout error from .NET
MyBase.connString ="Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DB;Data Source=Server;"
MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
MyBase.GetDataSet()
The stored procedure takes the 3 arguments and uses joins to 2 other
db-servers that I have access to via linked server connections. (MS SQL
Server)
I can run the stored proc on in QA on my MS SQL with no prob.
Must i open connections to the 2 linked servers from .NET as well?
Glad for any opinions so i dont have to rewrite my sp,
Mikaelbermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?|||> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?
> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>> How long does it take to run in QA, and how long is your timeout set for
>> in .NET?
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M|||bermik wrote:
> > I'm no .NET programmer, but with typical web apps you have a CONNECTION
> > timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> > So, in a nutshell, if your query takes longer than 30 seconds, the web
> > connection is going to give up and report a timeout.
> Not a web app, but still makes sense, will check the times. My test
> procedure worked out fine so it should have something to do with the
> load and not linked server, thanks!
> /M
...and timeout it was thanks for your help!
(MyBase.cmdTimeout = 15000)

Execute SP on linked server?

I've created a stored procedure on several servers. All with the same name and all in a database with same names.
Now Im trying to execute the stored procedures by executing a script... I dont get it to work!
I can execute it successfully by starting it manually:
exec [CIMPDB01\CIMT1].zz_am.dbo.usp_SpaceMon
exec [CIMPDB01\CIMT2].zz_am.dbo.usp_SpaceMon
and so on.... no problem

But when I try to execute it by using a script (and substitute the instance names with values taken from a table) it wont:

declare @.x int
declare @.dbname varchar(500)
declare @.SQL nvarchar(600)
set @.x = 1

create table #databases
(ID int IDENTITY,name varchar(500))

insert #databases select instancelongname from instances where actief='J'

while @.x <= (select max(id) from #databases)
begin
select @.dbname = name from #databases where id = @.x
print @.dbname
select @.SQL='exec ' + @.dbname + '.zz_am.dbo.usp_SpaceMon'
print @.SQL
execute @.SQL

set @.x = @.x + 1
end

drop table #databases

Msg 203, Level 16, State 2, Line 17
The name 'exec [CIMPDB01\CIM].zz_am.dbo.usp_SpaceMon' is not a valid identifier.

Please help...declare @.x int
declare @.dbname varchar(500)
declare @.SQL nvarchar(600)
set @.x = 1

create table #databases
(ID int IDENTITY,name varchar(500))

insert #databases select instancelongname from instances where actief='J'

while @.x <= (select max(id) from #databases)
begin
select @.dbname = name from #databases where id = @.x
print @.dbname
select @.SQL= @.dbname + '.zz_am.dbo.usp_SpaceMon'
print @.SQL
execute @.SQL

set @.x = @.x + 1
end

drop table #databases

Don't need to use 'exec'.

Hope this helps,

David