Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
manual execution of stored procedure works, activation by service broker queue fails

Unanswered manual execution of stored procedure works, activation by service broker queue fails

  • Thursday, January 19, 2012 4:48 PM
     
      Has Code
    Sign In to Vote
    0

    As explained in the title the following stored procedure executes fine except when called from service broker queue.

    Since they do not change the result of the procedure i've removed transactions, error checking etc.

    ALTER PROCEDURE [dbo].[sp_processrequestmessages]
AS
DECLARE @cg UNIQUEIDENTIFIER
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE @messagebody XML;
DECLARE @sqlquery VARCHAR(MAX);

RECEIVE TOP(1)
	@cg = conversation_group_id,
	@ch = conversation_handle,
	@messagetypename = message_type_name,
	@messagebody = CAST(message_body AS XML)
FROM [LDL Server].[dbo].[TargetQueue]

IF (@@ROWCOUNT > 0) BEGIN	
	SET @sqlquery = CAST(@messagebody AS VARCHAR(MAX))
	EXEC (@sqlquery)
END

END CONVERSATION @ch;

    So basically what i do is execute the query which is passed as a message.

    I don't know if it would be usefull but underneath is a simplified example of the query.
    The idea is to update the local table with values from a remote table.

    UPDATE 
	localtable
SET 
	localtable.[namefield] = remotetable.[namefield],
	localtable.[imagefield] = remotetable.[imagefield],
FROM 
	(SELECT id, namefield, imagefield FROM [localserver].[localdatabase].[dbo].[localtable] WHERE (id = idvalue)) localtable
INNER JOIN     
	OPENQUERY([linkedserver_origin], ''SELECT id, namefield, imagefield FROM [database].[dbo].[remotetable] where (id = idvalue)'') remotetable	
ON (localtable.id = remotetable.id)'

     TIA

     

All Replies

  • Tuesday, January 24, 2012 9:59 AM
    Moderator
     
     
    Sign In to Vote
    0
    Hi Peter Olivier,

    I would like to involve someone familiar with to have a look, and give an update later. Thanks for your understanding.

    Stephanie Lv

    TechNet Community Support

     
  • Wednesday, January 25, 2012 9:04 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Peter,

    I would expect there to be an error message within the errorlog that indicates there has been a failure. If not, it would be useful to check the Broker:Activation Event Class (http://msdn.microsoft.com/en-us/library/ms186262(v=SQL.100).aspx), to see whether there was a start, an end or abort.

    It may also be useful to add the Stored Procedures Event Category's SP:StmtStarting and SP:StmtCompleted events to the same profiler trace (and perhaps all events under the Errors and Warnings event category).

    The execution context of a Queue's activation stored procedure is documented in "Queue Properties (General Page)" (http://msdn.microsoft.com/en-us/library/cc280596(v=SQL.100).aspx). 

    The security context for internal activation is documented in "Internal Activation Context" (http://msdn.microsoft.com/en-us/library/ms171585.aspx).

    Troubleshooting Activation Stored Procedures (http://msdn.microsoft.com/en-us/library/ms166102.aspx) mentions the use of Execute As when testing via SSMS or SQLCMD.

    It may be useful to review the above two topics about context, and adjust SSMS testing (if needed).

    Thanks,

    Cathy Miller

    Microsoft Online Community Support