July 5, 2008 at 11:15 am
Comments posted to this topic are about the item Error Handling in Nested Procedures and Logging Custom Errors
July 7, 2008 at 8:10 am
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
July 7, 2008 at 10:07 am
i did sent email to steve jones
July 7, 2008 at 10:14 am
All the files are at the bottom of the article. I didn't notice the links internally, which needed to be removed.
July 7, 2008 at 2:26 pm
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).
July 7, 2008 at 2:46 pm
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
July 7, 2008 at 3:33 pm
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.
July 7, 2008 at 10:15 pm
Nice article. You have really encouraged lot of people to write. We may see lot of articles from new bies.....:)
July 8, 2008 at 4:39 pm
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?
July 9, 2008 at 9:03 am
Pity there are no autonomous transactions in SQL Server. Has anyone used a CLR to accomplish autonomous transactions?
July 2, 2009 at 10:00 pm
Hi All,
My Nested code is similar to the below code template
CREATE PROCEDURE usp_Proc1
AS
BEGIN TRY
BEGIN TRAN TRAN1
EXEC usp_Proc2
COMMIT TRAN TRAN1
END TRY
BEGIN CATCH
ROLLBACK TRAN TRAN1
END CATCH
---------------------------------------------------
CREATE PROCEDURE usp_Proc2
AS
BEGIN TRY
BEGIN TRAN TRAN2
EXEC usp_Proc3
COMMIT TRAN TRAN2
END TRY
BEGIN CATCH
ROLLBACK TRAN TRAN2
END CATCH
---------------------------------------------------
CREATE PROCEDURE usp_Proc3
AS
BEGIN TRY
BEGIN TRAN TRAN3
SOURCE CODE ----
COMMIT TRAN TRAN3
END TRY
BEGIN CATCH
ROLLBACK TRAN TRAN3
END 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,
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply