Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> SQL Server >> SQL Service Broker

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.

[233 byte] By [artsai] at [2007-12-24]

«« Accessing functions
»» Migrating OLAP Cubes from Analysis Services version 8.00 from SQL Server 2000 to SQL Server 2005

# 1

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?

RushiDesai at 2007-10-8 >

top of Msdn Tech,SQL Server,SQL Service Broker...

# 2

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

artsai at 2007-10-8 >

# 3

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

RushiDesai at 2007-10-8 >

# 4

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.

artsai at 2007-10-8 >

# 5

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

RemusRusanu at 2007-10-8 >

# 6

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.

artsai at 2007-10-8 >

# 7

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

artsai at 2007-10-8 >

# 8

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

RemusRusanu at 2007-10-8 >

SQL Server

SQL Service Broker

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech