Sql-server – SQL Server 2008 R2 Database Diagram Error Code

rdbmssql serversql-server-2008-r2ssmst-sql

I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error message:

===================================

>String or binary data would be truncated.
>The statement has been terminated.
>The sp_creatediagram procedure attempted to return a status of `NULL`, which is not allowed. A status of 0 will be returned instead. (.Net SqlClient Data Provider)

Program Location: at Microsoft.SqlServer.Management.DataTools.Interop.IDTDocTool.Save(Object dsRef, String path, Boolean okToOverwrite)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.DatabaseDesignerNode.Save(VSSAVEFLAGS dwSave, String strSilentSaveAsName, IVsUIShell pIVsUIShell, IntPtr punkDocDataIntPtr, String& strMkDocumentNew, Int32& pfCanceled)

I don't want to ALTER the database at all.

My goal is the save the diagram to references key relationships and other information in the future.

Does anyone know how I can enable saving the database diagram this way?

Best Answer

This can happen if your database was created in a previous version of SQL Server. You need to recreate the table [dbo].[sysdiagrams] or if you do not want to lose already existing diagrams try this:

ALTER table [dbo].[sysdiagrams]
DROP COLUMN [diagram_id]

GO

ALTER table [dbo].[sysdiagrams]
ADD [diagram_id] [int] NOT NULL IDENTITY (1, 1)
Related Question