Solved

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

Posted on 2010-09-09
5
640 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

861 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now