Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

808 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