Database diagrams

Can a Database Diagram be imported or exported so that the schema can be
shown to someone that does not have Enterprise Manager installed on their
system?
If so what tools can do this?
No; you can do a screen capture or print to a PDF, perhaps.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Jim Abel" <JimAbel@.discussions.microsoft.com> wrote in message
news:65C71846-71F2-4875-B0C8-79C18104F5CF@.microsoft.com...
> Can a Database Diagram be imported or exported so that the schema can be
> shown to someone that does not have Enterprise Manager installed on their
> system?
> If so what tools can do this?

Database Diagrams

I have a SQL 2000 database that I imported into SQL Server 2005 by restoring
the database from a backup. I carefully re-did the Users to make sure there
were corresponding Logins.
I want to create a Database Diagram using the Microsoft SQL Server
Management Studio. When I right click on the Database Diagrams node within
the correct database I get the following error.
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTE AUTHORIZATION statement to set the
database owner to a valid login, then add the database diagram support
objects.
Now I did both of these suggestions and then looked at the owner of the
database, who in fact is a valid Login with sysadmin privileges.
I'm not sure where to go from here since everything seems to be done
correctly.
Any suggestions?
Michael Hockstein
This is covered in section 4.8 in the Readme file that comes with the
product .
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. To
create database diagrams, change the database compatibility level to 2005,
install database diagram support, and then return the database to the
desired database compatibility level. For more information, search for
"sp_dbcmptlevel" in SQL Server Books Online.
So, run the following statement and try the diagram again.
EXEC sp_dbcmptlevel database_name, 90
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"michael" <howlinghound@.nospam.nospam> wrote in message
news:EB3D155A-EC71-4854-BAC6-7B14BE038586@.microsoft.com...
>I have a SQL 2000 database that I imported into SQL Server 2005 by
>restoring
> the database from a backup. I carefully re-did the Users to make sure
> there
> were corresponding Logins.
> I want to create a Database Diagram using the Microsoft SQL Server
> Management Studio. When I right click on the Database Diagrams node
> within
> the correct database I get the following error.
> Database diagram support objects cannot be installed because this database
> does not have a valid owner. To continue, first use the Files page of the
> Database Properties dialog box or the ALTE AUTHORIZATION statement to set
> the
> database owner to a valid login, then add the database diagram support
> objects.
> Now I did both of these suggestions and then looked at the owner of the
> database, who in fact is a valid Login with sysadmin privileges.
> I'm not sure where to go from here since everything seems to be done
> correctly.
> Any suggestions?
> --
> Michael Hockstein
|||Fantastic. Worked like a charm. Could you also give me a clue as to where to
find the Readme file? I looked in the SQL Server directory and subs but
couldn't locate it. This would be a good one to keep on file.
Michael Hockstein
"Gail Erickson [MS]" wrote:

> This is covered in section 4.8 in the Readme file that comes with the
> product .
> 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. To
> create database diagrams, change the database compatibility level to 2005,
> install database diagram support, and then return the database to the
> desired database compatibility level. For more information, search for
> "sp_dbcmptlevel" in SQL Server Books Online.
> So, run the following statement and try the diagram again.
> EXEC sp_dbcmptlevel database_name, 90
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:EB3D155A-EC71-4854-BAC6-7B14BE038586@.microsoft.com...
>
>
|||michael (howlinghound@.nospam.nospam) writes:
> Fantastic. Worked like a charm. Could you also give me a clue as to
> where to find the Readme file? I looked in the SQL Server directory and
> subs but couldn't locate it. This would be a good one to keep on file.
I happened to have the SQL 2005 server DVD in my player, and I found a
Readme in G:\SQL Server x86\Servers\ReadmeSQL2005.htm. There seem to be
one in every top-level setup directory.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||In addition to Erland's response, you can also find the readme here:
http://support.microsoft.com/default...b;en-us;910228
Also, the April update to Books Online will contain this information in the
various topics on database diagrams.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"michael" <howlinghound@.nospam.nospam> wrote in message
news:4885DE6E-B80F-4367-9B2E-D772F090CD7B@.microsoft.com...[vbcol=seagreen]
> Fantastic. Worked like a charm. Could you also give me a clue as to where
> to
> find the Readme file? I looked in the SQL Server directory and subs but
> couldn't locate it. This would be a good one to keep on file.
>
> --
> Michael Hockstein
>
> "Gail Erickson [MS]" wrote:
|||OK! Thanks for the tip.
Michael Hockstein
"Erland Sommarskog" wrote:

> michael (howlinghound@.nospam.nospam) writes:
> I happened to have the SQL 2005 server DVD in my player, and I found a
> Readme in G:\SQL Server x86\Servers\ReadmeSQL2005.htm. There seem to be
> one in every top-level setup directory.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
|||Thanks Gail!
Michael Hockstein
"Gail Erickson [MS]" wrote:

> In addition to Erland's response, you can also find the readme here:
> http://support.microsoft.com/default...b;en-us;910228
> Also, the April update to Books Online will contain this information in the
> various topics on database diagrams.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:4885DE6E-B80F-4367-9B2E-D772F090CD7B@.microsoft.com...
>
>

Database diagrams

Can a Database Diagram be imported or exported so that the schema can be
shown to someone that does not have Enterprise Manager installed on their
system?
If so what tools can do this?No; you can do a screen capture or print to a PDF, perhaps.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jim Abel" <JimAbel@.discussions.microsoft.com> wrote in message
news:65C71846-71F2-4875-B0C8-79C18104F5CF@.microsoft.com...
> Can a Database Diagram be imported or exported so that the schema can be
> shown to someone that does not have Enterprise Manager installed on their
> system?
> If so what tools can do this?

Database diagrams

Can a Database Diagram be imported or exported so that the schema can be
shown to someone that does not have Enterprise Manager installed on their
system?
If so what tools can do this?No; you can do a screen capture or print to a PDF, perhaps.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jim Abel" <JimAbel@.discussions.microsoft.com> wrote in message
news:65C71846-71F2-4875-B0C8-79C18104F5CF@.microsoft.com...
> Can a Database Diagram be imported or exported so that the schema can be
> shown to someone that does not have Enterprise Manager installed on their
> system?
> If so what tools can do this?

database detach/attach

An original database (eg A.mdf) is imported/exported to a different server as named B.mdf. The detach of A.mdf was unitentionally not done. It seems that both the database were updated.

A.mdf - 123000KB 25/12/2003
A_log.ldf - 14000KB 25/12/2003
B.mdf - 67000KB 25/12/2003
B_log.ldf - 1024KB 25/12/2003

In this case, which mdf & ldf should be the correct database?
If I I remove A.mdf database by "detach", will B.mdf database work?

SQL server 2000 and SP3 installed.

Assistance is appreciatedyou are exporting a database file and getting different sizes .. seems strange

sp_helpdb 'database_name'

on both servers

Copy and paste results over here

That might help|||The original database (eg A.mdf) is export/imported into a different server and the database is named as B.mdf. The 'detach' command from of the original database (A.mdf) was unintentionally
not done. As I have unintentionally did not do a 'detach' of the old database, both the database seems to be updated (as indicated from the date stamp) AFTER I use the application (VB6 business application) a day or two.

Once an import/export is done, is the detach of the old database compulsory?. In SQL Server Enterprise Manager->(Select say, A database)->All Task-> Detach database

A.mdf - 123,000KB, 2/1/2004 1142am
A_log.ldf - 10,000 KB, 2/1/2004 0133pm
B.mdf - 67,000KB, 2/1/2004 0902am
B_log.ldf - 1024KB, 2/1/2004 0902am

Exporting of the database is ok before the I started using the application.

How can I correct this? Advise is appreciated.

Regards,
Brian

Originally posted by Enigma
you are exporting a database file and getting different sizes .. seems strange

sp_helpdb 'database_name'

on both servers

Copy and paste results over here

That might help|||Are both databases supposed to be active - please describe more as to why you have 2 ? Where are you retrieving the database sizes ? Use sp_spaceused and post the results. Is the real question, what has changed between the 2 databases and how to find those records ?|||Originally posted by rnealejr
Are both databases supposed to be active - please describe more as to why you have 2 ? Where are you retrieving the database sizes ? Use sp_spaceused and post the results. Is the real question, what has changed between the 2 databases and how to find those records ?

the A.mdf and its log is the active database. 2 databases are active as 1 is live/production environment and the other is test/QA environment. the database sizes is seen from the window explorer.|||What needs to be corrected ? You keep mentioning detaching the A database - but why ? Is A production or test ? You need to run the stored procedure sp_spaceused to determine actual space used. Are you concerned that there might be activity on both databases - and you are not sure why ? Are you exporting the data from the production to the test periodically ? Since your log file for B does not appear to have grown, it appears that either minimal or no user activity is occuring on the B database (unless you are backing up the database/transaction log for B).|||Originally posted by rnealejr
What needs to be corrected ? You keep mentioning detaching the A database - but why ? Is A production or test ? You need to run the stored procedure sp_spaceused to determine actual space used. Are you concerned that there might be activity on both databases - and you are not sure why ? Are you exporting the data from the production to the test periodically ? Since your log file for B does not appear to have grown, it appears that either minimal or no user activity is occuring on the B database (unless you are backing up the database/transaction log for B).

if I were to remove (detach) the test/QA data (denotes by B.mdf and its log), will this cause any problem with production (denotes by A.mdf and its log) database?. I noted that the date/time has been updated on the same day even though my ODBC (live application) uses production database which is A.mdf.

What happen if a detach is not done after import/export from production to test/QA environment?. Do the production database get updated (by right it should since ODBC points to production) along with the test database?. I am concern that activities may be updated in the test environment - yes, I am not sure and I would like some feedback.|||There should be no connection between your A database and your B database (unless you have triggers/replication between the 2) - other than the fact that you exported the data from A to B. What method did you use to export the data from A to B ? Several activities can cause the date/time to change on B, but since the log is only 1 meg (the minimum) I would suspect that nothing has really changed on B. But if you are really concerned - dump the test database and copy the database from prod back to test.|||Originally posted by rnealejr
There should be no connection between your A database and your B database (unless you have triggers/replication between the 2) - other than the fact that you exported the data from A to B. What method did you use to export the data from A to B ? Several activities can cause the date/time to change on B, but since the log is only 1 meg (the minimum) I would suspect that nothing has really changed on B. But if you are really concerned - dump the test database and copy the database from prod back to test.

I agree with you. All I did was to simply import/export. no DTS used.
The strange thing I noted was that after the import/export from production to test (and no "detach" was done on test database), I ran the application and did the update. I found was that the test database time/date was updated AND the data went into production and the mdf and log of production did not change (seen from explorer).

Thanks for your feedback.|||It was probably just coincidence ... or a poltergeist (oooohhhhhh - supposed to be a spooky sound)