Prev: Incorrect syntax near...
Next: I need to create a data warehouse database, have only vague ideas
From: Erland Sommarskog on 4 Aug 2010 15:56
Dan Holmes (dan.holmes(a)routematch.com) writes:
> If i have a doomed (or will be doomed) transaction and i want to write
> the error and context (like proc name and parameters) to a table, how
> can i get that write to survive the ensuing ROLLBACK?

As Tom says, table variables is a way, but as you observe it's only
useful within a single procedure. For something like logging an error
deep down in a transaction, a loopback connection is the way to go.
We have been doing this for many years.

--
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

From: Dan Holmes on 5 Aug 2010 08:08
On 8/4/2010 3:56 PM, Erland Sommarskog wrote:
> Dan Holmes (dan.holmes(a)routematch.com) writes:
>> If i have a doomed (or will be doomed) transaction and i want to write
>> the error and context (like proc name and parameters) to a table, how
>> can i get that write to survive the ensuing ROLLBACK?
>
> As Tom says, table variables is a way, but as you observe it's only
> useful within a single procedure. For something like logging an error
> deep down in a transaction, a loopback connection is the way to go.
> We have been doing this for many years.
>
how do you keep it from enlisting in the current transaction?
From: Erland Sommarskog on 5 Aug 2010 15:20
Dan Holmes (dan.holmes(a)routematch.com) writes:
> On 8/4/2010 3:56 PM, Erland Sommarskog wrote:
>> Dan Holmes (dan.holmes(a)routematch.com) writes:
>>> If i have a doomed (or will be doomed) transaction and i want to write
>>> the error and context (like proc name and parameters) to a table, how
>>> can i get that write to survive the ensuing ROLLBACK?
>>
>> As Tom says, table variables is a way, but as you observe it's only
>> useful within a single procedure. For something like logging an error
>> deep down in a transaction, a loopback connection is the way to go.
>> We have been doing this for many years.
>>
> how do you keep it from enlisting in the current transaction?

Not sure that I understand your question? A loopback connection
will not enlist in the current transacton as far as I know. Ours
certainly doesn't. (But they are in an XP, not the CLR.)

--
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: Incorrect syntax near...
Next: I need to create a data warehouse database, have only vague ideas