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