June 29, 2008 at 9:32 am
I have been working with some of the DMVs to better my understanding of what I can capture about errors in Dynamic SQL. Ideally, I wanted to find a way to have the caller that needed to run the dynamic SQL be able to catch the error AND return the full text of the SQL in a standard way in the CATCH block.
I deal with a lot of dynamic SQL in the application I work on, it varies from simple statements to very complex with dynamic SQL built that builds more dynamic SQL that something eventually executes to get the needed result. Being able to standardize what a CATCH related to dynamic SQL execution should look like and what it should report would go a long way to making my life easier. Since I am looking at a code "retro-fit" to do this standardization (code was originally done in 2000, so only @@ERROR checks are in place right now), it would be best to be sure of the optimal approach before starting this work.
I have found I can capture the SQL that errored and return it to the caller by placing the query to return that SQL in a CATCH block in the dynamic SQL itself. That isn't really what I want, since it would require altering all the dynamic SQL in the application. Also, this doesn't help at all when the dynamic SQL has an error in it that prevents compile. It's looking to me like it may not be possible to query for that erroring SQL within the caller (in either type of error). My guess is that since the scope of the execution of the dynamic SQL is closed by the time control is returned to the caller the information I want is actually gone at that point.... but I'm hoping I'm wrong and someone can show me the trick to getting what I really want here.
IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'sub_TestErrorCollection' AND type = 'P')
BEGIN
DROP PROCEDURE dbo.sub_TestErrorCollection
END
GO
CREATE PROCEDURE dbo.
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQLString nvarchar(3000), @Output nvarchar(3000), @Rows int
SELECT @SQLString = IsNull(@SQLString + ', T.', 'SELECT TOP(5) T.') + SC.name
FROM sys.objects SO
INNER JOIN sys.columns SC
ON SO.object_id = SC.object_id
WHERE SO.name = 'st_Table'
AND SO.type = 'U'
ORDER BY SC.is_identity DESC, SC.column_id ASC
SET @SQLString = IsNull(@SQLString, '')
---- Option 1 (try capture the sql on error only)
---- doesn't work when error is at statement compile
--SET @SQLString = N'
--BEGIN TRY
--' + @SQLString + ' WHERE
--FROM dbo.st_Table WITH(NOLOCK)
--END TRY
--BEGIN CATCH
--SELECT text
--FROM sys.dm_exec_requests ER
--CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle)
--WHERE ER.session_id = @@SPID
--END CATCH'
-- Option 2 (try capture the sql on error only)
-- only works when the error is encountered after compile
SET @SQLString = N'
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
SELECT @SQLString = text
FROM sys.dm_exec_requests ER
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle)
WHERE ER.session_id = @@SPID
END CATCH'
BEGIN TRY
EXEC sp_ExecuteSQL @SQLString, N'@SQLString nvarchar(max) OUTPUT', @Output OUTPUT
SET @Rows = @@ROWCOUNT
SELECT OBJECT_NAME(@@PROCID) ProcName, @Rows RowsAffected, @@SPID session_id, @Output ExecutedText
END TRY
BEGIN CATCH
SELECT OBJECT_NAME(@@PROCID) ProcName, @Rows RowsAffected, @@SPID session_id, @Output ExecutedText
SELECT ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() ErrorMessage, ERROR_PROCEDURE() ErrorProcedure
-- Option 3 (find a way to capture the SQL that errored in the dynamic call, from the caller)
--SELECT * FROM sys.dm_exec_requests
--WHERE session_id = @@SPID
--SELECT *
--FROM sys.dm_exec_connections
--CROSS APPLY sys.dm_exec_sql_text (sys.dm_exec_connections.most_recent_sql_handle)
--WHERE session_id = @@SPID
--SELECT start_time, text, *
--FROM sys.dm_exec_requests
--CROSS APPLY sys.dm_exec_sql_text (sys.dm_exec_requests.sql_handle)
--WHERE session_id = @@SPID
END CATCH
END
June 29, 2008 at 10:21 am
I do this a lot also, and it has been my experience that you have to put TRY..CATCH in both the calling proc and the called proc.
There was a discussion about this somewhere on here in the last couple of weeks...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2008 at 10:25 am
Here it is:http://qa.sqlservercentral.com/Forums/Topic512477-338-1.aspx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2008 at 4:00 pm
I have read that. It is a bit different then what I am after. My focus is on being able to query to retrieve the text of the SQL that errorred.
June 29, 2008 at 4:56 pm
Kimberly.Hahn (6/29/2008)
I have read that. It is a bit different then what I am after. My focus is on being able to query to retrieve the text of the SQL that errorred.
Well, if it is Dynamic SQL, then you had to construct the SQL string beforehand anyway. If you nest the TRY..CATCH's and have the inner one raise back to the outer one, the outer one shoud then have access to the SQL string executed.
Of course, when you are nesting the Dynamic EXEC's also, you may not have built it as a string before you call down to the next lower level, because it may have been dynamically constructed above the current level to be a string literal at the current level. You can avoid this by just having the upper levels always out the string literal into a NVarchar variable first: @SQL and then EXEC that. That way, if you catch an exception at the current level from a lower level, you always have the command sting in @SQL that presumably caused the error.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2008 at 5:10 pm
One of the easier ways that you can do this is to write your own "wrapper" Proc for EXEC(string) (or for sp_ExecuteSQL), and then always have your Dynamic SQL call that instead. Then this procedure can encapsulate all of the necessary handling:
-- The command is already in a string variable, because it was passed in as a parameter.
-- The @SQL parameter passed in can have the Inner TRY..CATCH automatically wrpped around it:
Select @SQL = '
BEGIN TRY
' + @SQL + '
END TRY
BEGIN CATCH
...(your standard Catch code here)...
END CATCH'
-- Finally, the Proc itself can have the outer TRY..CATCH as part of its own code:
...
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
...(your outer Catch code here, including: )...
Print @SQL
END CATCH
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 30, 2008 at 8:35 am
Thank you for the additional information, but I already have a very solid understanding of TRY-CATCH structures and really am not asking about employing them.
What I am asking about is the ability to quey for SQL that has been executed or attemtped to be executed as dynamic SQL. Is there anything in the DMVs or anything else that could allow me to retrieve that information from within a CATCH? Is that only possible when the query failed after compile, or is there a way to capture that informtion when it failed at the compile level.
June 30, 2008 at 1:29 pm
I must have been focusing on these two statements:
Ideally, I wanted to find a way to have the caller that needed to run the dynamic SQL be able to catch the error AND return the full text of the SQL in a standard way in the CATCH block.
and, ...
Also, this doesn't help at all when the dynamic SQL has an error in it that prevents compile.
which my replies address. I guess I missed that you were already set on post-querying for it, sorry.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply