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