Monday, April 23, 2012

Cannot insert the value null into column ‘diagram_id’, table dbo.sysdiagrams

Error Condition: Cannot insert the value null into column ‘diagram_id’, table dbo.sysdiagrams

Problem: This sometimes occurs when a SQL Server database is ported from one version to another (such as 2000 to 2005 or 2005 to 2008) or possibly also when using the SSMS for a newer version of SQL Server against and older database.

Fix: Drop and recreated dbo.sysdiagrams making the column diagram_id an identity column. 


use [mydatabase]
GO

DROP TABLE [dbo].[sysdiagrams]
GO

CREATE TABLE [dbo].[sysdiagrams]
(   [name] [nvarchar](128) NOT NULL,
    [principal_id] [int] NOT NULL,
    [diagram_id] [int] identity(1,1) NOT NULL,
    [version] [int] NULL,
    [definition] [varbinary](max) NULL
)
GO



NOTE: If you want to preserve the diagrams, then rename the table and create a new one, then SELECT - INTO to copy the data into the new table.

No comments:

Post a Comment