June 10, 2003 at 2:59 am
I have scripts that act as an installation routine for an application that I use to build my database and all its dependent objects.
I tend to break down the scripts into seperate files as follows
[Build functions/stored procedures
Now obviously I could merge these into one huge script and run it but I prefer to keep the files seperate for maintenance and upgrade reasons.
This means that I have to load and run all my scripts one by one.
Ideally what I would like to be able to do is to have some sort of installation routine, say a batch file, that runs these scripts in order automatically but stops if an error occurs in one of the scripts.
I should also like to report any errors that get found.
Has anyone got any suggestions?
June 10, 2003 at 3:02 am
You could load the script into a temp table using BULK insert.
Looping through the table, row by row, select out the row and execute it.
Should work.
You have one script with the params (File names).
See BOL for BULK INSERT.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 10, 2003 at 5:14 am
Not bad. You could also write something to pull scripts from a folder and execute. In either case you have to have a way to establish the order to process them or you'll be trying to create functions and views before you create the tables. For executing you could use OSQL and log errors to a text file, or use an ADO connection and trap/report the errors with a handler to a text file or a table.
Andy
June 10, 2003 at 5:27 am
I see what you are getting at but the situation is that I am installing databases on someone elses i.e. customers servers so this would be a bit "chicken and egg". It's great for my test environment thought.
Ideally I would like to be able to do something along the lines of
echo off
cls
osql -USA -PPassword -iInstall01.sql -oInstall01.Err
if ErrorLevel==0
osql -USA -PPassword -iInstall01.sql -oInstall01.Err
etc
In an ideal world I would like something like the install routine that is used to apply SQL Server service packs. The one that says "applying n of m".
June 10, 2003 at 5:35 am
Hello David,
quote:
Ideally I would like to be able to do something along the lines ofecho off
cls
osql -USA -PPassword -iInstall01.sql -oInstall01.Err
if ErrorLevel==0
osql -USA -PPassword -iInstall01.sql -oInstall01.Err
etc
In an ideal world I would like something like the install routine that is used to apply SQL Server service packs. The one that says "applying n of m".
not sure it this is a good solution for you. But I remember Visual Studio 6 comes along with a programmable version of Install Shield Wizard. I haven't tried this and it might be overkill for what you want. But I guess it can do want you need.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 10, 2003 at 6:48 am
If you still want the batch file method, this might do it.
@echo off
cls
echo Installing 1 of n
osql /Sserver /Uuser /Ppassword -ddatabase -b /m-1 -iInstall01.sql -oInstall01.Err
IF ERRORLEVEL 1 GOTO er
echo Installing 2 of n
osql /Sserver /Uuser /Ppassword -ddatabase -b /m-1 -iInstall02.sql -oInstall02.Err
IF ERRORLEVEL 1 GOTO er
echo Installing 3 of n
osql /Sserver /Uuser /Ppassword -ddatabase -b /m-1 -iInstall03.sql -oInstall03.Err
IF ERRORLEVEL 1 GOTO er
...etc
GOTO ok
:er
--error reporting here
GOTO ex
:ok
--ok reporting here
:ex
Edited by - davidburrows on 06/10/2003 06:49:18 AM
Far away is close at hand in the images of elsewhere.
Anon.
June 10, 2003 at 4:59 pm
If you do all error checking within your T-SQL script, you can create
multiple procs. one for each job. After that all you have to do is call your
main proc let say for ex. Create_DateBase is you main proc.
Exec Master..Create_Database 'DB_Name'
within your Create_Database proc you call Build_Tables proc. ..
and this way you go down through the list and make each proc. return a status code to
the calling proc. indicating success or failure.
This also helps you if you in the installation process, b/c you can script out
your procs. and save it to a file.
MW
MW
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply