Using the INPUTBUFFER to find last statement from client

Question

Post reply

Using the INPUTBUFFER to find last statement from client

Joshua-531562

SSC Enthusiast

Points: 104
More actions
October 29, 2007 at 3:03 pm

#98675

I am using the DBCC INPUTBUFFER to determine the last statement from a client. I see that it only stores 255 characters of the query (statement). My statements are usually quite long. I would like to know how to get the entire statement. Is this possible?
I am looking for this information because I am creating a trigger to audit a table. I want to store the in the audit table the query that caused the trigger to fire. Currently I am only getting 255 characters of the statement. Thank you.
Portion of code I am using:
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
INSERT INTO #inputbuffer
EXEC (@ExecStr)
SET @Qry = (SELECT EventInfo FROM #inputbuffer)
~ Joshua

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 1

DBCC INPUTBUFFER gives first 255 charracters only. U can use below query to get all the characters or completer query

DECLARE @HANDLE BINARY(20)

SELECT @HANDLE = sql_handle from sys.sysprocesses where spid = 54

SELECT text FROM ::fn_get_sql(@handle)

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 2

Also, note that you can use CROSS APPLY to get the text directly and use it in a JOIN stmt instead of looping through. If your query is executing quickly the spid can be relieved even before you get to it.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Joshua-531562 SSC Enthusiast Points: 104 More actions · Answer 3

Mayank,

Thank you for the reply. When I run the statement I get an error:

Invalid object name 'sys.sysprocesses'.

I am using SQL Server 2000. I am quite new to triggers and this sort of SQL work. Thank you.

~ Joshua

happycat59 One Orange Chip Points: 29389 More actions · Answer 4

The code supplied is for SQL 2005. Instead of sys.sysprocesses, try dbo.sysprocesses

Joshua-531562 SSC Enthusiast Points: 104 More actions · Answer 5

When I use dbo.sysprocesses with my current database, I still get an error saying Invalid object name. If I use 'master' database, I get result with no rows. Any ideas? Thank you

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 6

Turn on your traceflag 2861

DBCC TRACEON(2861)

--yourfn_get_sql query

and Later on you can turn off your trace flag

DBCC TRACEOFF(2861)

The reason is handles expire very quickly and donot remain in cache. By turning on this flag they will remain in cache.

Joshua-531562 SSC Enthusiast Points: 104 More actions · Answer 7

This is what I have done:

DBCC TRACEON(2861)

DECLARE @HANDLE BINARY(20)

SELECT @HANDLE = sql_handle from dbo.sysprocesses where spid = 55

SELECT text FROM ::fn_get_sql(@handle)

Am I supposed to run this from master? If I run it from the database I am attempting to use, I get invalid object. If I run from master, it does not return anything even if I run a select statement after DBCC TRACEON(2861). I tried changing the numbers in parenthesis to match, but that didn't result in anything either.

I imagine there is something that I am not doing, but it is not obvious to me. Thank you.

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 8

Theses r the things that are coming into my mnind. Can u run both of those separately.First run the DBCC command, when u see success in the bottom screen of ur Query analyzer window, execute other part of the code. Before u run the second part can u make sure that ur checking for the correct SPID and not for the process that has been long been dead?

Also just for test cases u can use the below

DECLARE @HANDLE BINARY(20)

SELECT @HANDLE = sql_handle from sysprocesses where spid = @@SPID

SELECT * FROM ::fn_get_sql(@handle)

You should see in result pane above query where @@SPID is current session.

If u see the results above but not the results for ur query means ur SPID has been dead long time back.

Let me know the results.

Thanks.

Joshua-531562 SSC Enthusiast Points: 104 More actions · Answer 9

When I run what you provided, I do in fact get results. The text column contains the 3 statements that I ran to get the results in the first place. That is a good start. However, how do I get it to show me the query that ran that caused the trigger to fire? Is that possible? I thought that in my trigger if I knew the last statement that was run, it would show me the query that fired the trigger, however, using this code looks like I will get back the query that gets the query. Thank you. I appreciate everyones time.

Here is what I need to do:

1) Create an audit trigger that will copy rows from one table into an audit table upon inserting, deleting, or updating. (Seems like my trigger works well mostly)

2) Each row in the audit table will have a field that is the query that caused the trigger to fire. This way, I can better track down what is causing some weird data changes in a database that I do not yet understand.

rashmivbapat Grasshopper Points: 13 More actions · Answer 10

I m getting one different error related to same topic

The handle passed to fn_get_sql was invalid

If u have solution pls reply me.

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 11

Mayank Khatri

SSCertifiable

Points: 7984

July 11, 2008 at 7:45 am

#841487

Could you post the error too?Thanks