Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31317

logging stored procedure fails when inserting via linked server

Show all headers | View raw

Hi There,

I've created a stored procedure that inserts into a table in my current database using a loopback linked server, this was done because i need the logging transaction to be independent of the current active transaction.

the idea was taken from:
https://blogs.msdn.microsoft.com/sqlprogrammability/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008/

this is the statement used to create the linked server:
	EXEC sp_addlinkedserver @server = N'BIloopback',@srvproduct = N' ',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME
	EXEC sp_serveroption BIloopback,N'remote proc transaction promotion','FALSE' -- we want to ensure transactions do not propogate for logging
	EXEC sp_serveroption BIloopback,N'RPC OUT','TRUE' -- Enable RPC to the given server.


i call my logging proc directly from the code and the logging proc then executes the following dynamic sql:

                        DECLARE @DMLVars nvarchar(1024)
			,       @qry     nvarchar(1024)
			SET @DMLVars = '@LogDate datetime, @RowCount int, @Severity int, @ExtractSession uniqueidentifier, @LogLevel int, @MessageID int, @ProcName nvarchar(128), @Message nvarchar(max)'
			SET @qry = 'INSERT INTO '+isnull(quotename(@LoopbackServer)+'.','')+DB_NAME()+'.[Log].[BIInfoLog] '
			+' ([LogDate] 	, [RowCount]	, [Severity], [LogLevel]	,[MessageID], [SPName] 	, [Message], [ExtractSession] ) '
			+' values (@LogDate,@RowCount,@Severity,@LogLevel,@MessageID,@ProcName,@Message, @ExtractSession )'
			
			--SET ANSI_WARNINGS OFF -- don't warn about truncation
			--set ANSI_NULLS OFF
			EXEC sp_executesql @qry
			,                  @DMLVars
			, -- declare
			-- input vars
			                   @LogDate       = @now
			,                  @RowCount      = @RowCount
			,                  @Severity      = @Severity
			,                  @LogLevel      = @LogLevel
			,                  @MessageID     = @MessageID
			,                  @ProcName      = @ProcName
			,                  @Message       = @Message
			,                  @ExtractSession= @ExtractSession

however when i then use it, the first attempt to the logging proc returns this error:
2017-05-24 15:48:50.607|        |1:0|Staging.BIGetFormatterColumnList|Starting...
Logging to db!
Transaction context in use by another session.
Error: 50000, Severity: -1, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
Msg 50000, Level 5, State 1

the second call returns this error:
2017-05-24 15:48:50.610|        |4:0|Staging.BIGetFormatterColumnList|column discovery query size: 329
Logging to db!
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Error: 50000, Severity: -1, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
Msg 50000, Level 5, State 1


At this time i haven't even created a transaction in the caller as i am in the testing phase and wanted to make sure everything worked.

I'm not sure what i'm doing wrong.

Back to microsoft.public.sqlserver.programming | Previous | Next — Next in thread | Find similar

Thread

logging stored procedure fails when inserting via linked server lachlann@gmail.com - 2017-05-24 09:00 -0700
  Re: logging stored procedure fails when inserting via linked server lachlann@gmail.com - 2017-05-24 11:24 -0700
    Re: logging stored procedure fails when inserting via linked server Erland Sommarskog <esquel@sommarskog.se> - 2017-05-24 20:46 +0200
      Re: logging stored procedure fails when inserting via linked server lachlann@gmail.com - 2017-05-24 12:05 -0700
        Re: logging stored procedure fails when inserting via linked server Erland Sommarskog <esquel@sommarskog.se> - 2017-05-24 23:06 +0200
      Re: logging stored procedure fails when inserting via linked server lachlann@gmail.com - 2017-05-24 12:05 -0700
  Re: logging stored procedure fails when inserting via linked server Erland Sommarskog <esquel@sommarskog.se> - 2017-05-24 20:37 +0200

csiph-web