What is the correct way to do this?

[Grey Alien]

I have created a SQL Server Express db in my ASP.Net website. I have a
file that contains several SQL statements to create tables (100+) and
stored procedures. I am guessing that there has to be a way of creating
the database tables and stored procedures from the file with SQL
statements (other than typing the statements manually - which would take
hours - and is also error prone). However, I ahve not been able to find
any information that shows how I can use the SQL statements in my file
to generate the tables and stored procedures in my db. Does anyone know
how to do this?. Ideally, I will have the .sql file in the WebSite
project so that whenever I change the schema or a stored procedure, I
can automatically rebuild the db (i.e. update the db) to reflect the new
changes.

So what I need to know is the ff:

1). How can I use my SQL statements file to create tables and stored
procs in my newly created db?
2). How can I keep the db synchronised with any changes in the *.sql
file (i.e. if I change a stored procedure etc, how can I "update the db
with the change?)

 

[William \(Bill\) Vaughn]

If you create a correct script (a set of batched TSQL commands separated by
"GO"), you can execute it with SQLCMD or from your own code. I include an
example of an in-process SQLCMD class in my SQLCe EBook but it's not that
hard to launch SQLCMD. This batch can perform any DDL (or DML) operations
needed. AFA maintenance, you'll want to read up on managing databases and
updating schema and procedures in place. There are a number of issues to
consider...

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[bruce barker]

it depends.

if you have sql2005 dev, then you can use sqlcmd.exe to run the scripts
or use sql work bench.

if you have team suite for databases, then create a database project. it
will create scripts to update prod to dev. (just hit deploy).

if you have none of the above you need to write a program.


-- bruce (sqlwork.com)

 

[Chris]

To keep the DB synchronized with your latest changes, you can declare
your procedures as follows. Every time the script runs, your
procedures will be re-installed.

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P' AND [name] =
'MyProc')
DROP PROCEDURE MyProc
GO
CREATE PROCEDURE MyProc
AS
-- Implement procedure here
GO

Cheers,
Chris