Solved

MSSQL 2005: execute many MSSQL script by command (variable should be used)

Posted on 2010-09-09
5
643 Views
Last Modified: 2013-11-05
hi all,

that's say i have three MSSQL Script, step1.sql, step2.sql, step3.sql.

when i want execute these .sql scripts, i drag them into Microsoft SQL Server Management Studio and the execute them.  But now i would like to write a batch file and execute theses .sql scripts just by click the .batch file.

can anyone tell me how do i write such .batch file.

One more question, in these files i call the database name by using "USE [dbname] " . And i would like to use a variable as database name, because i want  let these scripts run for database with different name. Everytime i just need to run the batch file and input a parameter as database name.

any suggestion are greatly appreciated. thanks.

wantime
0
Comment
Question by:wantime
5 Comments
 

Assisted Solution

by:Sachid_Singh
Sachid_Singh earned 167 total points
ID: 33634795
MSSQL Scripts are  executable files stored as a .sql file, we write the Procedure  in these files to be
executed after converting it to batch file, you can create a DOS batch file that calls osql utility which
"allows you to enter Transact-SQL statements, system procedures, and script
files"
0
 
LVL 6

Expert Comment

by:dax_bad
ID: 33634854
What about converting the scripts into stored procedures and then use a small VBscript or the likes to call the SP's with a few parameters?

/Daniel
0
 
LVL 4

Assisted Solution

by:MichaelMH
MichaelMH earned 166 total points
ID: 33635186
0
 
LVL 5

Accepted Solution

by:
dbidba earned 167 total points
ID: 33653338
There are several ways to do this.

One is a straight sql solution. Create a stored procedure as described above to contain the code. Then create a SQL Agent job which launches the stored procedure. This assumes that you are not using Express Edition which does not include the job scheduler. With this method, you may want to create the stored procedure in each database you want to execute against and call each of them. To accomplish the flexibility of passing in the database name as a parameter, it requires that you do dynamic SQL, which ups the complexity.

To launch it from the Windows command line, create a .sql file containing your code, but not the "use database" statement. Research the SqlCmd command. It is the newer version of osql. It allows you to execute a .sql file against an instance and database which you specify. You may also send the output to a log file. Put these SqlCmd commands, one for each database in which you wish to run the code, into a .bat file. You may then execute the .bat file to run the sql code in every database.

..dbidba
0
 

Author Closing Comment

by:wantime
ID: 33779359
none
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question