Show command executed when error occurs in SQL

Question

Show command executed when error occurs in SQL

Navigation

#1 by (4 votes)
#2 by (0 votes)

6

Considering the following executed command

BEGIN TRY  
    -- Generate a divide-by-zero error.  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_MESSAGE() AS ErrorMessage;  

END CATCH;  
GO

Your response will be:

ErrorNumber
8134

ErrorSeverity
16

ErrorState
1

ErrorProcedure
NULL

ErrorMessage
Divide by zero error encountered.

Is there any way to get which execution block was executed to generate this exception?

I wanted the answer to be:

Command

'SELECT 1/0;'

sql sql-server sql-server-2012

asked by anonymous 07.07.2016 / 19:22

2 answers

2 gifs alternating the same space (frames loading correctly) How to authenticate the application and authorize it to consume a restful API

score 4 · Answer 1

Jhonathan is not usually a standard error exit, but this snippet may help you:

BEGIN TRY  
    -- Generate a divide-by-zero error.  

    SELECT 1 / 0;
END TRY
BEGIN CATCH
    SELECT [qt].text
      FROM [sys].[dm_exec_connections] AS [A]
           CROSS APPLY [sys].[dm_exec_sql_text]([A].[most_recent_sql_handle]) AS [qt]
     WHERE [session_id] = @@SPID;
END CATCH;  
GO

But in my opinion, you could collect these errors in an audit otherwise.

SQLServer: Audit of query errors

score 0 · Answer 2

A solution and you create a variable to store the description of the block that is running and concatenate the message.

declare @BlocoExe varchar(max)

BEGIN TRY  
    -- Generate a divide-by-zero error. 
    set @BlocoExe  = 'Bloco exe: SELECT 1/0 - Erro:'; -- descrição do bloco
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        , @BlocoExe + ERROR_MESSAGE()  AS ErrorMessage ;  

END CATCH;  
GO