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?


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]


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

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]
CREATE PROCEDURE [dbo].[usp_ScriptDiagram](  
    @name VARCHAR(128))  
    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)  
        @size = DATALENGTH(definition)   
    FROM sysdiagrams   
    WHERE [name] = @name  
    IF @diagram_id IS NULL  
        PRINT '/*<error>  
Diagram name [' + @name + '] could not be found.  
    ELSE -- Diagram exists  
        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!  
                    -- 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''  
                -- 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 '    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  
            -- 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  
        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 '    -- 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'  


exec [dbo].[usp_ScriptDiagram] ''

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.