Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 647
  • Last Modified:

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

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
wantime
Asked:
wantime
3 Solutions
 
Sachid_SinghCommented:
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
 
dax_badCommented:
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
 
dbidbaCommented:
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
 
wantimeAuthor Commented:
none
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now