CODEWITHSUNDEEP
sqlsql-serversql-server-2012ssms
AnotherDeveloper
AnotherDeveloper

Reputation: 1272

Error Creating Database Diagram in SSMS 2012

When I attempt to create a database diagram, I get the following error:

Cannot insert the value NULL into column 'diagram_id', table 'MyDB.dbo.sysdiagrams'; column does 
not allow nulls. INSERT fails.
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)

I am using SSMS 2012.

The database is set at a compatibility level of SQL Server 2012 (110)

@@Version is Microsoft SQL Server 2012 - 11.0.5343.0 (X64)

Upvotes: 3

Views: 1685

Answers (4)

Mr C
Mr C

Reputation: 61

@Monzur's answer does work - but if you want to keep any existing diagrams then best to rename the [dbo].[sysdiagrams] table rather than drop it.

So, steps would be:

  1. rename [dbo].[sysdiagrams] to [dbo].[sysdiagramsSaved]
  2. run the create table in this answer
  3. run the exec sys.sp_add..... in this answer
  4. resurrect your existing diagrams using inserts from sysdiagramsSaved to sysdiagrams. see example insert statement in this answer.
  5. when finished your resurrections, drop table sysdiagramsSaved

lemon squeezie

Upvotes: 0

Monzur
Monzur

Reputation: 1435

Hope this will help you, this script solved my issues

DROP TABLE dbo.sysdiagrams;
GO
CREATE TABLE [dbo].[sysdiagrams]
(
    [name] [sysname] NOT NULL,
    [principal_id] [int] NOT NULL,
    [diagram_id] [int] IDENTITY(1,1) PRIMARY KEY,
    [version] [int] NULL,
    [definition] [varbinary](max) NULL,
    CONSTRAINT [UK_principal_name] UNIQUE ([principal_id],[name])
);

GO
EXEC sys.sp_addextendedproperty 
  @name=N'microsoft_database_tools_support', 
  @value=1 , 
  @level0type=N'SCHEMA',
  @level0name=N'dbo', 
  @level1type=N'TABLE',
  @level1name=N'sysdiagrams';
GO

Upvotes: 1

Zamora
Zamora

Reputation: 21

Go to system tables and look for systemdiagrams table, and turn to YES the "indentity Specification" property for the field diagram_id

Upvotes: 2

Wes Palmer
Wes Palmer

Reputation: 880

Your problem is the diagram_ID when the table was created probably looked something like this

CREATE TABLE <table_name>
( diagram_ID INT NOT NULL PRIMARY KEY,
  n...,
)

This basically means that a NULL value cannot be inserted into that column because of the NOT NULL condition. So an insert statement like:

INSERT INTO <table_name>
(Diagram_ID, n...,)
VALUES
(NULL, n...,)

Would fail because of the NULL you would need to have a value in there like (since I called it an integer):

INSERT INTO <table_name>
(Diagram_ID, n...,)
VALUES
(23, n...,)

The column may also be an indentity column in which case you have no controll over what can be inserted into the table.

Upvotes: 2

Related Questions