Error creating new database diagrams in upgraded database

(I'm using MS SQL Server Management Studio, SQL Server version 9.0.3054)
I restored a database originally created in sql 2000 to a sql 2005 server. I
also set the compatibility level to sql 2005 (90). The owner is sa. When I
try to create a new database diagram, I first get the message "This database
does not have one or more of the support objects required to use database
diagramming. Do you wish to create them?" I click Yes to that. Then I get
the message "Violation of UNIQUE KEY constraint 'UK_principal_name'. Cannot
insert duplicate key in object 'dbo.sysdiagrams'. The statement has been
terminated. (Microsoft SQL Server, Error: 2627)".
I can create diagrams for AdventureWorks. Is there a way to fix this other
than recreating the database from scratch as sql 2005?
Thanks.
WWW.ROVA.COMM
Thankfully, the diagramming objects are benign and you can remove them with
little ill effect. I'm not sure if some of these will fail if they didn't
come over from 2000, but the whole script should run and then you should be
able to say yes to that prompt sufccessfully.
DROP PROCEDURE sp_upgraddiagrams;
GO
DROP PROCEDURE sp_helpdiagrams;
GO
DROP PROCEDURE sp_helpdiagramdefinition;
GO
DROP PROCEDURE sp_creatediagram;
GO
DROP PROCEDURE sp_renamediagram;
GO
DROP PROCEDURE sp_alterdiagram;
GO
DROP PROCEDURE sp_dropdiagram;
GO
DROP FUNCTION fn_diagramobjects;
GO
DROP TABLE sysdiagrams;
GO
I asked for a button to facilitate this a long time ago, but not too many
people seem to think it is a good idea.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125014
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"MCG" <MCG@.discussions.microsoft.com> wrote in message
news:A16BFF97-E832-4154-8391-712B1C415BE8@.microsoft.com...
> (I'm using MS SQL Server Management Studio, SQL Server version 9.0.3054)
> I restored a database originally created in sql 2000 to a sql 2005 server.
> I
> also set the compatibility level to sql 2005 (90). The owner is sa. When I
> try to create a new database diagram, I first get the message "This
> database
> does not have one or more of the support objects required to use database
> diagramming. Do you wish to create them?" I click Yes to that. Then I get
> the message "Violation of UNIQUE KEY constraint 'UK_principal_name'.
> Cannot
> insert duplicate key in object 'dbo.sysdiagrams'. The statement has been
> terminated. (Microsoft SQL Server, Error: 2627)".
> I can create diagrams for AdventureWorks. Is there a way to fix this other
> than recreating the database from scratch as sql 2005?
> Thanks.
> --
> WWW.ROVA.COMM
|||I was unable to reproduce this issue, btw. I created a database in 2000,
created a diagram, backed it up, then restored on 2005, set a valid owner,
changed cmptlevel to 90, expanded database diagrams node in object explorer,
said yes to the prompt, and I was able to modify the existing diagram and
create a new one. How many diagrams had you created in 2000 before you
backed up the database and restored it in 2005?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"MCG" <MCG@.discussions.microsoft.com> wrote in message
news:A16BFF97-E832-4154-8391-712B1C415BE8@.microsoft.com...
> (I'm using MS SQL Server Management Studio, SQL Server version 9.0.3054)
> I restored a database originally created in sql 2000 to a sql 2005 server.
> I
> also set the compatibility level to sql 2005 (90). The owner is sa. When I
> try to create a new database diagram, I first get the message "This
> database
> does not have one or more of the support objects required to use database
> diagramming. Do you wish to create them?" I click Yes to that. Then I get
> the message "Violation of UNIQUE KEY constraint 'UK_principal_name'.
> Cannot
> insert duplicate key in object 'dbo.sysdiagrams'. The statement has been
> terminated. (Microsoft SQL Server, Error: 2627)".
> I can create diagrams for AdventureWorks. Is there a way to fix this other
> than recreating the database from scratch as sql 2005?
> Thanks.
> --
> WWW.ROVA.COMM
|||I tried that script, running it against my database (and against master (!)
just in case). None of those procedures existed, I think. I got this for each
one:
Cannot drop the procedure 'sp_upgraddiagrams', because it does not exist or
you do not have permission. (I'm running as 'SA' by the way.)
I tried creating diagrams again but got the same error.
I only had 4 or 5 diagrams, which I don't really care about. I just wanna
make some new ones.
Thanks for the quick response.
WWW.ROVA.COMM
"Aaron Bertrand [SQL Server MVP]" wrote:

> Thankfully, the diagramming objects are benign and you can remove them with
> little ill effect. I'm not sure if some of these will fail if they didn't
> come over from 2000, but the whole script should run and then you should be
> able to say yes to that prompt sufccessfully.
> DROP PROCEDURE sp_upgraddiagrams;
> GO
> DROP PROCEDURE sp_helpdiagrams;
> GO
> DROP PROCEDURE sp_helpdiagramdefinition;
> GO
> DROP PROCEDURE sp_creatediagram;
> GO
> DROP PROCEDURE sp_renamediagram;
> GO
> DROP PROCEDURE sp_alterdiagram;
> GO
> DROP PROCEDURE sp_dropdiagram;
> GO
> DROP FUNCTION fn_diagramobjects;
> GO
> DROP TABLE sysdiagrams;
> GO
> I asked for a button to facilitate this a long time ago, but not too many
> people seem to think it is a good idea.
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125014
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "MCG" <MCG@.discussions.microsoft.com> wrote in message
> news:A16BFF97-E832-4154-8391-712B1C415BE8@.microsoft.com...
>
>
|||Sorry, it should have been
DROP <object> SYS.<object_name>, e.g. (most importantly, I think):
DROP TABLE sys.sysdiagrams;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"MCG" <MCG@.discussions.microsoft.com> wrote in message
news:31A0508C-6D52-4543-BDC0-CE6FDFE3324C@.microsoft.com...[vbcol=seagreen]
>I tried that script, running it against my database (and against master (!)
> just in case). None of those procedures existed, I think. I got this for
> each
> one:
> Cannot drop the procedure 'sp_upgraddiagrams', because it does not exist
> or
> you do not have permission. (I'm running as 'SA' by the way.)
> I tried creating diagrams again but got the same error.
> I only had 4 or 5 diagrams, which I don't really care about. I just wanna
> make some new ones.
> Thanks for the quick response.
> --
> WWW.ROVA.COMM
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||> Sorry, it should have been
> DROP <object> SYS.<object_name>, e.g. (most importantly, I think):
> DROP TABLE sys.sysdiagrams;
Ignore that, I need more sleep.
|||Oh, and what happens when you run:
SELECT * FROM dbo.sysdiagrams
?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"MCG" <MCG@.discussions.microsoft.com> wrote in message
news:31A0508C-6D52-4543-BDC0-CE6FDFE3324C@.microsoft.com...[vbcol=seagreen]
>I tried that script, running it against my database (and against master (!)
> just in case). None of those procedures existed, I think. I got this for
> each
> one:
> Cannot drop the procedure 'sp_upgraddiagrams', because it does not exist
> or
> you do not have permission. (I'm running as 'SA' by the way.)
> I tried creating diagrams again but got the same error.
> I only had 4 or 5 diagrams, which I don't really care about. I just wanna
> make some new ones.
> Thanks for the quick response.
> --
> WWW.ROVA.COMM
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||When I run SELECT * FROM dbo.sysdiagrams on my app's database I get this:
Invalid object name 'dbo.sysdiagrams'. There's only one sys table in my
database: dbo.dtproperties.
Thanks again for your help.
WWW.ROVA.COMM
"Aaron Bertrand [SQL Server MVP]" wrote:

> Oh, and what happens when you run:
> SELECT * FROM dbo.sysdiagrams
> ?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "MCG" <MCG@.discussions.microsoft.com> wrote in message
> news:31A0508C-6D52-4543-BDC0-CE6FDFE3324C@.microsoft.com...
>
>
|||MCG (MCG@.discussions.microsoft.com) writes:
> I tried that script, running it against my database (and against master
> (!) just in case). None of those procedures existed, I think. I got this
> for each one:
> Cannot drop the procedure 'sp_upgraddiagrams', because it does not exist
> or you do not have permission. (I'm running as 'SA' by the way.)
> I tried creating diagrams again but got the same error.
> I only had 4 or 5 diagrams, which I don't really care about. I just wanna
> make some new ones.
Sounds like there is some funny data in dtproperties, which causes the
index violation. While it could be interesting to file a bug for it,
the quickest way to get it working is probably to simply drop
dtproperties:
DROP TABLE dtproperties
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Erland, the table that is coming up with the error is sysdiagrams, not
dtproperties. At least, that's what the error message in the OP said.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9954F23EE870DYazorman@.127.0.0.1...
> MCG (MCG@.discussions.microsoft.com) writes:
> Sounds like there is some funny data in dtproperties, which causes the
> index violation. While it could be interesting to file a bug for it,
> the quickest way to get it working is probably to simply drop
> dtproperties:
> DROP TABLE dtproperties
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx