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

execute SSIS package stored in remote SQL server from a client machine.

I have written a VB component to execute a SSIS package stored in SQL server.

I am using the Microsoft.sqlserver.dts.dtsclient, dtsconnection/dtscommand

and executereader and everything works perfect on the local machine. This is descibed in a msdn site .

But my customer have a remote SQL server and want no extra BI clients/SQL server clients etc on the client machine, only an ordinary windows client and network connection to the SQL server.

My question is: Can you still use dtsclient or do you have to do in some other way?

rose-marie

It depends where your VB component is going to be running. You will need SSIS installed on the same machine.

-Jamie

|||

The whole idea was to run the VB component in a client machine without any extra i.e without SSIS.

I understand that this is not possible, is it possible to achieve this in another way?

rose-marie

|||

Can you not run the component somewhere else?

|||

No, in this case there is a demand on a stand-alone windows application

without any dependencies other than oledb connections to SQL server.

Otherwise I would have liked a web service in the server machine or something like that, but...

rose-marie

Execute SSIS package at remote server across domain

Hi,

Is that possible to execute SSIS packages from a remote SQL Server 2005 in another domain at local computer (Windows XP)?

Can I use SQL authentication to connect to the remote SSIS? My problem is, the user who execute the SSIS is not allow to access to SQL server directly. Is that possible to code everything in a batch file instead?

I tried to use web services to execute the remote SSIS but it always return "authentication error"?

Can someone help?

Many thanks!

Regards,

Mashmallow

Going via SQL Server is the simplest. Remote execution of batch files is not really viable,a nd will certainly have issues cross domain, unless you have a trust. I assume not hence the question.

The Books Online link below covers such the SQL Agentand Web Service method, including notes on getting security to work, have you seen it?

Loading and Running a Remote Package Programmatically
(http://msdn2.microsoft.com/en-us/library/ms403355(SQL.90).aspx)

|||

Thanks Darren.

I have seen this link before and I have tried doesn't work. When I try to debug, it say "Authentication error". Do I need to use Window Authentication or SQL Authentication.

I try to use the dtexecui utility from my local machine to call the package from remote server. However, It doesn't allow SQL authentication. I am currently using VPN to dial in to client network before I am able to run the SSIS.

Regarding the link that you have sent to me. I follow the same code and I get result as below:-

1) use window authentication - I get the "Authentication Error" when execute the code

2) use SQL authentication - I do not get error when execute the code but it doesn't trigger the SSIS execution at remote server.

Please help. Many thanks.

Regards,

Mashmallow

Execute Remote Stored Procedure from the receiver's queue

How can my receiver's queue procedure execute a procedure on another server?
Everytime it gets to that remote procedure statement, I get DISCONNECTED_INBOUND status.
The procedure I want to call is on SQL 2000.

The disconnected inbound status is not related to your procedure. You are probably ending the dialog at the initiator after sending the message. When the remote side of a dialog ends it, the conversation endpoint goes into disconnected inbound state.

Are you planning to write a local stored procedure which receives messages and calls a remote procedure on a linked server?

|||

You are right about the status. I took care of that earlier this morning. Right now, the conversation_endpoints table is showing "CONVERSING."

Target's Queue procedure basically is reading the message body and call a remote procedure through a linked server. Can this be done? Right now, the remote procedure is just return a value with output .

create procedure myProc @.input varchar(100), @.output output as

set @.output ='I am here'

return

|||

I haven't tried it myself. Let us know if it works :-)

|||

It doesn't work for me. Can you try that? I did try using Synonym, that didn't help. Is it a permission issue? I did set my Target's queue procedure to execute as owner.

|||

A database EXECUTE AS context cannot use a linked server unless is signed and proper rights are granted on the signature (see http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx for an example) or, a much easier way, mark the database as trustworthy. The dbo must correspond to a login who has proper authority to use the linked server. For testing, do this:

ALTER AUTHORIZATION ON DATABASE::[<db_name_where_Activation_occurs>] TO [sa];
ALTER DATABASE ::[<db_name_where_Activation_occurs>] SET TRUSTWORTHY ON;

HTH,
~ Remus

|||

The problem was not the security at all. I couldn't execute remote stored procedure when it's in a transaction. For now I have to remove the begin tranaction/commit from the Target's queue procedures.. It's working now.

|||

Now that my linked server's procedure is returning results. How can I make this procedure run in parallel? this below is the activation procedure.

Create PROCEDURE [dbo].[Processing_Queue1] AS

begin

declare @.message_type int

declare @.dialogHandle uniqueidentifier,

@.ErrorSave INT,

@.ErrorDesc NVARCHAR(100),

@.message_body XML;

--begin transaction;

waitfor (receive top(1) -- just handle one message at a time

@.message_type = message_type_id,

@.message_body = message_body, -- the message contents

@.dialogHandle = conversation_handle

from Queue1

), TIMEOUT 1000

while (@.dialogHandle is not null) begin

if (@.message_type <> 2) begin

exec mylinkServer.myDB.dbo.ProcessingMessage @.message_body, @.dialogHandle

end

END CONVERSATION @.dialogHandle

-- commit;

set @.dialogHandle = null;

begin transaction

waitfor(receive top(1) -- just handle one message at a time

@.message_type = message_type_id,

@.message_body = message_body, -- the message contents

@.dialogHandle = conversation_handle

from Queue1

), TIMEOUT 1000

end

--commit;

end

|||

It's the WAITFOR. Remove the WAITFOR and put back the transactions. See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=555948&SiteID=1

HTH,
~ Remus

Execute Remote Stored Procedure from the receiver's queue

How can my receiver's queue procedure execute a procedure on another server?
Everytime it gets to that remote procedure statement, I get DISCONNECTED_INBOUND status.
The procedure I want to call is on SQL 2000.

The disconnected inbound status is not related to your procedure. You are probably ending the dialog at the initiator after sending the message. When the remote side of a dialog ends it, the conversation endpoint goes into disconnected inbound state.

Are you planning to write a local stored procedure which receives messages and calls a remote procedure on a linked server?

|||

You are right about the status. I took care of that earlier this morning. Right now, the conversation_endpoints table is showing "CONVERSING."

Target's Queue procedure basically is reading the message body and call a remote procedure through a linked server. Can this be done? Right now, the remote procedure is just return a value with output .

create procedure myProc @.input varchar(100), @.output output as

set @.output ='I am here'

return

|||

I haven't tried it myself. Let us know if it works :-)

|||

It doesn't work for me. Can you try that? I did try using Synonym, that didn't help. Is it a permission issue? I did set my Target's queue procedure to execute as owner.

|||

A database EXECUTE AS context cannot use a linked server unless is signed and proper rights are granted on the signature (see http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx for an example) or, a much easier way, mark the database as trustworthy. The dbo must correspond to a login who has proper authority to use the linked server. For testing, do this:

ALTER AUTHORIZATION ON DATABASE::[<db_name_where_Activation_occurs>] TO [sa];
ALTER DATABASE ::[<db_name_where_Activation_occurs>] SET TRUSTWORTHY ON;

HTH,
~ Remus

|||

The problem was not the security at all. I couldn't execute remote stored procedure when it's in a transaction. For now I have to remove the begin tranaction/commit from the Target's queue procedures.. It's working now.

|||

Now that my linked server's procedure is returning results. How can I make this procedure run in parallel? this below is the activation procedure.

Create PROCEDURE [dbo].[Processing_Queue1] AS

begin

declare @.message_type int

declare @.dialogHandle uniqueidentifier,

@.ErrorSave INT,

@.ErrorDesc NVARCHAR(100),

@.message_body XML;

--begin transaction;

waitfor (receive top(1) -- just handle one message at a time

@.message_type = message_type_id,

@.message_body = message_body, -- the message contents

@.dialogHandle = conversation_handle

from Queue1

), TIMEOUT 1000

while (@.dialogHandle is not null) begin

if (@.message_type <> 2) begin

exec mylinkServer.myDB.dbo.ProcessingMessage @.message_body, @.dialogHandle

end

END CONVERSATION @.dialogHandle

-- commit;

set @.dialogHandle = null;

begin transaction

waitfor(receive top(1) -- just handle one message at a time

@.message_type = message_type_id,

@.message_body = message_body, -- the message contents

@.dialogHandle = conversation_handle

from Queue1

), TIMEOUT 1000

end

--commit;

end

|||

It's the WAITFOR. Remove the WAITFOR and put back the transactions. See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=555948&SiteID=1

HTH,
~ Remus

execute remote stored procedure failed

I have set up a linked server to Sybase. I can run
distributed queries, but I'm trying to execute a stored
procedure on the remote server and I receive the
following message:
Error 7212: Could not execute procedure on remote server.
Any ideas as to what I'm doing wrong?
Thanks,
MTMT
Perhaps you want to look into permission issues
"MT" <anonymous@.discussions.microsoft.com> wrote in message
news:0d4b01c39e26$7ddf7c10$a301280a@.phx.gbl...
> I have set up a linked server to Sybase. I can run
> distributed queries, but I'm trying to execute a stored
> procedure on the remote server and I receive the
> following message:
> Error 7212: Could not execute procedure on remote server.
> Any ideas as to what I'm doing wrong?
> Thanks,
> MT
>
>
>|||Was there ever a satisfactory solution to this? I am having very similar problems when using a Sybase OLE DB linked server. No such problems when using an ODBC link
-- MT wrote: --
I have set up a linked server to Sybase. I can run
distributed queries, but I'm trying to execute a stored
procedure on the remote server and I receive the
following message
Error 7212: Could not execute procedure on remote server
Any ideas as to what I'm doing wrong
Thanks
M

Execute installation script.

Hello. I will export the database generated script for all objects, I want to make an installer that executes that script on the remote server, I think the installer must ask for sa password; anyway thats not the problem, How can I make with SMO execute an script file?

Thanks

SMO expects you to either use the SMO object model to modify the database interactivly or else script the changes and run them externally later. If you are just executing SQL script, you can use sqlcmd.exe for that.

For something fancier that gets passwords and other user input and then runs the script, you would need to write a wrapper script/application to gather the user input and then send it off to the server.|||If you see DotNetNuke when you navigate to the first page it takes a lot of time, that page creates all the objects on the database taking as input some .sql file that are already on the package. However its in .net 1.1 and not 2.0 and I want to use SMO.

It seems to be very difficult because I havent find good info about this.

Tks|||Use Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery Method with your script in parameter
(at first you must create a database)|||How can I Pass this method a .sql file?|||You can do something like:

using System.IO;

~~~~~~~~~~~~~~~
Server srv = new Server("MyServer");
string filePath = "c:\\create.sql";

FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);

StreamReader sr = new StreamReader(file);

string s = sr.ReadToEnd();

sr.Close();

srv.Databases["tempdb"].ExecuteNonQuery(s);

|||I use this:

string script = System.IO.File.ReadAllText(file);

Execute installation script.

Hello. I will export the database generated script for all objects, I want to make an installer that executes that script on the remote server, I think the installer must ask for sa password; anyway thats not the problem, How can I make with SMO execute an script file?

Thanks

SMO expects you to either use the SMO object model to modify the database interactivly or else script the changes and run them externally later. If you are just executing SQL script, you can use sqlcmd.exe for that.

For something fancier that gets passwords and other user input and then runs the script, you would need to write a wrapper script/application to gather the user input and then send it off to the server.|||If you see DotNetNuke when you navigate to the first page it takes a lot of time, that page creates all the objects on the database taking as input some .sql file that are already on the package. However its in .net 1.1 and not 2.0 and I want to use SMO.

It seems to be very difficult because I havent find good info about this.

Tks|||Use Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery Method with your script in parameter
(at first you must create a database)|||How can I Pass this method a .sql file?|||You can do something like:

using System.IO;

~~~~~~~~~~~~~~~
Server srv = new Server("MyServer");
string filePath = "c:\\create.sql";

FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);

StreamReader sr = new StreamReader(file);

string s = sr.ReadToEnd();

sr.Close();

srv.Databases["tempdb"].ExecuteNonQuery(s);

|||I use this:

string script = System.IO.File.ReadAllText(file);

Execute Command Process - remote batch file?

Good afternoon-
Can a batch file that resides on another server be executed from a different machine? I have a batch file that resides on a server that I would like to run using SQL 2005 Integration Services. Is there anything I can do that would allow me to remotely execute this batch file and have it run in that environment.
BATCH FILE:

cd C:\Trandev\
otrun -at OTRecogn.att -DINPUT_FILE=%1 -tl 1 -cs dv -lg mylog -I
C:\Trandev represents the remote environment

I have tried mapping the remote machine to a network drive on my local machine and using that drive to execute the batch file in an Execute Process Task, but it does not work.

SSIS:

I have a FOR EACH loop grabbing files and writing fileName to a variable that is passed to the Process Task as an argument through an expression(%1 in the batch file above). The Working Directory is a mapped network drive. The Executable is also a network drive plus batch file name.

Any help would be appreciated.
My computer is a HP Compaq dc7100, 512mb RAM, WindowsXP

An error message (if there is one) is more use than just saying "it doesn't work".

-Jamie

|||

[Execute Process Task] Error: In Executing "V:\z_811Test.bat" "edi_txt_test.in383" at "V:\", The process exit code was "1" while the expected was "0".

V:\ is a mapped network drive representing the environment where I wish to run the batch file.

James