none
Service Broker and linked server stored procedures

    Question

  • Dear netters,

    I have a stored procedure LoadData that is responsible for loading data from one table to another. Procedure calls for logging of errors/instrumentation regardless of whether the load is successful or not. To implement this type of logging, we defined a linked server (loopback onto the same SQL server 2012, on which the main procedure is running).

    EXEC master.dbo.sp_addlinkedserver @server = N'loopback', @srvproduct=N' ', @provider=N'SQLNCLI', @datasrc=N'DEV', @catalog=N'test'

    Here is the section of the LoadData stored proc that initiates an instrumentation call:

    	exec loopback..dbo.LogProcedureStart	   @i_ProcedureName		= @v_ProcedureName	  ,@o_id				= @v_LogFk output	;

    Procedure works fine. We run it many times in sequence, for different source tables, and wanted to experiment with performance when running it in parallel instead. We implemented a service broker to handle asynchronous procedure executions. Using an example from Asynchronous Procedure

    We got the sample procedure executing fine, but when we went to switch it for the LoadData procedure, the proc simply does not get called (or something is off). The call shows as "CONVERSING" in the queue, and nothing is happening.

    Pulling sections of code showed that when "loopback.." is used, LoadData stored procedure does not work. When "loopback.." is removed, all is fine.

    Given that we do need the logging/instrumentation to be in place regardless of whether the stored procedure is executed asynchronously or not, what can be done to resolve this issue?

    Thank you in advance!

    --Alex

    Saturday, April 05, 2014 8:33 PM

Answers

  • I suspect the activated proc user context is 'dbo'.  Make sure the service broker database is owned by 'sa' so that the 'sa' linked server login mapping is used (example below).  If the activated proc security context is not by dbo, whatever login the proc user execution context maps to needs to be set as the logcallogin;

    ALTER AUTHORIZATION ON DATABASE::test TO sa;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com



    Wednesday, April 09, 2014 12:39 PM

All replies

  • We got the sample procedure executing fine, but when we went to switch it for the LoadData procedure, the proc simply does not get called (or something is off). The call shows as "CONVERSING" in the queue, and nothing is happening.

    Pulling sections of code showed that when "loopback.." is used, LoadData stored procedure does not work. When "loopback.." is removed, all is fine.

    Mostly likely, the stored procedure generating an error when the linked server is used (permissions?).  Check the SQL Server error log for related messages from the activated proc.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, April 05, 2014 11:53 PM
  • I get the following from the log:

    Error: 50000, Unrecoverable error in procedure: 7437: Linked servers cannot be used under impersonation without a mapping for the impersonated login.

    So, I added the following to the linked server definition:

    sp_addlinkedsrvlogin @rmtsrvname = 'loopback' 
         ,@useself = 'FALSE'
    	 ,@locallogin = 'sa'
    	 ,@rmtuser = 'sa'
    	 ,@rmtpassword = 'xxxxxx'
    go
    

    But the error message in the log remains the same. I also tried @useself = 'TRUE', with the same result...

    Any suggestions?

    Than you!

    Monday, April 07, 2014 3:02 PM
  • I suspect the activated proc user context is 'dbo'.  Make sure the service broker database is owned by 'sa' so that the 'sa' linked server login mapping is used (example below).  If the activated proc security context is not by dbo, whatever login the proc user execution context maps to needs to be set as the logcallogin;

    ALTER AUTHORIZATION ON DATABASE::test TO sa;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com



    Wednesday, April 09, 2014 12:39 PM
  • HI, 

    I am using the same approach and facing the same issue. 

    I am using a loopback for the logs but the Service Broker activation stored procedure not working correctly with the loopback, when I remove loopback everythign works fine. I tried changing the owner of the database to sa but still it is not working. 

    Please let me know if you find any solution for this issue ?

    thanks

    Thursday, June 16, 2016 3:18 PM
  • HI 

    I am having the same issue with Service broker Activation Procedure not working when using the Loopback to write to a log table. I tried changing the Owner of DB to sa but still it is not working. Please suggest me if there is any other solution I can try. 

    Thank You 

    Thursday, June 16, 2016 3:25 PM
  • Rather than using a linked server for the loopback, use the CLR.

    It is not very difficult, but if you want an example you can see here:
    http://www.sommarskog.se/error_handling/Part3.html#CLRloopback

    Note that this is part of a bigger example, so may you have to sift through details not of interest to you.

    Thursday, June 16, 2016 9:15 PM
  • Or don't use the loopback for logging.  Instead batch the log records in a table variable and insert them after rollback.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, June 16, 2016 9:30 PM
  • Thanks for responding. For some reasons I cannot modify the logging to move into a table variable. 

    I have another question. When I call the stored procedure from another stored procedure to send the message to the Broker Queue and when the parent stored procedure rollback it rollback/removes the message from queue. is there any way to preserve and continue processing the message in queue even when the parent procedure rollback ?

    thanks 

    Friday, June 17, 2016 1:56 PM
  • >is there any way to preserve and continue processing the message in queue even when the parent procedure rollback ?

    Not without a SENDing the message from a separate session not enlisted in the transaction.  And again a CLR stored procedure is the best way to do this.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Friday, June 17, 2016 3:10 PM
  • Thanks for responding. For some reasons I cannot modify the logging to move into a table variable. 

    Personally, I think the solution with the table variable is not tenable. If you want the logging in many places, you cannot easily shove this into a logging procedure.
    And if you are in a nested procedure, you cannot even do it if you want to log without rolling back. (Because nothing went wrong so far.)

    I have another question. When I call the stored procedure from another stored procedure to send the message to the Broker Queue and when the parent stored procedure rollback it rollback/removes the message from queue. is there any way to preserve and continue processing the message in queue even when the parent procedure rollback ?

    So that is the beauty of Service Broker: it is transactional. So nothing is sent if nothing is committed.

    So if you want the message to be sent anyway, it's another case for that loopback connection.

    Friday, June 17, 2016 9:18 PM