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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

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



mikeg13
mikeg13
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1100 Visits: 370
i did sent email to steve jones



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

Group: Administrators
Points: 178622 Visits: 19497
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
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1100 Visits: 370
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
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1434 Visits: 752
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 (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10681 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 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
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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