October 11, 2011 at 8:24 am
Hi all,
Is it possible to retrieve the actual SQL transaction statement which has been actioned on a table.
For example, if the SQL statement:
UPDATE thisTable SET thisField='thisValue' WHERE thisID='thisValue'
was fired from either .NET, ASP.net OR any other stored procedure within SQL - I would want to be able to store that complete instruction in another table named History in a field SQLInstruction nvarchar(max) to keep track of what has happened.
The issue I have is that I have a system where people entered a code and depending on whether the code existed - populated the relevant fields. Due to VERY VERY bad coding the UPDATE statements that were fired never actually updated the database and all attempts were lost (after much investigating I might add). With the addition of the table above we would have been able to recover the error.
I thought that this information would be available within an UPDATE trigger so I could just write to the table every time an update was fired BUT I cannot find any value of the statement???
Any thoughts?
Thanks in advance.
October 11, 2011 at 8:39 am
What edition of SQL Server 2008 are you running?
Thanks,
Jared
Jared
CE - Microsoft
October 11, 2011 at 8:40 am
Have a look at APEX SQL and Lumigen. They do offer change tracking but not sure if they capture the actual command. The other option is server side trace but this will use a considerable amount of resources.
MCITP SQL 2005, MCSA SQL 2012
October 11, 2011 at 8:45 am
SQL Server has some tools for you and depending on what your requirements are and what edition you have, these can be very useful (CDC is only available on Enterprise):
http://msdn.microsoft.com/en-us/library/cc280519.aspx
Thanks,
Jared
Jared
CE - Microsoft
October 11, 2011 at 9:08 am
this might be tough...if the update fails, i'm not sure you can even capture something in the trigger....if the update executes but touches zero rows, that's a different issue.
for example, if the update fails due to a column not existing, the trigger would not be touched...the error returned before it goes to the table.
anyway, inside a trigger, you can get up to 4000 characters of the command...so if the update command was huge, it might get cut off, but it's appropriate for 99% of the time, i would reckon:
you can use dynamic SQL and DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS' to get the executing command.
here's a code example:
CREATE TABLE WHATEVER(
WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30)
)
INSERT INTO WHATEVER(DESCRIP)
SELECT 'APPLES' UNION
SELECT 'ORANGES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES' UNION
SELECT 'CHERRIES' UNION
SELECT 'KIWI'
--used to capture the row id plus a bunch of audit information
CREATE TABLE [dbo].[WHATEVER_AUDIT] (
[WHATEVERID] INT NOT NULL,
[INSERTUPDATE] NVARCHAR(30) NULL,
[LASTCOMMAND] NVARCHAR(max) NULL,
[USER_NAME] NVARCHAR(256) NULL,
[SUSER_NAME] NVARCHAR(256) NULL,
[CURRENT_USER] NVARCHAR(256) NULL,
[SYSTEM_USER] NVARCHAR(256) NULL,
[SESSION_USER] NVARCHAR(256) NULL,
NVARCHAR(256) NULL,
[APPLICATION_NAME] NVARCHAR(256) NULL,
[HOST_NAME] NVARCHAR(256) NULL,
[OCCURANCE_DATE] DATETIME DEFAULT GETDATE() NOT NULL)
GO
CREATE TRIGGER TR_WHATEVER
ON WHATEVER
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @INSERTUPDATE NVARCHAR(30),
@LASTCOMMAND NVARCHAR(max)
--################################################################################################
--note these two methods do not get the last command when inside a trigger;
--included for complete solution
--get the last command by the current spid:
--DECLARE @handle varbinary(64)
--SELECT @handle = MAX(sql_handle) FROM master..sysprocesses WHERE spid = @@SPID
--SELECT @LASTCOMMAND = [Text] FROM ::fn_get_sql(@Handle)
--get the last command by the current spid:
--SELECT @LASTCOMMAND = DEST.TEXT
--FROM sys.[dm_exec_connections] SDEC
--CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST
--WHERE SDEC.[most_recent_session_id] = @@SPID
--################################################################################################
--because dbcc inputbuffer is limited to 4000 chars, you may need to combine this with a DML trace
--################################################################################################
DECLARE @SQLBuffer nvarchar(4000)
DECLARE @buffer TABLE (
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000)
)
INSERT @buffer
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'
SELECT @LASTCOMMAND = EventInfo
FROM @buffer
--assume it is an insert
SET @INSERTUPDATE='INSERT'
--if there's data ind eleted, it's an update
IF EXISTS(SELECT * FROM DELETED)
SET @INSERTUPDATE='UPDATE'
--insert data that meets the criteria: the column 'description' is null
INSERT INTO [WHATEVER_AUDIT]
SELECT
INSERTED.WHATEVERID,
@INSERTUPDATE,
@LASTCOMMAND,
user_name() AS [user_name],
suser_name() AS [suser_name],
current_user AS [current_user],
system_user AS [system_user],
session_user AS [session_user],
user AS ,
APP_NAME() AS [application_name],
HOST_NAME() AS [host_name],
getdate() AS [occurance_date]
FROM INSERTED
WHERE DESCRIP IS NULL
END --TRIGGER
GO
--does not trigger audit:
INSERT INTO WHATEVER(DESCRIP)
SELECT 'CANTALOUPE' UNION
SELECT 'TANGARINES' UNION
SELECT 'PLUMS' UNION
SELECT 'PEACHES' UNION
SELECT 'BLUEBERRIES'
--triggers one row out of multi row insert
INSERT INTO WHATEVER(DESCRIP)
SELECT NULL UNION
SELECT 'TANGARINES'
--triggers one row out of multi row insert
UPDATE WHATEVER SET DESCRIP = NULL WHERE WHATEVERID IN (4,5)
SELECT * FROM WHATEVER
SELECT * FROM [WHATEVER_AUDIT]
Lowell
October 11, 2011 at 9:21 am
Just to take a totally different approach to this. Could you not have just run a trace? This would have shown you what the actual statements were. To solve the problem you described I don't see why you would need to track all update dml statements. You just needed to run a trace so you could see that the update statement was not correct.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 9:31 am
Sean Lange (10/11/2011)
Just to take a totally different approach to this. Could you not have just run a trace? This would have shown you what the actual statements were. To solve the problem you described I don't see why you would need to track all update dml statements. You just needed to run a trace so you could see that the update statement was not correct.
A trace will not show you what the actual statements "were" unless the trace is running. I think that running a trace indefinitely is clearly a bad idea. Again, if you have Enterprise use CDC. If not, use a trigger. You will not be able to really capture the exact statement into 1 field unless you are doing a bunch of concatenation or unless you are using dynamic SQL and inserting it into a table before or after executing it, which seems silly to me.
Thanks,
Jared
Jared
CE - Microsoft
October 11, 2011 at 9:36 am
jared-709193 (10/11/2011)
Sean Lange (10/11/2011)
Just to take a totally different approach to this. Could you not have just run a trace? This would have shown you what the actual statements were. To solve the problem you described I don't see why you would need to track all update dml statements. You just needed to run a trace so you could see that the update statement was not correct.A trace will not show you what the actual statements "were" unless the trace is running. I think that running a trace indefinitely is clearly a bad idea. Again, if you have Enterprise use CDC. If not, use a trigger. You will not be able to really capture the exact statement into 1 field unless you are doing a bunch of concatenation or unless you are using dynamic SQL and inserting it into a table before or after executing it, which seems silly to me.
Thanks,
Jared
The problem described here was that the update was NOT changing anything. CDC or an update trigger would have a hard time because the data was not being changed. I certainly do not condone running an indefinite trace but a trace would have helped identify the problem, and it would have had the statements in it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 9:48 am
Sean Lange (10/11/2011)
jared-709193 (10/11/2011)
Sean Lange (10/11/2011)
Just to take a totally different approach to this. Could you not have just run a trace? This would have shown you what the actual statements were. To solve the problem you described I don't see why you would need to track all update dml statements. You just needed to run a trace so you could see that the update statement was not correct.A trace will not show you what the actual statements "were" unless the trace is running. I think that running a trace indefinitely is clearly a bad idea. Again, if you have Enterprise use CDC. If not, use a trigger. You will not be able to really capture the exact statement into 1 field unless you are doing a bunch of concatenation or unless you are using dynamic SQL and inserting it into a table before or after executing it, which seems silly to me.
Thanks,
Jared
The problem described here was that the update was NOT changing anything. CDC or an update trigger would have a hard time because the data was not being changed. I certainly do not condone running an indefinite trace but a trace would have helped identify the problem, and it would have had the statements in it.
Ahh... good point. Although it seems to me this is something that is happening consistently and needs constant monitoring until the code can be updated. I think that the only long term solution barring stored proc and application changes is a well designed trigger.
EDIT: Not sure that you can use a trigger at all for this since nothing was updated or inserted. The trigger will still fire, but I don't think it can return any values. Anyone know or able to test this?
Thanks,
Jared
Jared
CE - Microsoft
October 11, 2011 at 9:53 am
jared-709193 (10/11/2011)
Sean Lange (10/11/2011)
jared-709193 (10/11/2011)
Sean Lange (10/11/2011)
Just to take a totally different approach to this. Could you not have just run a trace? This would have shown you what the actual statements were. To solve the problem you described I don't see why you would need to track all update dml statements. You just needed to run a trace so you could see that the update statement was not correct.A trace will not show you what the actual statements "were" unless the trace is running. I think that running a trace indefinitely is clearly a bad idea. Again, if you have Enterprise use CDC. If not, use a trigger. You will not be able to really capture the exact statement into 1 field unless you are doing a bunch of concatenation or unless you are using dynamic SQL and inserting it into a table before or after executing it, which seems silly to me.
Thanks,
Jared
The problem described here was that the update was NOT changing anything. CDC or an update trigger would have a hard time because the data was not being changed. I certainly do not condone running an indefinite trace but a trace would have helped identify the problem, and it would have had the statements in it.
Ahh... good point. Although it seems to me this is something that is happening consistently and needs constant monitoring until the code can be updated. I think that the only long term solution barring stored proc and application changes is a well designed trigger.
Thanks,
Jared
I am not sure but I got the impression from the OP that nothing was getting updated because the code was bad. Maybe a where 1 <> 1 or some such awfulness. I have never really tried but not sure the update trigger would fire if nothing gets updated. I suppose with an instead of trigger it would though. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply