Solved

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

Posted on 2010-09-09
5
644 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
[X]
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
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL order by with "over" statement and row_number() 11 65
Query to Add Late Tolerance 10 87
INSERT DATE FROM STRING COLUMN 18 67
Need help in debugging a UDF results 7 72
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

710 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