Database Diagrams / Alter Table

(Repost. Originally posted as a reply in the correct thread.)
You are correct in the assumption that the application works due to the
error checking.
I'm working with Mike on this problem and somewhere along the line, all of
the keys got dropped in the production database. We want to recreate them
in the development database and then run the scripts against the production
database to create the keys and the relationships that are required. Is
there any way to automatically compare two versions of the database and
generate a batch of the ALTER TABLE commands? Or am I looking at
writing each statement individually?
Thanks
Dave
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uGKT3TRVDHA.2192@.TK2MSFTNGP10.phx.gbl...
> Check if the FKs are in place. Use sp_help and sp_foreignkeys system SPs
to
> check if FKs are present. You can add them, if they are not in place,
using
> the ALTER TABLE command. If the application respects them, if it has input
> validation & error handling, you should have no problem.
>
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Mike" <Mike149@.yahoo.com> wrote in message
> news:312f01c35514$024662e0$a001280a@.phx.gbl...
> > I have a question concerning the diagramming tool in SQL
> > Server 2000. On the development server the keys show up
> > in the table view of the Enterprise Manager and the
> > diagramming tool works as expected. However, when we
> > imported the database into production, the keys did not
> > come forward so the diagramming tool cannot diagram
> > relationships. The interesting thing is that the
> > application works as expected, except the keys dont show
> > up in the table view.
> >
> > Question...how is this possible? How do I add the keys
> > to the design without disrupting the application?
> >
> > Thanx
>
>I am using the diff tool from http://www.adeptsql.com/
they have an 30 day trial of the software.
It does an great job on comparing Databases where the only difference is FK
not in one of them.
I delete on the FKs on one of my test DBs several times yesterday;
and then I use the diff tool to compare my tests DBs to each other and
high-light the FKs and have the Diff tool create the ALTER Table commands.
Note: I cut and paste the command into QA to run them sometimes.
FYI: It does not yet handle permissions on objects.
"Dave" <dave@.glimmernet.com> wrote in message
news:%23PLD0FDWDHA.2464@.TK2MSFTNGP09.phx.gbl...
> (Repost. Originally posted as a reply in the correct thread.)
> You are correct in the assumption that the application works due to the
> error checking.
> I'm working with Mike on this problem and somewhere along the line, all of
> the keys got dropped in the production database. We want to recreate them
> in the development database and then run the scripts against the
production
> database to create the keys and the relationships that are required. Is
> there any way to automatically compare two versions of the database and
> generate a batch of the ALTER TABLE commands? Or am I looking at
> writing each statement individually?
> Thanks
> Dave
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uGKT3TRVDHA.2192@.TK2MSFTNGP10.phx.gbl...
> > Check if the FKs are in place. Use sp_help and sp_foreignkeys system SPs
> to
> > check if FKs are present. You can add them, if they are not in place,
> using
> > the ALTER TABLE command. If the application respects them, if it has
input
> > validation & error handling, you should have no problem.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "Mike" <Mike149@.yahoo.com> wrote in message
> > news:312f01c35514$024662e0$a001280a@.phx.gbl...
> > > I have a question concerning the diagramming tool in SQL
> > > Server 2000. On the development server the keys show up
> > > in the table view of the Enterprise Manager and the
> > > diagramming tool works as expected. However, when we
> > > imported the database into production, the keys did not
> > > come forward so the diagramming tool cannot diagram
> > > relationships. The interesting thing is that the
> > > application works as expected, except the keys dont show
> > > up in the table view.
> > >
> > > Question...how is this possible? How do I add the keys
> > > to the design without disrupting the application?
> > >
> > > Thanx
> >
> >
>
>|||You could also use DB Ghost www.dbghost.com which does
handle object permissions.
>--Original Message--
>This sounds like it'll work for what I need - I'll look
into it.
>Thanks, Tim
>Dave
>
>"Tim S" <stahta01@.juno.com> wrote in message
>news:38xWa.1585$GN6.331@.fe01.atl2.webusenet.com...
>> I am using the diff tool from http://www.adeptsql.com/
>> they have an 30 day trial of the software.
>> It does an great job on comparing Databases where the
only difference is
>FK
>> not in one of them.
>> I delete on the FKs on one of my test DBs several
times yesterday;
>> and then I use the diff tool to compare my tests DBs
to each other and
>> high-light the FKs and have the Diff tool create the
ALTER Table commands.
>> Note: I cut and paste the command into QA to run them
sometimes.
>> FYI: It does not yet handle permissions on objects.
>>
>> "Dave" <dave@.glimmernet.com> wrote in message
>> news:%23PLD0FDWDHA.2464@.TK2MSFTNGP09.phx.gbl...
>> > (Repost. Originally posted as a reply in the
correct thread.)
>> >
>> > You are correct in the assumption that the
application works due to the
>> > error checking.
>> >
>> > I'm working with Mike on this problem and somewhere
along the line, all
>of
>> > the keys got dropped in the production database. We
want to recreate
>them
>> > in the development database and then run the scripts
against the
>> production
>> > database to create the keys and the relationships
that are required. Is
>> > there any way to automatically compare two versions
of the database and
>> > generate a batch of the ALTER TABLE commands? Or am
I looking at
>> > writing each statement individually?
>> >
>> > Thanks
>> >
>> > Dave
>> >
>> >
>> >
>> > "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
>in
>> > message news:uGKT3TRVDHA.2192@.TK2MSFTNGP10.phx.gbl...
>> > > Check if the FKs are in place. Use sp_help and
sp_foreignkeys system
>SPs
>> > to
>> > > check if FKs are present. You can add them, if
they are not in place,
>> > using
>> > > the ALTER TABLE command. If the application
respects them, if it has
>> input
>> > > validation & error handling, you should have no
problem.
>> > >
>> > > --
>> > > Dejan Sarka, SQL Server MVP
>> > > FAQ from Neil & others at:
http://www.sqlserverfaq.com
>> > > Please reply only to the newsgroups.
>> > > PASS - the definitive, global community
>> > > for SQL Server professionals -
http://www.sqlpass.org
>> > >
>> > > "Mike" <Mike149@.yahoo.com> wrote in message
>> > > news:312f01c35514$024662e0$a001280a@.phx.gbl...
>> > > > I have a question concerning the diagramming
tool in SQL
>> > > > Server 2000. On the development server the keys
show up
>> > > > in the table view of the Enterprise Manager and
the
>> > > > diagramming tool works as expected. However,
when we
>> > > > imported the database into production, the keys
did not
>> > > > come forward so the diagramming tool cannot
diagram
>> > > > relationships. The interesting thing is that the
>> > > > application works as expected, except the keys
dont show
>> > > > up in the table view.
>> > > >
>> > > > Question...how is this possible? How do I add
the keys
>> > > > to the design without disrupting the application?
>> > > >
>> > > > Thanx
>> > >
>> > >
>> >
>> >
>> >
>>
>
>.
>

Database Creation Help

Hi All,
I am trying to build a prototype in vb with sql server backend, I have a que
stion regarding the database , due to time constraints i am in a dilema whet
her to build a normalized database or to build a denormalized database and n
ormalize it later'
Which will be a good decision?
Thanks for any help
Sudha.If you don't have time to do it right, where are you going to find the time
to do it over? At least Normalize the biggest pieces.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"sudha" <anonymous@.discussions.microsoft.com> wrote in message
news:47AACA23-7420-4BF2-9B10-5F37F44123C5@.microsoft.com...

quote:

---

> Hi All,
> I am trying to build a prototype in vb with sql server backend, I have a

---

question regarding the database , due to time constraints i am in a dilema
whether to build a normalized database or to build a denormalized database
and normalize it later'

quote:

---

> Which will be a good decision?
>
> Thanks for any help
> Sudha.
>

---

|||I meant total Normalization ofcourse.Apllication comes into picture only aft
er approval for the project .
Thanks.|||Usual industry practice is to throw away the prototype code when you are
building it for production.
If that is what you anticipate, I guess either way is fine.
But if you want to prove your design concept and if that is the reason you
are building a prototype, then I would do it right the first way, which is
normalized way.
Keep in mind in certain hybrid systems of OLTP/OLAP, the data is
denormalized deliberately for optimum performance between OLTP and Reporting
operations.
HTH
Satish Balusa
Corillian Corp.
"sudha" <anonymous@.discussions.microsoft.com> wrote in message
news:47AACA23-7420-4BF2-9B10-5F37F44123C5@.microsoft.com...

quote:

---

> Hi All,
> I am trying to build a prototype in vb with sql server backend, I have a

---

question regarding the database , due to time constraints i am in a dilema
whether to build a normalized database or to build a denormalized database
and normalize it later'

quote:

---

> Which will be a good decision?
>
> Thanks for any help
> Sudha.
>

---

|||Thanks for the advice.
sudha

quote:

---

>--Original Message--
>Usual industry practice is to throw away the prototype

---

code when you are

quote:

---

>building it for production.
>If that is what you anticipate, I guess either way is

---

fine.

quote:

---

>But if you want to prove your design concept and if that

---

is the reason you

quote:

---

>are building a prototype, then I would do it right the

---

first way, which is

quote:

---

>normalized way.
>Keep in mind in certain hybrid systems of OLTP/OLAP, the

---

data is

quote:

---

>denormalized deliberately for optimum performance between

---

OLTP and Reporting

quote:

---

>operations.
>--
>HTH
>Satish Balusa
>Corillian Corp.
>
>"sudha" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:47AACA23-7420-4BF2-9B10-5F37F44123C5@.microsoft.com...
backend, I have a[QUOTE]
>question regarding the database , due to time constraints

---

i am in a dilema

quote:

---

>whether to build a normalized database or to build a

---

denormalized database

quote:

---

>and normalize it later'
>
>.
>

---

Database Creation Help

Hi All,
I am trying to build a prototype in vb with sql server backend, I have a question regarding the database , due to time constraints i am in a dilema whether to build a normalized database or to build a denormalized database and normalize it later'
Which will be a good decision?
Thanks for any help
Sudha.If you don't have time to do it right, where are you going to find the time
to do it over? At least Normalize the biggest pieces.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"sudha" <anonymous@.discussions.microsoft.com> wrote in message
news:47AACA23-7420-4BF2-9B10-5F37F44123C5@.microsoft.com...
> Hi All,
> I am trying to build a prototype in vb with sql server backend, I have a
question regarding the database , due to time constraints i am in a dilema
whether to build a normalized database or to build a denormalized database
and normalize it later'
> Which will be a good decision?
>
> Thanks for any help
> Sudha.
>|||I meant total Normalization ofcourse.Apllication comes into picture only after approval for the project .
Thanks.|||Usual industry practice is to throw away the prototype code when you are
building it for production.
If that is what you anticipate, I guess either way is fine.
But if you want to prove your design concept and if that is the reason you
are building a prototype, then I would do it right the first way, which is
normalized way.
Keep in mind in certain hybrid systems of OLTP/OLAP, the data is
denormalized deliberately for optimum performance between OLTP and Reporting
operations.
--
HTH
Satish Balusa
Corillian Corp.
"sudha" <anonymous@.discussions.microsoft.com> wrote in message
news:47AACA23-7420-4BF2-9B10-5F37F44123C5@.microsoft.com...
> Hi All,
> I am trying to build a prototype in vb with sql server backend, I have a
question regarding the database , due to time constraints i am in a dilema
whether to build a normalized database or to build a denormalized database
and normalize it later'
> Which will be a good decision?
>
> Thanks for any help
> Sudha.
>|||Thanks for the advice.
sudha
>--Original Message--
>Usual industry practice is to throw away the prototype
code when you are
>building it for production.
>If that is what you anticipate, I guess either way is
fine.
>But if you want to prove your design concept and if that
is the reason you
>are building a prototype, then I would do it right the
first way, which is
>normalized way.
>Keep in mind in certain hybrid systems of OLTP/OLAP, the
data is
>denormalized deliberately for optimum performance between
OLTP and Reporting
>operations.
>--
>HTH
>Satish Balusa
>Corillian Corp.
>
>"sudha" <anonymous@.discussions.microsoft.com> wrote in
message
>news:47AACA23-7420-4BF2-9B10-5F37F44123C5@.microsoft.com...
>> Hi All,
>> I am trying to build a prototype in vb with sql server
backend, I have a
>question regarding the database , due to time constraints
i am in a dilema
>whether to build a normalized database or to build a
denormalized database
>and normalize it later'
>> Which will be a good decision?
>>
>> Thanks for any help
>> Sudha.
>
>.
>

Database create error due to folder access right.

I have an application which will create a SQL database(using CREATE DATABASE) and the database files are supposed to be placed in c:\program files\myApp\data\myDatabase.mdf
However, when I try to do so, I get the following error:

System.ApplicationException: System.Data.SqlClient.SqlException: CREATE DATABASE failed. Some file names listed could not be created. Check related errors.CREATE FILE encountered operating system error 5(Access Denied) while attempting to open or create the physical file 'C:\Program Files\myApp\data\myDatabase.mdf'.

My window login already have administrator role.

The problem can be solved if I manually add full access right to my login in window exploder. However, how can I do this programmatically? Or what is the proper way to create database located under my application folder?The account that sql runs as needs to have the security privledge to access the directory or file.

thanks,
mark|||Sorry that I am using SQL authenication and provide the sa password in the connection string. How can I create database in my application folder?|||As Mark pointed out, you will need to grant write access to that directory to the SQL Server service account. The error is not related to the permissions of the account that you use to connect to SQL Server when you execute the CREATE DATABASE statement.

To find out the service account, execute Start->Run->services.msc and then look for the SQL Server service entry; you should find in it the name of the service account.

Thanks
Laurentiu|||I see. Thank you very much.
If I would like to include SQL Server Express as a pre-requirite of my application, can I grant my app folder write access right of the SQL Server service account during installation?
Thanks again.|||

Are you referring to the SQL Server Express (SSE) installation or to your application installation?
In the first case, I don't think there is a way to grant access at installation time, unless the data folder is the same as your app data folder. For the second case, you can determine the SSE service account and grant it permissions to your folder at the time you install your application.

Thanks
Laurentiu

|||Anyone have a code sample of how to determine the SSE service account programmatically?

Database create error due to folder access right.

I have an application which will create a SQL database(using CREATE DATABASE) and the database files are supposed to be placed in c:\program files\myApp\data\myDatabase.mdf
However, when I try to do so, I get the following error:

System.ApplicationException: System.Data.SqlClient.SqlException: CREATE DATABASE failed. Some file names listed could not be created. Check related errors.CREATE FILE encountered operating system error 5(Access Denied) while attempting to open or create the physical file 'C:\Program Files\myApp\data\myDatabase.mdf'.

My window login already have administrator role.

The problem can be solved if I manually add full access right to my login in window exploder. However, how can I do this programmatically? Or what is the proper way to create database located under my application folder?The account that sql runs as needs to have the security privledge to access the directory or file.

thanks,
mark|||Sorry that I am using SQL authenication and provide the sa password in the connection string. How can I create database in my application folder?|||As Mark pointed out, you will need to grant write access to that directory to the SQL Server service account. The error is not related to the permissions of the account that you use to connect to SQL Server when you execute the CREATE DATABASE statement.

To find out the service account, execute Start->Run->services.msc and then look for the SQL Server service entry; you should find in it the name of the service account.

Thanks
Laurentiu|||I see. Thank you very much.
If I would like to include SQL Server Express as a pre-requirite of my application, can I grant my app folder write access right of the SQL Server service account during installation?
Thanks again.|||

Are you referring to the SQL Server Express (SSE) installation or to your application installation?
In the first case, I don't think there is a way to grant access at installation time, unless the data folder is the same as your app data folder. For the second case, you can determine the SSE service account and grant it permissions to your folder at the time you install your application.

Thanks
Laurentiu

|||Anyone have a code sample of how to determine the SSE service account programmatically?