Automated update scripts
-
Hi, I plan to create a program to allow user to execute batch scripts to update the database by double click the program instead of execute every scripts by query analyzer. At first, I try to use the ADODB's Execute method to run the scripts, but I get the error "Incorrect syntax near 'go'" because the method do not support the 'go' command. After do the search online about 4 hours I still cannot find a best solution for my problem. So, I post the message to the forum to see whether anyone can help me to solve it.
Actually, I find some posible methods to solve this problem, but I don't have confident it is a best solutions or not. First method is to write a function to remove the 'go' command before I execute the script. It is look like very dangerous cause removing the 'go' command may generate other error. Second method is to use the isql utility, but I don't have much of experiance about this method.
Thank you.
-
Use osql (look up different options in BOL)...one way to do it would be to ask the end user for input like instance name, name of the database, authentication method (Windows or SQL Server - if SQL Server, then ask for login-id and password on the screen), the script files that need to be run.
Have a bat file that has place holders for these values that the end user would be providing (so you can have a javascript that does the search and replace of those place holder variables) and then execute the batch file. Take the output from the osql run into an out file and then check for errors - if there are errors, display that to the end user else report that the run has been successful.
-
Dear rsharma,
I just did a test to use the isql and osql to run the scripts and it work! Do you know why the isql and osql not so popular? Is it any disadvantages for this method?
-
My team uses the osql tool to solve this problem.
OSQL is a command line too which allows you to execute, among other things tsql scripts.
the parameters included in my sample are:
- -S : the sql server name to execute the script against
- -d : the name of the database to execute the script against. (if your script contains any "use" statements this will be voided.
- -E : specify a trusted connection (use -U & -P for a sql login)
- -b : break on batch error
- -i : the name of the script to execute.
osql -S localhost -d DevDBName -E -b -i "ClearData.sql"
Just use your favourite programing language to shell out an call this script. This will execute as if being run through query analyser, avoiding any problems associated with ado.
<rant>
I don't know that the osql tool is any less popular that say query analyser (isqlw) however it seems that many people don't take the time to learn about all the command line tools provided with sql server and instead fixate upon the windows gui tools where in actual fact there is most to be benefited from learning about the command line tools.
</rant>
-
I agree with clayton. For our installation process, we use Javascript (primarily to do a Search and Replace for the place-holders in the scripts), VBScript (using SQL-DMO) and batch files that use osql and all this works out very well. Besides the ones that clayton has mentioned above, there are other important parameters as well that you might be interested in - look up BOL.
-
Thank you for all of you help. I think I know what to do already.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply