Re: Transaction Abort in a trigger SQL 2008, writing to event log

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

John Couch (JohnCouch@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I have done some stack trace type of error catching in Powerbuilder, VB
and in C# (not real good at C# though). I liked it simply because I
could see the entire call stack to see where the call originated and the
steps it went through to get to the error...etc.

Well, it would be nice to access the call stack from anywhere in SQL Server,
but alas there is no such feature. However, votes to this Connect item
are always welcome:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124537

BUT, you said something about using a CLR procedure to write out to the
table or event log? I will have to look that up. I would still have to
rollback that transaction wouldn't I? Wouldn't that affect the CLR
procedure?

In the system I work with, whenever we want to raise an error or just log a
debug message we called a certain stored procedure what expands parameters,
writes the message to a log table and raises the error. But if the logging
procedure finds that there is an open transaction, it does not write
directly to the log table, but instead calls an extended stored procedure
that performs a loopback connection and writes to this table outside the
transaction. We use an XP because we devised this scheme long before there
were CLR procedures.

It should be added that using a loopback is a kludge for another missing
feature in SQL Server which exists in some other products, for instance
Oracle, that is autonomous transactions:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=296870

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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

.

Relevant Pages

  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: Windows Completely Locks Up!! FREEZE
    ... failing to start/run the Distributed Transaction Coordinator. ... Error Specifics: ... The Business Contact Manager SQL Server Startup Service service terminated ... The service did not respond to the start or control request in a timely ...
    (microsoft.public.windowsxp.general)
  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Transaction Abort in a trigger SQL 2008, writing to event log
    ... transaction could still be doomed at some point, ... With the current functionality in SQL Server ... you should roll back the transaction in your error handler. ... global cursor in a stored procedure. ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction Abort in a trigger SQL 2008, writing to event log
    ... I have done some stack trace type of error catching in Powerbuilder, ... transaction could still be doomed at some point, ... With the current functionality in SQL Server ... you should roll back the transaction in your error handler. ...
    (microsoft.public.sqlserver.programming)