Posted on Leave a comment

Error creating database diagrams

Error: Cannot insert the value NULL into column ‘diagram_id’, table ‘mydb.dbo.sysdiagrams’; column does not allow nulls. INSERT failes. 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)

Fix: Add the following trigger

CREATE TRIGGER tr_sysdiagrams_i ON dbo.sysdiagrams INSTEAD OF INSERT

DECLARE @id int

SELECT @id = max(A.diagram_id) + 1
FROM dbo.sysdiagrams A

INSERT INTO dbo.sysdiagrams
( name
, principal_id
, diagram_id
, [version]
, [definition] )
, principal_id
, @id
, [version]
, [definition]
FROM inserted
END –tr_sysdiagrams_i


I seem to remeber getting this same error when I was using the SQL Server 2008 Management Studio (SSMS) on a SQL Server 2005 instance. I got this again using the SSMS 2012 on a 2008R2 instance. Either the diagram_id is supposed to be an identity, or the id should be generated by SSMS. In either case, just making the column allow NULLs leads to other problems. So either have to recreate the table with the column as an identity, or add this trigger.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.