SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error Handling in Nested Procedures and Logging Custom Errors


Error Handling in Nested Procedures and Logging Custom Errors

Author
Message
Leo Peysakhovich
Leo Peysakhovich
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 376
Comments posted to this topic are about the item Error Handling in Nested Procedures and Logging Custom Errors



mikeg13
mikeg13
SSC Eights!
SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)

Group: General Forum Members
Points: 940 Visits: 322
How 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.

Mike
Leo Peysakhovich
Leo Peysakhovich
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 376
i did sent email to steve jones



Steve Jones
Steve Jones
SSC Guru
SSC Guru (300K reputation)SSC Guru (300K reputation)SSC Guru (300K reputation)SSC Guru (300K reputation)SSC Guru (300K reputation)SSC Guru (300K reputation)SSC Guru (300K reputation)SSC Guru (300K reputation)

Group: Administrators
Points: 300024 Visits: 20005
All the files are at the bottom of the article. I didn't notice the links internally, which needed to be removed.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
voutmaster
voutmaster
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 34
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'.

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).
Leo Peysakhovich
Leo Peysakhovich
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 376
pvoutov (7/7/2008)
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).


You are right about Error_Log table name.
$ is to distinguish templated variables from lately declared by developer's variables



Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 760
Thanks 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.
Anipaul
Anipaul
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13853 Visits: 1407
Nice article. You have really encouraged lot of people to write. We may see lot of articles from new bies.....Smile



joshcsmith13
joshcsmith13
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1775 Visits: 254
Thanks 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?



ballan
ballan
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 1
Pity there are no autonomous transactions in SQL Server. Has anyone used a CLR to accomplish autonomous transactions?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search