Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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)
share|improve this answer

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] ''

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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