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)'
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).
Microsoft Online Community Support