rohit singh rohit singh - 1 month ago 19
SQL Question

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

Answer

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.