Create table script without drop

Hello all!
I'm currently working on a project where we have several customers with
the same application. The database is constantly being changed and it's
hard to keep track of all the changes from all the versions in the
customers' systems.
Usually I create the changes script every time I alter any of the tables
but there is always a risk of loosing them. I wonder if there is anyway
of creating a script that updates all the tables instead of dropping and
creating them all, so our customers won't loose the database records.
Thanks in advance,
Hugo MadureiraHugo,
You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
TABLE.
eg.
Alter Table MyTable
Add MyColumn varchar(10)
HTH
Barry|||You can get rid of a lot of headaches by using SQL Compare.
www.red-gate.com
"Hugo Madureira" <hugomadureira@.hotmail.com> wrote in message
news:%232216JpJGHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hello all!
> I'm currently working on a project where we have several customers with
> the same application. The database is constantly being changed and it's
> hard to keep track of all the changes from all the versions in the
> customers' systems.
> Usually I create the changes script every time I alter any of the tables
> but there is always a risk of loosing them. I wonder if there is anyway of
> creating a script that updates all the tables instead of dropping and
> creating them all, so our customers won't loose the database records.
>
> Thanks in advance,
> Hugo Madureira|||Of course, this gets more complex than just adding columns. Such as
adding/removing columns with check constraints, foreign key constraints,
primary key constraints, unique constraints, computed columns, changing
datatypes/scale/precision, etc. Not all table changes are adding columns.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1138731694.928227.324210@.z14g2000cwz.googlegroups.com...
> Hugo,
> You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
> TABLE.
> eg.
> Alter Table MyTable
> Add MyColumn varchar(10)
>
> HTH
> Barry
>|||Ahh now I understand what he *actually* wanted... oops!
Barry|||I was looking for a possible way of doing that with Enterprise Manager
manager, in a way that it could be done automatically.
When I use Enterprise Manager to create a table script, it drops the
table and re-creates it. That causes data loss in the database.
If there is no way of doing that, is it possible to easily edit the
script generated by Enterprise Manager to do that?
Barry wrote:
> Hugo,
> You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
> TABLE.
> eg.
> Alter Table MyTable
> Add MyColumn varchar(10)
>
> HTH
> Barry
>

CREATE TABLE rights problems

I cannot fix the rights depending on customers server...

when i create a table dynamicly with ASP.NET for MS SQL 2000, should I use :

CREATE TABLE [dbo] .[Component]

or

CREATE TABLE [Component]

-------

[id_Component] [int] IDENTITY (1, 1) NOT NULL ,

or

[id_Component] [int] IDENTITY (1, 1) COLLATE French_CI_AS NOT NULL ,

-------

shall I add COLLATE French_CI_AS to all columns ?

thank youIf you don't explicitly specify the collation, the system will use the database collation as the default.

It's a best practice to be explicit for DDL/DML. So, [dbo] and [COLLATE] should be specified.|||thank you !

Create table as I need it, how to reference in query?

I'll try to use the customer/orders example for my situation.
Let's say I have 10 customers and each customer has n order records. Most
of the time I will just accept the default order of the order records. In
other words, when I SELECT a customers orders I get them back in the order
they are in the table.
In some cases (remember, I'm not really dealing with customers/orders) I
want to explicitly re-order a customer's order records. Not by any
particular field. There is no logical pattern.
I was thinking that I could maintain a Customer_Orders_Order table. It
would look something like this:
[ CustomerID (FK to the customer tbl) ] [ OrderIndex ] [ OrderID (FK to
Order tbl) ]
the values might look like:
1 1 2
1 2 5
1 3 8
1 4 9
2 1 2
2 2 4
2 3 3
2 4 1
2 5 5
I would use this table to display the orders in the desired order in the UI.
OrderIndex would be the index in a ListView or DataGrid, etc.
My question is, would you store all the information in a single table or
would you create a table per customer to store this information and only if
the Customer's orders have actually been reordered from their default
presentation order (row order in the table)?
If your answer is create a table for each customer, how do you work with a
dynamically created table in a sproc or query? If my client app detects
that a user dragged an order above another order thus changing the
presentation order, I would create a table with a unique name, maybe
Tbl<customerName>_OrderPresentationOrdering. Then I could insert the
ordering information. Now, later when I want to display this customer's
orders and want to check if there is explicit presentation ordering that I
should use, how do I query the table I just created? Assuming I know the
naming convention used, can you concatenate a string in a sproc to build a
table name to be used in a Query?
Man, I hope this makes sense to someone, it's hard to explain.. :)
Thanks for any help,
SteveI was just thinking about this and realized that updates and inserts would
be involved due to the quantity of item in the update/insert. This got me
thinking... If I store this data per customer, inside the customer record,
then I don't need to have anything other than an array of OrderIDs.
So I can just have a delimited string stored in a field called
"OrderOrdering" (stupid name, my actual name would be "ProtocolOrdering" but
sticking with the example....)
something like:
4;2;7;1;3;5
Insert and updates are easy (fast) and there really is no need to normalize
data like this, is there? I won't use it for searches or anything.
If anyone has a compelling reason why I shouldn't do this, please share,
please, please.
"Steve" <sss@.sss.com> wrote in message
news:uThe317QGHA.6084@.TK2MSFTNGP10.phx.gbl...
> I'll try to use the customer/orders example for my situation.
> Let's say I have 10 customers and each customer has n order records. Most
> of the time I will just accept the default order of the order records. In
> other words, when I SELECT a customers orders I get them back in the order
> they are in the table.
> In some cases (remember, I'm not really dealing with customers/orders) I
> want to explicitly re-order a customer's order records. Not by any
> particular field. There is no logical pattern.
> I was thinking that I could maintain a Customer_Orders_Order table. It
> would look something like this:
> [ CustomerID (FK to the customer tbl) ] [ OrderIndex ] [ OrderID (FK
> to Order tbl) ]
>
> the values might look like:
> 1 1 2
> 1 2 5
> 1 3 8
> 1 4 9
> 2 1 2
> 2 2 4
> 2 3 3
> 2 4 1
> 2 5 5
> I would use this table to display the orders in the desired order in the
> UI. OrderIndex would be the index in a ListView or DataGrid, etc.
> My question is, would you store all the information in a single table or
> would you create a table per customer to store this information and only
> if the Customer's orders have actually been reordered from their default
> presentation order (row order in the table)?
> If your answer is create a table for each customer, how do you work with a
> dynamically created table in a sproc or query? If my client app detects
> that a user dragged an order above another order thus changing the
> presentation order, I would create a table with a unique name, maybe
> Tbl<customerName>_OrderPresentationOrdering. Then I could insert the
> ordering information. Now, later when I want to display this customer's
> orders and want to check if there is explicit presentation ordering that I
> should use, how do I query the table I just created? Assuming I know the
> naming convention used, can you concatenate a string in a sproc to build a
> table name to be used in a Query?
> Man, I hope this makes sense to someone, it's hard to explain.. :)
> Thanks for any help,
> Steve
>|||I have a feeling you're about to feel the wrath of JC.
But to answer your question, you would create a single table for this.
I see what you mean by your sorting information. In that case, I
consider that type of data to not have anything to do with the database
-- while it's human readable and client readable, I consider the data
to be arbitrary to anything accessing it within the database. It might
as well be random binary data for all your database is concerned. Now,
the question is, does this data belong in your database? You're
breaking encapsulation by storing this in your database if it's not
specific to the data. I make two exceptions to putting
application-specific data within a database. The first is when the
database is used by one and only one application, and will be used by
one and only one application for the purpose of storing data and logic
specific to that application. The second is when the performance or
implementation advantages of storing such application-specific logic
and data are exceptionally significant.
-Alan|||Steve wrote:
> I was just thinking about this and realized that updates and inserts would
> be involved due to the quantity of item in the update/insert. This got me
> thinking... If I store this data per customer, inside the customer record
,
> then I don't need to have anything other than an array of OrderIDs.
> So I can just have a delimited string stored in a field called
> "OrderOrdering" (stupid name, my actual name would be "ProtocolOrdering" b
ut
> sticking with the example....)
> something like:
> 4;2;7;1;3;5
> Insert and updates are easy (fast) and there really is no need to normaliz
e
> data like this, is there? I won't use it for searches or anything.
> If anyone has a compelling reason why I shouldn't do this, please share,
> please, please.
>
What reason would you have for NOT normalizing in this case?
You seem to be making something very simple into something very
complex. No question in my mind: One table in Normal Form. The keys
would be I assume (customer, order_index) and (customer, order_id).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I've upset JC?
(who is JC? Like Jesus Christ JC or is there a NG regular named JC?)
Yes, the data does belong to the database. I'll tell you why; given my
example, it sounds like an app preference or user preference, but this
ordering is actually used to generate source code. If a user logs in at
location A and initiates a build without knowing the ordering of the
records, that would result in invalid firmware and an irritated customer.
This is a feature I should have added from the get go, but I overlooked it.
So moving forward and agreeing (I assume we agree?) that the data does
belong in the DB, is this a valid case for a delimited string?
If not, make your case. I want to learn the right way.
Thanks,
Steve
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141943305.488882.121840@.u72g2000cwu.googlegroups.com...
>I have a feeling you're about to feel the wrath of JC.
> But to answer your question, you would create a single table for this.
> I see what you mean by your sorting information. In that case, I
> consider that type of data to not have anything to do with the database
> -- while it's human readable and client readable, I consider the data
> to be arbitrary to anything accessing it within the database. It might
> as well be random binary data for all your database is concerned. Now,
> the question is, does this data belong in your database? You're
> breaking encapsulation by storing this in your database if it's not
> specific to the data. I make two exceptions to putting
> application-specific data within a database. The first is when the
> database is used by one and only one application, and will be used by
> one and only one application for the purpose of storing data and logic
> specific to that application. The second is when the performance or
> implementation advantages of storing such application-specific logic
> and data are exceptionally significant.
> -Alan
>|||>> So I can just have a delimited string stored in a field [sic] called"OrderOrde
ring" (stupid name, my actual name would be "ProtocolOrdering" but sticking
with the example....) something like:4;2;7;1;3;5 <<
Once more, you missed the most basic concepts again! Look up "First
Normal Form" (1NF), and learn why a column is not a field, a row is not
a record and a table is not a file. You might want to read at least
one book on RDBMS before you code in SQL.|||>I've upset JC?
>(who is JC? Like Jesus Christ JC or is there a NG regular named JC?)
I think you've probably figured this out by now.
So, you've decided to keep this in your database. Be aware of the
possible future implications of this. If you store multiple values a
single row, those values will be utterly useless in your database and
you'll be forced to do your sorting on the client. Next, if you dump
your front-end and it's rewritten, you'll have this artifact in your
database that those inheriting your work won't know what to do with
(or, given enough time, you won't either). In this situation, where
you've decided to store application-specific data in your database, at
least make it friendly to whoever may be administering thing. Store
your application-specific data in an application-specific location --
either a "helper" database that is specific to your application, or in
tables that are clearly indicated as being specific to your application
(e.g. App_MyApplication_InvoiceSorting) so that it's known they can be
dropped without implications once your application is no longer used.
-Alan|||>> 've upset JC? (who is JC? Like Jesus Christ JC or is there a NG regular
named JC?) <<
This si why I go by "--CELKO--" in the Newsgroup; peopel keep getting
us all the time.
hout knowing the ordering of the records [sic], that would result in invalid firm
ware and an irritated customer. <<
So the ordering has logical meanng in the data model!
So moving forward and agreeing (I assume we agree?) that the data does
belong in the DB, is this a valid case for a delimited string? <<
NO, it is a valid reason for a sequencing column in First Normal Form.
Since you will not post any DDL or even helpful narrative with
meaningful names, here is a guess:
CREATE TABLE FirmwareTemplates
(customer_id INTEGER NOT NULL
REFERENCES Custromers (customer_id),
template_seq INTEGER NOT NULL,
template_txt VARCHAR (255) NOT NULL,
PRIMARY KEY (customer_id, template_seq ));|||LOL.. let the NG lashings begin!
My entire database is application specific to a single app. It's entire
reason for existence is my application. It would seem in that case that ALL
my data is application specific. Is that bad? I'm getting the distinct
feeling that is somehow a bad thing?
Either way, yes, I am doing the sorting on the client. This is intentional.
My application is a high level firmware editor for our sales people. We
sell a product that can be customized (a bit) per customer. However, ALL
device's firmware must share some data that is constant.
One of the things that can be customized is the ordering of menu items in a
UI menu. All devices have the same menu items, but some customers want them
in a different order. I have approached this by maintaing a base shared
collection of menu items that all customers use. When they want to reorder
the items I store "meta data" (I'm not even sure if I understand that term,
seems like "settings" to me, but whatever) in their record.
Point is, I don't return a separate set of ordered menu item records for
each customer, I get the menu items for all customers, then the ordering
data for each customer, then on the client each customer has a reference to
the shared items and I order them the way they want in the UI.
Hope that makes a bit of sense.
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141944627.875280.107100@.i40g2000cwc.googlegroups.com...
> I think you've probably figured this out by now.
> So, you've decided to keep this in your database. Be aware of the
> possible future implications of this. If you store multiple values a
> single row, those values will be utterly useless in your database and
> you'll be forced to do your sorting on the client. Next, if you dump
> your front-end and it's rewritten, you'll have this artifact in your
> database that those inheriting your work won't know what to do with
> (or, given enough time, you won't either). In this situation, where
> you've decided to store application-specific data in your database, at
> least make it friendly to whoever may be administering thing. Store
> your application-specific data in an application-specific location --
> either a "helper" database that is specific to your application, or in
> tables that are clearly indicated as being specific to your application
> (e.g. App_MyApplication_InvoiceSorting) so that it's known they can be
> dropped without implications once your application is no longer used.
> -Alan
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141944759.875765.197440@.e56g2000cwe.googlegroups.com...
> This si why I go by "--CELKO--" in the Newsgroup; peopel keep getting
> us all the time.

>
> So the ordering has logical meanng in the data model!
yes, yes it does.

>
> So moving forward and agreeing (I assume we agree?) that the data does
> belong in the DB, is this a valid case for a delimited string? <<
> NO, it is a valid reason for a sequencing column in First Normal Form.
> Since you will not post any DDL or even helpful narrative with
> meaningful names, here is a guess:

> CREATE TABLE FirmwareTemplates
> (customer_id INTEGER NOT NULL
> REFERENCES Custromers (customer_id),
> template_seq INTEGER NOT NULL,
> template_txt VARCHAR (255) NOT NULL,
> PRIMARY KEY (customer_id, template_seq ));
>
Gotcha, I will investigate this, thanks for the suggestion!

Create Database using C#

Hi,
I write software in C# that should create (by itself) a new database.
The SQL server should be installed on the customer's computers silently
(I still need to figure how to do this part)
How can my software create a database that only it can access? Do I
need to create a special user account from the software?
What user account do I use to open a connection to the SQL server in
the first place when I create the database (taking under consideration
that the SQL server was installed silently and as far as the user
concern he has no idea my software uses SQL server).
To summarize:
1.The install of my software should install and configure the SQL
server silently (appreciate any help here)
2.The software needs to access the local SQL server and create a
database that only it can access when the software runs under a simple
"USER" account in Win2K/XP and not "ADMINISTRATOR" (my big problem -
any idea?)
Thanks in advance,
Oren
"orenbt78" <orenbt78@.googlemail.com> wrote in message
news:1164123542.578586.6280@.j44g2000cwa.googlegrou ps.com...
> Hi,
> I write software in C# that should create (by itself) a new database.
> The SQL server should be installed on the customer's computers silently
> (I still need to figure how to do this part)
> How can my software create a database that only it can access? Do I
> need to create a special user account from the software?
> What user account do I use to open a connection to the SQL server in
> the first place when I create the database (taking under consideration
> that the SQL server was installed silently and as far as the user
> concern he has no idea my software uses SQL server).
> To summarize:
> 1. The install of my software should install and configure the SQL
> server silently (appreciate any help here)
> 2. The software needs to access the local SQL server and create a
> database that only it can access when the software runs under a simple
> "USER" account in Win2K/XP and not "ADMINISTRATOR" (my big problem -
> any idea?)
> Thanks in advance,
> Oren
>
See
Embedding SQL Server Express into Custom Applications
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/emsqlexcustapp.asp
User Instances for Non-Administrators
http://msdn2.microsoft.com/en-us/library/ms143684.aspx
David
|||Thanx for the reply.
The "Embedding SQL Server Express into Custom Applications" seems to be
very helpful.
However, I can't figure out yet how to solve my problem with creating
databases as a simply user.
I plan to create a new database every year and let the user to view
last year's data while I am updating this year's data. That means 2
connections at the same time.
In the "User Instances for Non-Administrators" document it says "One
user can only have one user instance". So how can I do that?
And how do I do it in C# code?
Thanks again,
Oren
|||"orenbt78" <orenbt78@.googlemail.com> wrote in message
news:1164565596.943159.13410@.14g2000cws.googlegrou ps.com...
> Thanx for the reply.
> The "Embedding SQL Server Express into Custom Applications" seems to be
> very helpful.
> However, I can't figure out yet how to solve my problem with creating
> databases as a simply user.
> I plan to create a new database every year and let the user to view
> last year's data while I am updating this year's data. That means 2
> connections at the same time.
> In the "User Instances for Non-Administrators" document it says "One
> user can only have one user instance". So how can I do that?
> And how do I do it in C# code?
An instance is an instance of the database engine. A single instance of the
database engine can have multiple databases.
And in C# you just send TSQL commands using a
System.Data.SqlClient.SqlCommand object. You can get the syntax for the
commands from SQL Server Books Online.
TDavid
|||I have managed to create the database file (code below). I am not sure
it is the right way.
I would like to create a password for these database so only my
software will be able to control it (change data). how do i do that?
tmpConn.ConnectionString = "Data Source=(local); DATABASE =
master;Integrated Security=True; user instance=true";
sqlCreateDBQuery = " CREATE DATABASE " + DBParam.DatabaseName + " ON
PRIMARY "
+ " (NAME = " + DBParam.DataFileName +", "
+ " FILENAME = '" + DBParam.DataPathName +"', "
+ " SIZE = 5MB,"
+ "FILEGROWTH =" + DBParam.DataFileGrowth +") "
+ " LOG ON (NAME =" + DBParam.LogFileName +", "
+ " FILENAME = '" + DBParam.LogPathName + "', "
+ " SIZE = 1MB, "
+ "FILEGROWTH =" + DBParam.LogFileGrowth +") ";
SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, tmpConn);
try
{
tmpConn.Open();
MessageBox.Show(sqlCreateDBQuery);
myCommand.ExecuteNonQuery();
MessageBox.Show("Database has been created successfully!", "Create
Database", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "Create Database",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
tmpConn.Close();
}
|||anyone? any idea?