Search code examples
sqlsql-serversql-server-2012dbcc

SQL Update table error : DBCC execution completed. If DBCC printed error messages, contact your system administrator


update Calls 
set 
Call_Closed = GETDATE()
where CallId = 4266576;

I have a table named calls. Whenever i'm trying to update any column in this table it throws an error. I have logged in with "sa" account in the server and it is the admin account. The above column datetype is a datetime. Also,updating any column in the table is throwing the below error.

Error Details

*String or binary data would be truncated.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The statement has been terminated.*

Trigger on the table

CREATE TRIGGER tgrMonitorDoneWho ON Calls
FOR INSERT,UPDATE
AS  
begin   

    DECLARE @ExecStr varchar(50), @Qry nvarchar(255)

    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)


    INSERT INTO tblDoneWhoMonitor(CallId,DoneWho,Operation,Query)
    SELECT  case
            when inserted.CallId is null then deleted.CallId
            when deleted.CallId is null then inserted.CallId
            else deleted.CallId end,
            case
            when inserted.CallId is null then deleted.DoneWho
            when deleted.CallId is null then inserted.DoneWho
            else deleted.DoneWho end, 
            case
            when inserted.CallId is null then 'DELETE'
            when deleted.CallId is null then 'INSERT'
            else  'UPDATE' end,
            @Qry
    FROM inserted
    FULL OUTER JOIN deleted
    ON inserted.CallId = deleted.CallId
end

Solution

  • The EventInfo column is too small. Change it to max. You will need to change the @Qry to nvarchar(max) as well.

    CREATE TABLE #inputbuffer 
        (
          EventType nvarchar(30), 
          Parameters int, 
          EventInfo nvarchar(max)
        )
    
    @Qry nvarchar(max)
    

    And in turn you will need to modify the table definition for tblDoneWhoMonitor to accept the Query as well.