Can't Create Diagrams in SQL Server 2005?
Published By: Doug Hughes on Feb 26, 2008 at 5:59 PM
Times Viewed: 5261
Categories: SQL
This issue has plagued me since moving to SQL Server 2005. I find the diagram tools help me visualize my data and relationships. The problem is that with SQL 2005 sometimes you get this error when you try to create a new diagram:
You can follow the instructions in the error message till the cows come home and still not be able to successfully create a diagram. This is true even if you set the owner to SA or some other administrator.
After much screaming and gnashing of teeth I finally learned that the problem isn't really with the owner, it's with the database compatibility level. Your database may be running in either SQL 7 or 2000 compatibility mode, not 2005.
To resolve the problem, in SQL Server Management Studio, right click on the database node and click Properties. Select Options on the left and set the compatibility level to SQL Server 2005 (90).
Just make that change, click OK and you're good to go.
Very good to know. We have a 2000 and 2005 server at work, and I have yet to develop any apps fresh on the 2005 environment. This will save me the gnashing of teeth. :-)
Posted By: Joshua Curtiss on Feb 27, 2008
thanks a lot..I struggled a lot with this error.nice solution
Posted By: JSJ on Apr 3, 2008
Thanks a lot..struggled a lot with this error.Nice solution
Posted By: JSJ on Apr 3, 2008
vaya puta mierda!
Posted By: Adsadsa on Apr 25, 2008
This is verrryyyyy goood solution
Thanque veryyyyyy much
Posted By: Rajan K on Aug 30, 2008
thank you
Posted By: soliman on Nov 5, 2008
Goodday every One,
I have problem with sql 2005 and 2000 together,to great Database Diagrams. How?
Now ? tell you how.
I have two sql server in my machin, one of them is sql 2000 woking for Program "LOGO GO" and second server for me(I am studing in Course software Microsoft). My sql version 2005 and there ? cannt dowload upgrate anything(my friends say, due to sql 2000 server)
and ? have sql2005(80), but havnt sql 2005(90). So what ? must to do , to break this problem.
I will wait your reply
Thank you Very much....
Posted By: Jamila on Nov 6, 2008
I have the problem that this guid should fix, but in the database property option it is alredy sql 2005(90)??
Posted By: Torsten on Nov 13, 2008
Since writing this entry in February I also found this script somewhere on the web that has worked for me in every case:
EXEC sp_dbcmptlevel 'YourDatabase', '90';
go
ALTER AUTHORIZATION ON DATABASE::YourDatabase TO "sa"
go
use YourDatabase
go
EXECUTE AS USER = N'dbo' REVERT
go
Posted By: Doug Hughes on Nov 13, 2008
Weee, thanks Doug Hughes, your solution worked.
Posted By: Torsten on Nov 13, 2008
Great... your solution worked perfectly... thanks...
Posted By: Alan Wong on Jan 8, 2009
thanks...
Posted By: murat on Jan 9, 2009
Thanks, Doug. Worked like a charm! :-)
Posted By: Paul Moran on May 23, 2009