CODEWITHSUNDEEP
sql-server
gnarbarian
gnarbarian

Reputation: 2819

unable to save diagram, "string or binary data would be truncated"

I established a bunch of relationships with foreign keys in a database. the relationships and constraints were saved. but when I add them to the diagram I receive the error: "string or binary data would be truncated". I initially added the relationships with the diagram tool. When I was unable to save it I tried both reopening the diagram and creating a new one. In both cases I am unable to save the diagram.

Upvotes: 0

Views: 164

Answers (2)

David Herpin
David Herpin

Reputation: 11

I had the same issue and the resolutions above did not help.

To solve this, I used SQL Profiler to capture the statement that was failing. I manually ran the following statement (obtained from Profiler) to get the error:

--Note: I'm not including the full string of Definition here - it's 10200 characters long.  But, in my case, just a few characters was enough to reproduce the error.


exec dbo.[sp_creatediagram] @diagramname=N'Diagram_1'
                            ,@owner_id=1
                            ,@version=1
                            ,@definition=0xD0CF11E0A1B11AE1

/*Returned:
    Msg 8152, Level 16, State 10, Procedure sysdiagrams_InsertAudit, Line 5
    String or binary data would be truncated.
    The statement has been terminated.
*/

--I turned on the following trace option.

DBCC TRACEON(460, -1);
GO

--Note, after turning on the above TRACE option, the sp_creatediagram statement will tell you where the issue occurred.

exec dbo.[sp_creatediagram] @diagramname=N'Diagram_2'
                            ,@owner_id=1
                            ,@version=1
                            ,@definition=0xD0CF11E0A1B11AE1

/*Returned:
    Msg 8152, Level 16, State 10, Procedure sysdiagrams_InsertAudit, Line 5
    String or binary data would be truncated.
    The statement has been terminated.
*/

In my case, the issue was in sysdiagrams_InsertAudit, which inserts changes to the diagram into a sysdiagrams_log table. To see the statement that was failing, I ran:

exec sp_helptext sysdiagrams_InsertAudit

The offending statement was "Insert Into sysdiagrams_Log". I changed the type/length of the Definition column in table sysdiagrams_log (this table was in the database related to the diagram, it is not a system table, like sysdiagrams is) from varbinary(1) to varbinary(max). (This matches the type/length in the sysdiagrams table).

alter table sysdiagrams_log
    alter column definition varbinary(max)

After this change, the sp_creatediagram statement worked. Note: If you use the SSMS designer to make the change, you may have to Disable the option in SSMS, "Prevent Saving Changes that require table recreate". This is found under, Tools, Options, Designers, Table and Database Designers.

Turn trace back off.

DBCC TRACEOFF(460, -1);
GO

Upvotes: 1

Mike Mooney
Mike Mooney

Reputation: 11989

Delete all of your diagrams, and then delete the sysdiagrams table. The next time you try to add a diagram it should prompt you to recreate it.

I believe there was an incompatibility between SQL 2005 and SQL 2008, where the fields in 2005 were smaller, so if you tried adding a 2008 diagram you'd get truncation issues

Upvotes: 3

Related Questions