SQL Server 2008 R2 Database Diagram Error Code

Question

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?

Answer 1

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)

Answer 2

One way to migrate a diagram is to script it in the source database and recreate it one the destination. I do not remember where I have found the procedure below, but I have used several times to migrate diagrams between databases (even of different versions):

IF EXISTS (SELECT TOP 1 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_ScriptDiagram]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[usp_ScriptDiagram]
GO 
CREATE PROCEDURE [dbo].[usp_ScriptDiagram]( 
 @name VARCHAR(128)) 
AS 
BEGIN 
 DECLARE @diagram_id INT 
 DECLARE @index INT 
 DECLARE @size INT 
 DECLARE @chunk INT 
 DECLARE @line VARCHAR(max) 
 -- Set start index, and chunk 'constant' value 
 SET @index = 1 -- 
 SET @chunk = 32 -- values that work: 2, 6 
 -- values that fail: 15,16, 64 
 -- Get PK diagram_id using the diagram's name (which is what the user is familiar with) 
 SELECT 
 @diagram_id=diagram_id, 
 @size = DATALENGTH(definition) 
 FROM sysdiagrams 
 WHERE [name] = @name 
 IF @diagram_id IS NULL 
 BEGIN 
 PRINT '/*<error> 
Diagram name [' + @name + '] could not be found. 
</error>*/' 
 END 
 ELSE -- Diagram exists 
 BEGIN 
 PRINT '/*' 
 PRINT '<summary>' 
 PRINT 'Restore diagram ''' + @name + '''' 
 PRINT '</summary>' 
 PRINT '<generated>' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16) + '</generated>' 
 PRINT '*/' 
 PRINT 'PRINT ''=== Tool_ScriptDiagram2008 restore diagram [' + @name + '] ===''' 
 PRINT ' -- If the sysdiagrams table has not been created in this database, create it! 
 IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''sysdiagrams'') 
 BEGIN 
 -- Create table script generated by Sql Server Management Studio 
 -- _Assume_ this is roughly equivalent to what Sql Server/Management Studio 
 -- creates the first time you add a diagram to a 2008 database 
 CREATE TABLE [dbo].[sysdiagrams]( 
 [name] [sysname] NOT NULL, 
 [principal_id] [int] NOT NULL, 
 [diagram_id] [int] IDENTITY(1,1) NOT NULL, 
 [version] [int] NULL, 
 [definition] [varbinary](max) NULL, 
 PRIMARY KEY CLUSTERED 
 ( 
 [diagram_id] ASC 
 )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) , 
 CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED 
 ( 
 [principal_id] ASC, 
 [name] ASC 
 )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) 
 ) 
 EXEC sys.sp_addextendedproperty @name=N''microsoft_database_tools_support'', @value=1 , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N''sysdiagrams'' 
 PRINT ''[sysdiagrams] table was created as it did not already exist'' 
 END 
 -- Target table will now exist, if it didn''t before' 
 PRINT 'SET NOCOUNT ON -- Hide (1 row affected) messages' 
 PRINT 'DECLARE @newid INT' 
 PRINT 'DECLARE @DiagramSuffix varchar (50)' 
 PRINT '' 
 PRINT 'PRINT ''Suffix diagram name with date, to ensure uniqueness''' 
 PRINT 'SET @DiagramSuffix = '' '' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)' 
 PRINT '' 
 PRINT 'PRINT ''Create row for new diagram''' 
 -- Output the INSERT that _creates_ the diagram record, with a non-NULL [definition], 
 -- important because .WRITE *cannot* be called against a NULL value (in the WHILE loop) 
 -- so we insert 0x so that .WRITE has 'something' to append to... 
 PRINT 'BEGIN TRY' 
 PRINT ' PRINT ''Write diagram ' + @name + ' into new row (and get [diagram_id])''' 
 SELECT @line = 
 ' INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])' 
 + ' VALUES (''' + [name] + '''+@DiagramSuffix, '+ CAST (principal_id AS VARCHAR(100))+', '+CAST (version AS VARCHAR(100))+', 0x)' 
 FROM sysdiagrams WHERE diagram_id = @diagram_id 
 PRINT @line 
 PRINT ' SET @newid = SCOPE_IDENTITY()' 
 PRINT 'END TRY' 
 PRINT 'BEGIN CATCH' 
 PRINT ' PRINT ''XxXxX '' + Error_Message() + '' XxXxX''' 
 PRINT ' PRINT ''XxXxX END Tool_ScriptDiagram2008 - fix the error before running again XxXxX''' 
 PRINT ' RETURN' 
 PRINT 'END CATCH' 
 PRINT '' 
 PRINT 'PRINT ''Now add all the binary data...''' 
 PRINT 'BEGIN TRY' 
 WHILE @index < @size 
 BEGIN 
 -- Output as many UPDATE statements as required to append all the diagram binary 
 -- data, represented as hexadecimal strings 
 SELECT @line = 
 ' UPDATE sysdiagrams SET [definition] .Write (' 
 + ' ' + UPPER(sys.fn_varbintohexstr (SUBSTRING (definition, @index, @chunk))) 
 + ', null, 0) WHERE diagram_id = @newid -- index:' + CAST(@index AS VARCHAR(100)) 
 FROM sysdiagrams 
 WHERE diagram_id = @diagram_id 
 PRINT @line 
 SET @index = @index + @chunk 
 END 
 PRINT '' 
 PRINT ' PRINT ''=== Finished writing diagram id '' + CAST(@newid AS VARCHAR(100)) + '' ===''' 
 PRINT ' PRINT ''=== Refresh your Databases-[DbName]-Database Diagrams to see the new diagram ===''' 
 PRINT 'END TRY' 
 PRINT 'BEGIN CATCH' 
 PRINT ' -- If we got here, the [definition] updates didn''t complete, so delete the diagram row' 
 PRINT ' -- (and hope it doesn''t fail!)' 
 PRINT ' DELETE FROM sysdiagrams WHERE diagram_id = @newid' 
 PRINT ' PRINT ''XxXxX '' + Error_Message() + '' XxXxX''' 
 PRINT ' PRINT ''XxXxX END Tool_ScriptDiagram2008 - fix the error before running again XxXxX''' 
 PRINT ' RETURN' 
 PRINT 'END CATCH' 
 END 
END 

usage:

exec [dbo].[usp_ScriptDiagram] ''