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

    Question

  • 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

    Thursday, January 19, 2012 4:48 PM

All replies

  • 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

    Tuesday, January 24, 2012 9:59 AM
  • 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

    Wednesday, January 25, 2012 9:04 PM
    Moderator