InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 16:24:38 GMT20http://www.sqlservercentral.com/Forums/Topic528876-163-1.aspxHi All, My Nested code is similar to the below code templateCREATE PROCEDURE usp_Proc1ASBEGIN TRY BEGIN TRAN TRAN1 EXEC usp_Proc2 COMMIT TRAN TRAN1END TRYBEGIN CATCH ROLLBACK TRAN TRAN1END CATCH ---------------------------------------------------CREATE PROCEDURE usp_Proc2ASBEGIN TRY BEGIN TRAN TRAN2 EXEC usp_Proc3 COMMIT TRAN TRAN2END TRY BEGIN CATCH ROLLBACK TRAN TRAN2END CATCH---------------------------------------------------CREATE PROCEDURE usp_Proc3ASBEGIN TRY BEGIN TRAN TRAN3 SOURCE CODE ---- COMMIT TRAN TRAN3END TRY BEGIN CATCH ROLLBACK TRAN TRAN3END CATCH---------------------------------------------------In summary, usp_Proc1 calls usp_Proc2 which in turn calls usp_Proc3.I need to log all the error that might occur in above flow and also the source(proc_name) of error with description.Regards,Thu, 02 Jul 2009 22:00:51 GMTnaveenreddy.84http://www.sqlservercentral.com/Forums/Topic528876-163-1.aspxPity there are no autonomous transactions in SQL Server. Has anyone used a CLR to accomplish autonomous transactions?Wed, 09 Jul 2008 09:03:17 GMTballanhttp://www.sqlservercentral.com/Forums/Topic528876-163-1.aspxThanks for the article Leo. It seems well suited to function in the environment you propose. I want to know why I never see anybody use the SAVE TRAN statement. In my research and testing, this is the only way to isolate a transaction within a stored procedure, so that it can be independently rolled back without regards to the transaction state before the procedure was called. ?? Has anybody else used SAVE TRAN?Tue, 08 Jul 2008 16:39:35 GMTjoshcsmith13http://www.sqlservercentral.com/Forums/Topic528876-163-1.aspxNice article. You have really encouraged lot of people to write. We may see lot of articles from new bies.....:)Mon, 07 Jul 2008 22:15:28 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic528876-163-1.aspxThanks Leo for the article, the information was very interesting. Your English is pretty good, though there were plenty of grammar errors. I would have thought your editor would have caught them. Keep practicing. I look forward to your next article.Mon, 07 Jul 2008 15:33:25 GMTDan Guzman - Not the MVPhttp://www.sqlservercentral.com/Forums/Topic528876-163-1.aspx[quote][b]pvoutov (7/7/2008)[/b][hr]I noticed that the author uses this statement to grab the newly inserted identity:set @INSERTED_IDENTITY_ID = IDENT_CURRENT('HIST_ERROR_LOG'); I think he meant to specify the table 'ERROR_LOG', not 'HIST_ERROR_LOG'. (Correct)Also, IDENT_CURRENT will return the identity value of the table provided across all sessions, meaning you could get a value that some other thread generated. I usually rely on scope_identity(), which works all the time, except if you have a trigger on the table in which you're inserting into and that trigger happens to be inserting into yet another table with an identity (not a common scenario).Another confusing point of the article is the author's variable naming conventions, prefix of: '@$'. You only need to use @ to indicate something's a variable. I couldn't find any reference as to what the $ would stand for. My guess it's a remnant of an older style when variables were prefixed with $ (e.g. environment variables in a shell).[/quote]You are right about Error_Log table name.$ is to distinguish templated variables from lately declared by developer's variablesMon, 07 Jul 2008 14:46:19 GMTLeo Peysakhovichhttp://www.sqlservercentral.com/Forums/Topic528876-163-1.aspxI noticed that the author uses this statement to grab the newly inserted identity:set @INSERTED_IDENTITY_ID = IDENT_CURRENT('HIST_ERROR_LOG'); I think he meant to specify the table 'ERROR_LOG', not 'HIST_ERROR_LOG'.Also, IDENT_CURRENT will return the identity value of the table provided across all sessions, meaning you could get a value that some other thread generated. I usually rely on scope_identity(), which works all the time, except if you have a trigger on the table in which you're inserting into and that trigger happens to be inserting into yet another table with an identity (not a common scenario).Another confusing point of the article is the author's variable naming conventions, prefix of: '@$'. You only need to use @ to indicate something's a variable. I couldn't find any reference as to what the $ would stand for. My guess it's a remnant of an older style when variables were prefixed with $ (e.g. environment variables in a shell).Mon, 07 Jul 2008 14:26:27 GMTvoutmasterhttp://www.sqlservercentral.com/Forums/Topic528876-163-1.aspxAll the files are at the bottom of the article. I didn't notice the links internally, which needed to be removed.Mon, 07 Jul 2008 10:14:48 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic528876-163-1.aspxi did sent email to steve jonesMon, 07 Jul 2008 10:07:04 GMTLeo Peysakhovichhttp://www.sqlservercentral.com/Forums/Topic528876-163-1.aspxHow can I see the contents of the TXT files? When I click on the link to the TXT files, I get a SQL Server Central page with the contents saying that it could not find the file. This is the case for both TXT files. I am curious to see what code you added, since I am working on this type of problem now.MikeMon, 07 Jul 2008 08:10:38 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic528876-163-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/T-SQL/63353/">Error Handling in Nested Procedures and Logging Custom Errors</A>[/B]Sat, 05 Jul 2008 11:15:57 GMTLeo Peysakhovich