December 17, 2008 at 9:59 pm
I created a log file that is intended to capture the sql statement being used to insert/ update/delete a record in a table (see the attached code snippet) but even though I created the statement field to be varchar (4000) the captured statement is being cut off...
Is there any other way to capture the whole statement.
December 17, 2008 at 10:17 pm
ksprashanths (12/17/2008)
I created a log file that is intended to capture the sql statement being used to insert/ update/delete a record in a table (see the attached code snippet) but even though I created the statement field to be varchar (4000) the captured statement is being cut off...Is there any other way to capture the whole statement.
You didn't show the code that you used. You also didn't write how much of the text you can see. If I remember correctly, with SQL Server 2000 dbcc inputbuffer showed only 255 characters of the statement. Are you using SQL Server 2000 or SQL Server 2005?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 17, 2008 at 10:20 pm
Cant see ur code but if varchar(4000) is the problem try using VARCHAR(MAX).
"Keep Trying"
December 17, 2008 at 10:34 pm
This is the trigger i am using to get the insert/update/delete Statements from InputBuffer
ALTER TRIGGER [dbo].[StudentTrigger]
ON [dbo].[Students]
AFTER Insert, UPDATE, DELETE
AS
Declare @SqlStatement Nvarchar(Max)
CREATE TABLE SQLLog (
language_event NVARCHAR(100),
parameters INT,
event_info NVARCHAR(4000))
INSERT INTO SQLLog (language_event, parameters, event_info)
EXEC('DBCC INPUTBUFFER(@@SPID);');
Select @SqlStatement = event_info from SQLLog
Print SqlStatement
Drop Table SQLLog
December 18, 2008 at 1:05 am
Is this is your real trigger? Your trigger will send the results of the DBCC INPUTBUFFER to the client, but it won't save the data into a table. Also you don't have to create a table and drop it each time. If all you want to do is to see the statement that the user activated, you can just activate the dbcc inputbuffer statement without inserting it into a table and then selecting it from the table and dropping the table. One last remark - only members of sysadmin server role can run this DBCC. Is your application is connecting to the server as with a login that belongs to the sysadmin server role? If it is, then you should change that. It would be best if you'll explain what you are trying to do. If you'll do so others might give you a better approach.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 18, 2008 at 5:25 am
If you are using SQL 2005, rather use the sys.dm_exec_sql_text than inputbuffer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2008 at 6:47 am
Gail beat me to it. You're in a brave new world. Use the DMV's and DMF's liberally. They're great and provide a lot more information in a much clearer fashion than the old functions do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 18, 2008 at 8:21 am
DBCC INPUTBUFFER returns the proc being used, with parameters and full path, vs. the DMV's return the definition of the procedure being created.
December 18, 2008 at 8:31 am
Ah, but other DMV's will capture the parameters, the execution plan, aggregate performance characteristics... And they all join together and all through TSQL queries...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply