March 25, 2010 at 5:53 am
Hi!
I inherited a SS2K database that is the back end to a COTS web application. We are having difficulties with the values in one table changing in what looks to be unexplained ways. I have written a trigger and created a log table to capture the time of the change. In addition, I use APP_NAME(), HOST_NAME(), and USER_NAME to try to identify where the change is coming from.
However, I am getting some situations where it would really be helpful to be able to query the database to identify what T-SQL or Stored Procedure caused the change in my table's data.
Ian Stirk has an excellent article about how to do this in SS2005 (http://www.sqlservercentral.com/articles/DMV/64425/[/url]).
Is there an equivalent way to identify the SQL or SP that caused the trigger to fire in SS2K?
I would want the code in the trigger to do the identification and write that information to my log table.
Thank you ahead of time for any help you can provide!
March 25, 2010 at 4:07 pm
You should be able to use DBCC INPUTBUFFER(@@SPID) and ::fn_get_sql (The second one only works if you have SP3, or more specifically the patch that added this and the sql_handle column to sysprocesses). You'll probably have to use a temp table for the dbcc inputbuffer so that you can log it somewhere. Note that the two do not behave the same way. Inputbuffer is going to give you the starting statement that launched the batch, but fn_get_sql can return the last run statement. For example, if you do an insert to a table with a trigger, inputbuffer would return the insert and fn_get_sql might return the trigger definition (depending on the time of capture).
Also note that inputbuffer is limited to 255 characters.
Example Code for both:
CREATE TABLE #B(eventtype nvarchar(30), parameters int, eventinfo nvarchar(255))
INSERT INTO #B(EventType, Parameters, EventInfo)
EXEC ('dbcc inputbuffer (' + @@spid + ') with no_infomsgs')
DECLARE @handle binary(20)
SELECT @handle = (SELECT MAX(sql_handle) FROM master..sysprocesses WHERE spid = @@SPID)
SELECT * FROM ::fn_get_sql(@handle)
March 25, 2010 at 4:12 pm
Here's another snippet for working with fn_get_sql that might be helpful.
CREATE TABLE #C(dbid smallint, objectid int, number smallint, encrypted bit, [text] text)
DECLARE @handle binary(20),@textptr binary(16),
@dl int
SET @Handle = (SELECT MAX(sql_handle) FROM master..sysprocesses WHERE spid = @@spid)
INSERT INTO #C(DBID, ObjectID, Number, Encrypted, [Text])
EXEC ('SELECT * FROM ::fn_get_sql(' + @Handle + ')')
SELECT @textptr = TEXTPTR([Text]),
@dl = CASE WHEN DATALENGTH([Text]) > 3000 THEN 3000 ELSE DATALENGTH([Text]) END
FROM #C
--...
READTEXT #c.[Text] @TextPtr 0 @dl
March 25, 2010 at 7:32 pm
I am reading this from home and cannot try it out until tomorrow. I am relatively new to this area of SQL Server. Will this code run from inside a trigger on SS2K? (I don't mean to sound skeptical, but I have been looking at lots of forums and have found lots of different solutions, only to find out they don't work on SS2K).
Thank you for your advice ... I will try it out tomorrow! 🙂
March 25, 2010 at 7:44 pm
Seth,
I read your reply more closely. Correct me if I am wrong ... it sounds as though these snippets will capture the T-SQL that is running at the time of the trigger firing. I know that some of the Stored Procedures that run on this App are fairly long and complicated. My concern is that the restriction of length might defeat the purpose of this solution.
QUESTION: would you know how to capture the name of the Stored Procedure that triggered the event?
Thanks again!
March 26, 2010 at 9:48 am
No problem with the skepticism, I'm still running 2000 in my environment here, and these work fine (with the versioning restriction on fn_get_sql that I mentioned above, you need that hotfix or sp3).
The DBCC Inputbuffer will capture the initial command that triggered everything happening. This would be the name of the stored procedure... IF that stored procedure was run directly (ie. not run by a trigger or another stored procedure). This is actually not replaced (to my knowledge) by even the DMV's in the newer versions, as they behave similar to fn_get_sql. fn_get_sql returns the text of the last statement. You may want to capture both within the trigger and log them into separate columns to try to give you a good idea of what's happening.
March 26, 2010 at 2:29 pm
I tried it today at work and it works! Thanks!
March 28, 2010 at 1:12 pm
March 29, 2010 at 3:28 pm
Seth,
I was a little premature. The 2 other snippets worked great. I am having problems with the 3rd one.
Here is what I have (note: I used a global temp table vs. a regular temp table):
IF OBJECT_ID('tempdb..##C','U') IS NOT NULL
DROP TABLE ##C
CREATE TABLE ##C(dbid smallint, objectid int, number smallint, encrypted bit, [text] text)
DECLARE @handle binary(20),@textptr binary(16), @dl int
SET @Handle = (SELECT MAX(sql_handle) FROM master..sysprocesses WHERE spid = @@spid)
INSERT INTO ##C(DBID, ObjectID, Number, Encrypted, [Text])
EXECUTE ('SELECT * FROM ::fn_get_sql(' + @Handle + ')')
SELECT @textptr = TEXTPTR([Text]),
@dl = CASE WHEN DATALENGTH([Text]) > 3000 THEN 3000 ELSE DATALENGTH([Text]) END
FROM ##C
READTEXT ##c.[Text] @TextPtr 0 @dl
I got the following error message:
.Net SqlClient Data Provider: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '?'.
(0 row(s) affected)
.Net SqlClient Data Provider: Msg 7133, Level 16, State 1, Line 28
NULL textptr (text, ntext, or image pointer) passed to READTEXT function.
The best I can tell, the reference to
Incorrect syntax near '?'
is coming from the statement:
EXECUTE ('SELECT * FROM ::fn_get_sql(' + @Handle + ')')
I am not sure why I am getting a NULL txtptr.
Can you give me an idea of what I might be doing wrong?
Thanks again!
March 29, 2010 at 4:40 pm
Heh, ok, I don't know what I was thinking when I linked that. I guess I should have actually tested that first. That code was all wrong, I apologize.
You don't need dynamic sql for this one at all:
DECLARE @fgs nvarchar(4000),
@handle varbinary(64)
SELECT @handle = MAX(sql_handle) FROM master..sysprocesses WHERE spid = @@SPID
select @fgs = CONVERT(nvarchar(4000),SUBSTRING([Text],1,4000)) from ::fn_get_sql(@handle)
SELECT @fgs
I wrote a blog about this this weekend(and that was wrong too, so thanks for the heads up):
March 29, 2010 at 6:05 pm
I tried it ... it works!
Thanks! 😀
March 30, 2010 at 9:35 am
Seth,
The code works well but in my trigger, when I call the code, it returns the trigger code instead of the SQL or stored procedure that caused the trigger to fire. :blush:
Any suggestions?
The intent of my question was to place code in a trigger that would determine what code or stored procedure fired the trigger.
This is part of an audit process where we are trying to determine when and what is changing values in one column of one table. Since many changes are coming in via our intranet, the user is always the same, so I need to determine if there is another process that is changing the values and go from there. Note: this problem is the result of a COTS product that is not well understood. We bought the source code but it is of the variety "spaghetti" and very hard to debug. My attempts here is to shorten the process of tracking this phenomenon.
Sorry to be the "squeaky wheel" or the "gum on one's shoe" ... any help would be appreciated
March 30, 2010 at 11:36 am
Not a problem at all Tom. Does DBCC INPUTBUFFER not return the data you are looking for? If you're looking for the statement right before the trigger that caused the update but not the initial statement that launched the batch, I believe your best bet is profiler. What I'd do:
Use DBCC INPUTBUFFER in the trigger to find out what is triggering the update.
Then, open up a Query Analyzer window and copy/paste the line that DBCC IB gave you. (Don't run it yet).
Open up Profiler and point it at the SPID of your Query Analyzer window.
Run the statement.
Examine the trace, figure out what is doing it.
You can also wrap the statement in a BEGIN TRAN...ROLLBACK TRAN so that you can keep rerunning it / run it with different values.
March 30, 2010 at 9:31 pm
I used your code (below) inside my trigger:
INSERT INTO #B(EventType, Parameters, EventInfo)
EXEC ('dbcc inputbuffer (' + @@spid + ') with no_infomsgs')
SELECT * FROM #B
It returned the code from inside the trigger.
While I can run traces and a lot of DBA functions in our Development environment, very few people can do so in our Production environment. Getting them to do so is like pulling teeth, so this was my meager attempt to see if I could pinpoint the process or SP that is changing the data in the one field in our table.
As it is, I capture the date and time and the changed values. If they change the data from the COTS package "control panel", I can detect that and get the person's userid. My boss suspects it is some other process that is modifying the data unintentionally. My hope was to capture the name of the SP causing the table update, insert, or delete as well.
Thanks!
March 31, 2010 at 8:08 am
dbcc inputbuffer should not be returning trigger code. fn_get_sql will, but DBCC Inputbuffer should not.
Are you sure you're seeing that function's result and not the other one?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply