Creating a script file from a diagram

Hi there,

I have created a diagram on my development SQL2005 database and want to transfer this diagram to my live environment. How do I go about creating a script to do this?

thx

Dawid

There is no way to script out diagrams in SQL Server 2005. The binary data in the diagram is tied to your SID (a unique identifier for security principals) when you created the diagram, so I can't even give you foolproof instructions on how to hack the tables to copy the diagram data.

I'm afraid you'll have to recreate your diagram on your live system. If this is a feature you'd like to see in the next version of SQL Server, please file a suggestion on http://connect.microsoft.com/SqlServer. Customer feedback is a very important criterion when we choose which features will make it into the next release.

Thanks,

Steve

|||

I highly recommend using Visio (or one of the other robust diagramming tools) to create your database diagrams.

Printouts are easier. Documentation is easier. Scripts are easy. Changes to the diagrams can be saved WITHOUT changing your database. (Great for development environments.) Multiple step Un-Do/Re-Do!!

Creating a relationship

I am very new to SQL Server and to S.S. 2005. I am trying to create a relationship using a database diagram. I keep getting this error:

'tblCASEMAST' table saved successfully
'tblCASECHRG' table
- Unable to create relationship 'FK_tblCASECHRG_tblCASEMAST'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblCASECHRG_tblCASEMAST". The conflict occurred in database "Criminal Records", table "dbo.tblCASEMAST", column 'CASENO'.

In tblCASEMAST, CASENO is nvarchar, length of 12 and a primary key

In tblCASECHRG, the column I am trying to link to is also nvarchar, length of 12

Any help is appreciated.

I found the solution. I had to delete the data, create the relationship and then import the data again.

Creating a diagram with existing tables

I'd like to create a diagram for my existing tables without wiping them out
in the process. I rightclick & select New Diagram, place the various
tables, create relationships, then save. I get a dialog box asking if I
want to create these tables. No! They have data in them. Unfortunately I
don't see a way to save the relationships and the diagram without blowing
everything away.
Jeremy
The diagram utility operates directly on the underlying tables so if you
only want to add the relationships for documentation or viewing purposes and
not have them permanently saved to the database, then you will need to use
another tool.
--Brian
(Please reply to the newsgroups only.)
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>
|||Brian, thanks. The diagram tool is asking about saving tables, not the
relationships. I can understanding the need to save the relationship, but
don't see why it wants to save my already-existing tables and potentially
blow away the data.
Jeremy
"Brian Lawton" <brian.k.lawton@.redtailcreek.com> wrote in message
news:OW%23thLEnFHA.420@.TK2MSFTNGP09.phx.gbl...
> The diagram utility operates directly on the underlying tables so if you
> only want to add the relationships for documentation or viewing purposes
> and not have them permanently saved to the database, then you will need to
> use another tool.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "JeremyGrand" <jeremy@.ninprodata.com> wrote in message
> news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
>
|||I agree that the messaging in the dialog could certainly be improved however
no matter what changes you make, it will always say that it is saving the
tables. Unless you changed the table structure itself, it should only apply
the relationship constraints via ALTER TABLE syntax and not recreate your
tables. If you want to see exactly how your changes are going to be
applied, there is an option to "Save Change Script" which will generate a
SQL script with the code it plans to apply.
--Brian
(Please reply to the newsgroups only.)
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:uUygVYEnFHA.4064@.TK2MSFTNGP10.phx.gbl...
> Brian, thanks. The diagram tool is asking about saving tables, not the
> relationships. I can understanding the need to save the relationship, but
> don't see why it wants to save my already-existing tables and potentially
> blow away the data.
> Jeremy
> "Brian Lawton" <brian.k.lawton@.redtailcreek.com> wrote in message
> news:OW%23thLEnFHA.420@.TK2MSFTNGP09.phx.gbl...
>
|||By the way, DataAnalyst saves relationship discovered in its own access
file, so it won't touch your original table at all.
Download it at http://www.agileinfollc.com
Eric
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>
|||Hi Jeremy
You might want to look at this product
http://www.ag-software.com/?tabid=17
It is a diagram tool and database compare tool. All details are saved
outside of SQL Server
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>

creating a diagram in sql server express

hi..

i have a pc that is on a domain with domain user rights and i installed a sql server express 2005..

when trying to create a new database am encountering an error:

create database permission denied in database 'master' (microsoft sql server error:262)

could anyone help how to go about this..am new to sql express.

thanks

So it seems that your are not logged in with a Windows account which has sufficient priviliedge "Create Database" to perform this action. If you have an account that is local administrator, try to login with this one and execute your DDL Actions.

HTH, Jens Suessmeyer.|||

what do you mean by an account that is local admin? an account outside the domain?

what if i still want to work within the domain? what probably the best sufficient priviledge that i could use? domain admin account?

pls. clarify

thanks

|||By default the local admin group is in in the group of the server administrators, so the domain admin are usally also members of the local admin group and therefore sa′s. YOu just have to login with a user with more permissions than the current one you are trying with.

HTH, Jens Suessmeyer.|||

btw, am using windows authentication mode and not sql authentication mode.. will that be ok...and also a check with my privileges the user am currently using is also a member of administrator built in group.. but not domain admin.

thanks

|||what am trying to achieve here is that.. clients pc can use sql server express without giving them full priveleges to access others pc's folders except for those folders that are shared..|||You could create a group on the local machine and add your user to that group. Then in SQL Server give that group the permissions you want.|||

am having this error when trying to create a diagram. (you are not logged on as database owner or system administrator. you might not be able to save changes to tables that you do not own in sql server express...certain edits require create table permission)

i would greatly appreaciate helping me how to give permissions.. if possible the most step by step guide pls.)

thanks

|||

Hi

THIS WORKED!!!!

Go to SQL Server manager >> Security >> Logins and find an account named "NT AUTHORITY\NETWORK SERVICE"

Open this and in the SERVER ROLE tab, give it DBCREATOR permissioin. Thats it. now you will be able to create the database.

Reason: When SharePoint administration website connects SQL database, it uses the account "NT AUTHORITY\NETWORK SERVICE". So this account must have DBCREATEOR permission

hope this helps

Regards,

Ash

|||

It took me a while to find the "SQL Server manager" mentioned in the post above this post.

Eventually I found out that Ash31 meant the "Microsoft SQL Server Management Studio". =)

Hopefully this will help people like me who can't find the SQL Server manager.

Creating a diagram automatically through Enterprise Manager

Hi all,
I'm wondering, is there any way to have SQL Server automatically generate
a diagram via Enterprise Manager?
The situation is, I have created a database by executing a big as script
of SQL. The script created the tables and views and added the primary key
constraints etc. However there isnt a diagram available to help me understan
d
the schema a bit easier.
Is there some way to have Enterprise Manager interpret the schema and the
constraints and reverse engineer a pretty diagram for me?
Thanks to anyone who can advise
Kindest Regards
tce
Kindest Regards
tceIsn't it as easy as creating a new Diagram and adding all the tables?
Not to belittle the task, but the wizard is pretty good? It isn't the
automated approach but works none the less.
Clint Hill
H3O Software
http://www.h3osoftware.com
thechaosengine wrote:
> Hi all,
> I'm wondering, is there any way to have SQL Server automatically
> generate a diagram via Enterprise Manager?
> The situation is, I have created a database by executing a big as script
> of SQL. The script created the tables and views and added the primary
> key constraints etc. However there isnt a diagram available to help me
> understand the schema a bit easier.
> Is there some way to have Enterprise Manager interpret the schema and
> the constraints and reverse engineer a pretty diagram for me?
> Thanks to anyone who can advise
> Kindest Regards
> tce
> Kindest Regards
> tce
>|||Hi Clint
Thanks for your reply. You are quite right, the wizard does generate the
diagram! I must confess to being a bit stupid here. I normally use Access
connected to SQL Server to develop the database and access doesn't have quit
e
the same functionality in this regard. I actually assumed that the diagramin
g
features were identical. They certainly seem very similar.
Thanks for your help!
Kindest Regards
tce
[vbcol=seagreen]
> Isn't it as easy as creating a new Diagram and adding all the tables?
> Not to belittle the task, but the wizard is pretty good? It isn't the
> automated approach but works none the less.
> Clint Hill
> H3O Software
> http://www.h3osoftware.com
> thechaosengine wrote:
>

Creating a diagram automatically through Enterprise Manager

Hi all,
I'm wondering, is there any way to have SQL Server automatically generate
a diagram via Enterprise Manager?
The situation is, I have created a database by executing a big as script
of SQL. The script created the tables and views and added the primary key
constraints etc. However there isnt a diagram available to help me understand
the schema a bit easier.
Is there some way to have Enterprise Manager interpret the schema and the
constraints and reverse engineer a pretty diagram for me?
Thanks to anyone who can advise
Kindest Regards
tce
Kindest Regards
tce
Isn't it as easy as creating a new Diagram and adding all the tables?
Not to belittle the task, but the wizard is pretty good? It isn't the
automated approach but works none the less.
Clint Hill
H3O Software
http://www.h3osoftware.com
thechaosengine wrote:
> Hi all,
> I'm wondering, is there any way to have SQL Server automatically
> generate a diagram via Enterprise Manager?
> The situation is, I have created a database by executing a big as script
> of SQL. The script created the tables and views and added the primary
> key constraints etc. However there isnt a diagram available to help me
> understand the schema a bit easier.
> Is there some way to have Enterprise Manager interpret the schema and
> the constraints and reverse engineer a pretty diagram for me?
> Thanks to anyone who can advise
> Kindest Regards
> tce
> Kindest Regards
> tce
>
|||Hi Clint
Thanks for your reply. You are quite right, the wizard does generate the
diagram! I must confess to being a bit stupid here. I normally use Access
connected to SQL Server to develop the database and access doesn't have quite
the same functionality in this regard. I actually assumed that the diagraming
features were identical. They certainly seem very similar.
Thanks for your help!
Kindest Regards
tce
[vbcol=seagreen]
> Isn't it as easy as creating a new Diagram and adding all the tables?
> Not to belittle the task, but the wizard is pretty good? It isn't the
> automated approach but works none the less.
> Clint Hill
> H3O Software
> http://www.h3osoftware.com
> thechaosengine wrote:

Creating a diagram automatically through Enterprise Manager

Hi all,
I'm wondering, is there any way to have SQL Server automatically generate
a diagram via Enterprise Manager?
The situation is, I have created a database by executing a big as script
of SQL. The script created the tables and views and added the primary key
constraints etc. However there isnt a diagram available to help me understand
the schema a bit easier.
Is there some way to have Enterprise Manager interpret the schema and the
constraints and reverse engineer a pretty diagram for me?
Thanks to anyone who can advise
Kindest Regards
tce
Kindest Regards
tceIsn't it as easy as creating a new Diagram and adding all the tables?
Not to belittle the task, but the wizard is pretty good? It isn't the
automated approach but works none the less.
Clint Hill
H3O Software
http://www.h3osoftware.com
thechaosengine wrote:
> Hi all,
> I'm wondering, is there any way to have SQL Server automatically
> generate a diagram via Enterprise Manager?
> The situation is, I have created a database by executing a big as script
> of SQL. The script created the tables and views and added the primary
> key constraints etc. However there isnt a diagram available to help me
> understand the schema a bit easier.
> Is there some way to have Enterprise Manager interpret the schema and
> the constraints and reverse engineer a pretty diagram for me?
> Thanks to anyone who can advise
> Kindest Regards
> tce
> Kindest Regards
> tce
>