<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Search:
Home
Articles
Editorials
Forums
Scripts
Blogs
QotD
Books
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Advertise
Write for us
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
General
»
Using the INPUTBUFFER to find last statement...
12 posts, Page 1 of 2
1
2
»»
Using the INPUTBUFFER to find last statement from client
Rate Topic
Display Mode
Topic Options
Author
Message
Joshua
Joshua
Posted Monday, October 29, 2007 3:03 PM
Forum Newbie
Group: General Forum Members
Last Login: Monday, November 24, 2008 5:03 PM
Points: 8,
Visits: 20
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
Post #416217
Mayank Khatri
Mayank Khatri
Posted Monday, October 29, 2007 3:13 PM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 1,581,
Visits: 969
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)
Post #416221
Dinakar Nethi
Dinakar Nethi
Posted Monday, October 29, 2007 3:17 PM
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, June 23, 2009 10:16 AM
Points: 265,
Visits: 79
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.
******************
Post #416223
Joshua
Joshua
Posted Monday, October 29, 2007 3:46 PM
Forum Newbie
Group: General Forum Members
Last Login: Monday, November 24, 2008 5:03 PM
Points: 8,
Visits: 20
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
Post #416236
happycat59
happycat59
Posted Monday, October 29, 2007 8:35 PM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 6:14 AM
Points: 1,437,
Visits: 690
The code supplied is for SQL 2005. Instead of sys.sysprocesses, try dbo.sysprocesses
Post #416274
Joshua
Joshua
Posted Tuesday, October 30, 2007 9:25 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, November 24, 2008 5:03 PM
Points: 8,
Visits: 20
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
Post #416586
Mayank Khatri
Mayank Khatri
Posted Tuesday, October 30, 2007 10:00 AM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 1,581,
Visits: 969
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.
Post #416622
Joshua
Joshua
Posted Tuesday, October 30, 2007 10:40 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, November 24, 2008 5:03 PM
Points: 8,
Visits: 20
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.
Post #416651
Mayank Khatri
Mayank Khatri
Posted Tuesday, October 30, 2007 10:52 AM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 1,581,
Visits: 969
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.
Post #416661
Joshua
Joshua
Posted Tuesday, October 30, 2007 11:12 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, November 24, 2008 5:03 PM
Points: 8,
Visits: 20
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.
Post #416677
« Prev Topic
|
Next Topic »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
may
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2009 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use
