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