May 19, 2017 at 10:54 am
So.. I'm working on writing a generic CATCH block what will insert into a log table the following information:
The table that was the target of a failed insert/update statement.
The command that was being executed (including the values being passed for update or insert)
The procedure it happened in
The error message that resulted.
I can get ERROR_MESSAGE()
I can get ERROR_PROCEDURE()
I can get the error session text (but not the actual command) in a bulky sort of way from the ring buffer using extended events
I can't figure out how to get the target of the failed statement.
I'd like to find the actual statement that caused the error.
I don't want to see:
[
BEGIN TRY
INSERT INTO [dbo].[testTable]
([testInt]
,[testVarchar])
VALUES
('A'
,'A')
END TRY
BEGIN CATCH
THROW;
END CATCH
I'd rather see (if possible)INSERT INTO [dbo].[testTable] ([testInt],[testVarchar]) VALUES ('A','A')
Is there a way to get the target table from extended events? Perhaps I missed an option there? Any other thoughts?
May 25, 2017 at 8:22 am
*bump*
May 25, 2017 at 9:31 am
I'm confused. Is this something you want from extended events, or are you trying to capture this in the T-SQL with the try..catch?
It's not quite clear what you're asking.
May 25, 2017 at 10:35 am
Basically, I'm trying to get the target table(s) and the sql command that was executed. Not the entire sql batch like you get in the extended events but the actual statement with substituted parameters. I'm happy with either extended events or a try..catch block or a combination of both. The basic idea though is to make it generic enough that I don't need to capture the statement or target table in the procedures prior to executing the catch block.
Does that make more sense?
I want to wrap ANY procedure in my try...catch structure and capture those errors without having to modify the procedures manually before-hand.
I'm using the below event to supplement the information in my catch block... just need those two other things, if possible to get something I can really use:IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'GenericError' )
DROP EVENT SESSION [ErrorLogger] ON SERVER;
CREATE EVENT SESSION [ErrorLogger] ON SERVER
ADD EVENT sqlserver.error_reported (
ACTION ( sqlserver.sql_text )
WHERE ( ([severity] > ( 10 )) ) )
ADD TARGET package0.ring_buffer
WITH ( EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS );
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply