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

Error changing startup parameters after 8patch

Please help! Here is what happened in details:
----
Originally, the SQL Server install placed the data and log files in
d:\Program Files\Microsoft SQL Server\MSSQL\Data\ folder and the error
logs in d:\Program Files\Microsoft SQL Server\MSSQL\Log folder.
----
For syntactical (commands that do not like spaces in folder or file
names) and security and organizational (separate data and logs to
different drives) reasons, I decided to place them in various
locations. Using http://support.microsoft.com/?kbid=224071 as a
guide, I moved the data file to d:\mssql\data\ and log files to
e:\mssql\transaction_log\ and error logs to e:\mssql\error_log\ by
changing the startup parameters:
-dD:\MSSQL\Data\master.mdf
-lE:\MSSQL\Transaction_Log\mastlog.ldf
-eE:\MSSQL\Error_Log
I followed the exact steps detailed from the link ("Moving the Master
Database").
No problems occurred.
----
I remember I ran the above procedures twice because I renamed one of
the Tlog folder to Transaction_Log. Again, that did not pose any
problems.
----
From time to time, Window updates and this patch were installed on
this machine:
- - ---
Title: Cumulative Patch for Microsoft SQL Server (815495)
Date: 23 July 2003
Software:
- Microsoft SQL Server 7.0
- Microsoft Data Engine (MSDE) 1.0
- Microsoft SQL Server 2000
- Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)
- Microsoft SQL Server 2000 Desktop Engine (Windows)
Impact: Run code of attacker's choice
Max Risk: Important
Bulletin: MS03-031
Microsoft encourages customers to review the Security Bulletins at:
http://www.microsoft.com/technet/security/bulletin/MS03-031.asp
http://www.microsoft.com/security/security_bulletins/ms03-031.asp
- ---
----
The next time, I again attempted to change the location of the error
log file by changing the startup parameters to
-eD:\MSSQL\Process_Log\SQL_Error_Log. On step 8 of the "Moving the
Master Database" instructions, SQL Server was having problems starting
up. The error message received was:
initerrlog: Could not open error log file
'D:\MSSQL\Process_Log\SQL_Error_Log'. Operating system error = 5(Access is denied.).
You could not start SQL Server from anywhere except I found that
running sqlservr.exe from the command prompt with the temporary
startup parameters of the error log file of the original location
worked (sqlservr.exe -eD:\Program Files\Microsoft SQL
Server\MSSQL\Log). SQL server runs under this mode, but SQL Server
Agent cannot start up even still.
While the SQL Server is running using the above command, I attempted
to change the Master Start Parameters of the Error Log back to
-eE:\MSSQL\Error_Log
and even -ed:\Program Files\Microsoft SQL Server\MSSQL\Log
but found no success starting the SQL Server with Enterprise Manager
or Services interfaces (sqlservr.exe was shutdown prior to attempt).
----
I am led to believe that the patch 815495 may have been involved with
the error. There have been times where Enterprise Manager freezes up
and automatically shuts down (occurs at least once a week) when
working within EM such as opening properties of Database Maintenance
Plan. This had not occurred before. In addition all the SQL logs
have been wiped out.
I have installed the same patch to my personal WinXP machine and the
Enterprise Manager no longer works (unable to open and receive an
error message to forward error to Microsoft) even with a reinstall.
These same results occurred on two server machines which were prepared
for production and standby. I don't know what else to do? Please
help!
Thanks so much,
JuneThe Error message you are receiving is access is denied to the log files.
Check your NTFS permissions, and make sure that the SQL Server account has
full control of the log files and the folder that they are in.
--
Denny Cherry
DBA
GameSpy Industries
"June" <jnguyen@.harbourcg.com> wrote in message
news:29f6d8aa.0309121232.54032ff@.posting.google.com...
> Please help! Here is what happened in details:
> ----
--
> Originally, the SQL Server install placed the data and log files in
> d:\Program Files\Microsoft SQL Server\MSSQL\Data\ folder and the error
> logs in d:\Program Files\Microsoft SQL Server\MSSQL\Log folder.
> ----
--
> For syntactical (commands that do not like spaces in folder or file
> names) and security and organizational (separate data and logs to
> different drives) reasons, I decided to place them in various
> locations. Using http://support.microsoft.com/?kbid=224071 as a
> guide, I moved the data file to d:\mssql\data\ and log files to
> e:\mssql\transaction_log\ and error logs to e:\mssql\error_log\ by
> changing the startup parameters:
> -dD:\MSSQL\Data\master.mdf
> -lE:\MSSQL\Transaction_Log\mastlog.ldf
> -eE:\MSSQL\Error_Log
> I followed the exact steps detailed from the link ("Moving the Master
> Database").
> No problems occurred.
> ----
--
> I remember I ran the above procedures twice because I renamed one of
> the Tlog folder to Transaction_Log. Again, that did not pose any
> problems.
> ----
--
> From time to time, Window updates and this patch were installed on
> this machine:
> - - ---
> Title: Cumulative Patch for Microsoft SQL Server (815495)
> Date: 23 July 2003
> Software:
> - Microsoft SQL Server 7.0
> - Microsoft Data Engine (MSDE) 1.0
> - Microsoft SQL Server 2000
> - Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)
> - Microsoft SQL Server 2000 Desktop Engine (Windows)
> Impact: Run code of attacker's choice
> Max Risk: Important
> Bulletin: MS03-031
> Microsoft encourages customers to review the Security Bulletins at:
> http://www.microsoft.com/technet/security/bulletin/MS03-031.asp
> http://www.microsoft.com/security/security_bulletins/ms03-031.asp
> - ---
> ----
--
> The next time, I again attempted to change the location of the error
> log file by changing the startup parameters to
> -eD:\MSSQL\Process_Log\SQL_Error_Log. On step 8 of the "Moving the
> Master Database" instructions, SQL Server was having problems starting
> up. The error message received was:
> initerrlog: Could not open error log file
> 'D:\MSSQL\Process_Log\SQL_Error_Log'. Operating system error => 5(Access is denied.).
> You could not start SQL Server from anywhere except I found that
> running sqlservr.exe from the command prompt with the temporary
> startup parameters of the error log file of the original location
> worked (sqlservr.exe -eD:\Program Files\Microsoft SQL
> Server\MSSQL\Log). SQL server runs under this mode, but SQL Server
> Agent cannot start up even still.
> While the SQL Server is running using the above command, I attempted
> to change the Master Start Parameters of the Error Log back to
> -eE:\MSSQL\Error_Log
> and even -ed:\Program Files\Microsoft SQL Server\MSSQL\Log
> but found no success starting the SQL Server with Enterprise Manager
> or Services interfaces (sqlservr.exe was shutdown prior to attempt).
> ----
--
> I am led to believe that the patch 815495 may have been involved with
> the error. There have been times where Enterprise Manager freezes up
> and automatically shuts down (occurs at least once a week) when
> working within EM such as opening properties of Database Maintenance
> Plan. This had not occurred before. In addition all the SQL logs
> have been wiped out.
> I have installed the same patch to my personal WinXP machine and the
> Enterprise Manager no longer works (unable to open and receive an
> error message to forward error to Microsoft) even with a reinstall.
> These same results occurred on two server machines which were prepared
> for production and standby. I don't know what else to do? Please
> help!
> Thanks so much,
> June