Saturday, November 19, 2005

Usage of Diagrams with SQL Server 2005 and compatibility level of: SQL Server 2000 (80)

Diagrams... yes I like them, to get a clear Idea of a DB I'm usually creating Diagrams. I got a DB from a friend which is working which works with SQL Server 2000 [80]. After I created an empty DB and restored the DB I tried to create an Diagram of its main tables. Every time I wanted to create a Diagram the following message appears:

Database diagram support objects cannot be installed because this database does not not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

at least to me that sounds interesting.... So, naturally I searched for the following store procedure:

USE MyFriendsDatabaseNameIHaveCreated
EXEC sp_changedbowner 'sa'

After I have executed this store procedure I clicked on my new DB and .... again the same message.

I have checked the configuration of this Database I have imported which has a compatibility level of: SQL Server 2000 (80). After playing around with all the different properties I have found out that the Sql Server Management Studio is not able to create Diagrams as long the database runs with the compatibility level of: SQL Server 2000 (80). once you change that to the compatibility level of: SQL Server 2005 (90) you can create as much as you want diagrams.

The Database Diagram tool in SQL Server 2005 uses supporting objects that require SQL Server 2005 functionality. So the readme txt file say's in the documentation of SQL Server 2005. Here is an extract from this text:

To install database diagram support in SQL Server Management Studio, databases must be in SQL Server 2005 database compatibility level. Database
compatibility level can be reset after diagram support is installed.

I just should read the manual ;-)

No comments:

Shared Cache - .Net Caching made easy

All information about Shared Cache is available here: Its free and easy to use, we provide all sources at codeplex.

Facebook Badge