SQL Server Performance

DBCC INPUTBUFFER

Discussion in 'General DBA Questions' started by sgovoni, Jul 1, 2008.

  1. sgovoni New Member

    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
  2. moh_hassan20 New Member

    EventInfo is nvarchar(4000)
    do you store the results in a table with field eventinfo as nvarchar(4000) ?
  3. sgovoni New Member

    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
  4. moh_hassan20 New Member

    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
  5. sgovoni New Member

    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
  6. satya Moderator

  7. sgovoni New Member

    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!
  8. sgovoni New Member

    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 ?
  9. satya Moderator

    Then you should depend on PROFILER or server side trace to catch the events that are firing that will have all the underlying connection & statements that are passed.
  10. sgovoni New Member

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

Share This Page