Wednesday, July 18, 2012

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] )
          SELECT name
               , principal_id
               , @id
               , [version]
               , [definition]
            FROM inserted
END --tr_sysdiagrams_i
Explanation: 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.