Prev: Find constraint
Next: UPDATE through IN(..) behaviour
From: moondaddy on 12 Jan 2010 16:10
When I get an exception with in a stored procedure, I want to write all
relative data to some exception log tables in the same database. The
problem is often this is in a stored procedure that is part of a sql
transaction (not an ado.net transaction) and then the transaction rolls
back, so does the insert into the exception log.

I do this in the stored procedure where the exception occurred by passing
parameters to another stored procedure which logs the exception data
something like this:

INSERT INTO tbSysSqlErrorLog(sysSqlEr_Id, sysSqlEr_Number,
sysSqlEr_Severity, sysSqlEr_State, sysSqlEr_Line, sysSqlEr_Procedure,
sysSqlEr_Message, sysSqlEr_OtherMessage, sysSqlEr_TimeStamp)
VALUES (@ErrorLogId, error_number(), error_severity(), error_state(),
error_line(), error_procedure(), error_message(), @ErrMsg,
CURRENT_TIMESTAMP)and then I can also log parameters and variavles used in the stored
procedures like this:

EXEC spSysSqlErrorLogParams_insert @ErrorLogId, '@OrD_Id',
'uniqueidentifier', @OrD_Id, 1;

Is there a way to do an insert with in a transaction but declare it outside
of the transaction so it doesn't get rolled back?


--
moondaddy(a)newsgroup.nospam

From: Plamen Ratchev on 12 Jan 2010 17:08
It depends on how your transaction is structured in the error handling. In the following example the error in the log
table will not be rolled back:

SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRAN

-- your code here
SELECT 1/0;

COMMIT TRAN

END TRY
BEGIN CATCH

IF (XACT_STATE()) = -1
BEGIN
ROLLBACK
END
ELSE IF (XACT_STATE()) = 1
BEGIN
COMMIT -- or ROLLBACK
END

INSERT INTO Log VALUES(ERROR_MESSAGE());

END CATCH

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 12 Jan 2010 17:37
moondaddy (moondaddy(a)newsgroup.nospam) writes:
> When I get an exception with in a stored procedure, I want to write all
> relative data to some exception log tables in the same database. The
> problem is often this is in a stored procedure that is part of a sql
> transaction (not an ado.net transaction) and then the transaction rolls
> back, so does the insert into the exception log.
>
> I do this in the stored procedure where the exception occurred by passing
> parameters to another stored procedure which logs the exception data
> something like this:
>
> INSERT INTO tbSysSqlErrorLog(sysSqlEr_Id, sysSqlEr_Number,
> sysSqlEr_Severity, sysSqlEr_State, sysSqlEr_Line, sysSqlEr_Procedure,
> sysSqlEr_Message, sysSqlEr_OtherMessage, sysSqlEr_TimeStamp)
> VALUES (@ErrorLogId, error_number(), error_severity(), error_state(),
> error_line(), error_procedure(), error_message(), @ErrMsg,
> CURRENT_TIMESTAMP)and then I can also log parameters and variavles used
> in the stored procedures like this:
>
> EXEC spSysSqlErrorLogParams_insert @ErrorLogId, '@OrD_Id',
> 'uniqueidentifier', @OrD_Id, 1;
>
> Is there a way to do an insert with in a transaction but declare it
> outside of the transaction so it doesn't get rolled back?

The only way to do it today, is to write a CLR procedure that
makes a loopback connection.

Here is a Connect item to vote for:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=296870



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Find constraint
Next: UPDATE through IN(..) behaviour