DBCC INPUTBUFFER

Hi,
within a trigger I use the command "DBCC INPUTBUFFER (@@SPID) WITH NO_INFOMSGS" to capture the statement that the client has sent to the server.

The column EventInfo, however, contains only the first 255 characters of the statement.

I need to know the all entire text of command, do you have any suggestion for me ?

Thanks

EventInfo is nvarchar(4000)
do you store the results in a table with field eventinfo as nvarchar(4000) ?

Hi moh_hassan20,
thanks for replay, I store the results in a table with field EventInfo ad VarChar(2000) but only first 255 characters are stored.
Sample Code:
DECLARE @TextData NVARCHAR(2000),

CREATE TABLE #TraceInfo
(EventType NVARCHAR(30),
Parameters INTEGER,
EventInfo NVARCHAR(2000))
INSERT INTO #TraceInfo EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS')
Thanks
Sergio

i think you use sql server 2000
If your engine is sql server 2000 , EventInfo is nvarchar(255) []
If your engine is sql server 2005 , EventInfo is expanded to nvarchar(4000)
if you use fn_get_sql , you will get the complete text of sql statement (even in sql 2000), which is similar to the DBCC INPUTBUFFER command.
For more info: http://msdn.microsoft.com/en-us/library/ms189451.aspx

Thanks moh_hassan20,
I have a mixed situation: some installation using SQL Server 2000, other installations SQL Server 2005. Well, with SQL Server 2005 use ::fn_get_sql.
Thanks
Bye

Thanks for your answer.
I still have a problem: use the function:: fn_get_sql () with SQL Server 2000 within a trigger, such as:
...IF UPDATE(<FieldName>)
BEGIN
--DELETE FROM #TraceInfo
--INSERT INTO #TraceInfo EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS')
--SET @TextData = (SELECT Eventinfo FROM #TraceInfo)SELECT @Handle = s.sql_handle
FROM master.dbo.sysprocesses s
WHERE s.spid = @@SPID
SELECT @TextData = CAST(Text As NVarChar(4000)) FROM ::fn_get_sql(@Handle)....
....
The variable @TextData contains the text of command for creating the trigger, such as: CREATE TRIGGER... But, I want to know the sql command that has fire the trigger.
How can solve the problem ?
Thanks!

From Books OnLine:
The fn_get_sql function returns information that is similar to the DBCC INPUTBUFFER command. The following are examples of when the fn_get_sql function can be used because DBCC INPUTBUFFER cannot be:

• When events have more than 255 characters.
  
  • When you have to return the highest current nesting level of a stored procedure. For example, there are two stored procedures that are named sp_1 and sp_2. If sp_1 calls sp_2 and you obtain the handle from the sys.dm_exec_requests dynamic management view while sp_2 is running, the fn_get_sql function returns information about sp_2. Additionally, the fn_get_sql function returns the complete text of the stored procedure at the highest current nesting level.

• The Triggers is similar as stored procedure and the fn_get_sql function returns the complete text of the stored procedure at the highest current nesting level (= my trigger).
  What other roads ?

Can I easily know the old and new value of a field in the case run a command to update ?