How to automatically execute SQL queries with a batch file


Article ID: 51069 - Last Review: December 14, 2010

ABOUT

The following article explains how to automatically execute SQL queries with the use of a batch file.



OSQL UTILITY

The osql utility allows you to enter Transact-SQL statements, system procedures, and script files.  This utility uses ODBC to communicate with the server.  Once you have the osql command written, save the entire line into a file with a .bat extension.

Commonly Used Arguments:

-S (SQL Server\Instance name) Ex. -S myserver\SQLEXPRESS

-E (Uses a trusted connection instead of requesting a password)

-U (Login ID)

-P (Password)

-i (Input file) Ex. -i "c:\My Folder\MyScript.sql"

-o (Output file) Ex. -o "c:\My Folder\MyScriptLog.txt"

Example Syntax Usage:

osql -S .\sqlexpress -E  -i "c:\defragIndexes.sql" -o "c:\defragIndexesLog.txt"
(This command would execute a SQL script on a local SQLExpress server using Windows Authentication)

osql -S mysql -U sqluser -P sqlpassword  -i "c:\defragIndexes.sql" -o "c:\defragIndexesLog.txt"
(This command would execute a SQL script on a SQL server using SQL authentication)

For more information about the OSQL Utility, see the following Microsoft article: http://technet.microsoft.com/en-us/library/aa214012(SQL.80).aspx



EXECUTING THE BATCH FILE

Once you have your batch file created with the "osql" command, you can use Windows Scheduled Tasks to automatically run this script.

  1. Open Control Panel=>Scheduled Tasks=>Add a Scheduled Task
  2. Browse to the batch file (Ex. c:\MyScripts\myscript.sql)
  3. Choose how often to run the task
  4. Choose the time to run the task
  5. Enter the Windows User account credentials


APPLIES TO

All CCM versions

Keywords: osql executing sql scripts automatically


Posted Tuesday, December 14, 2010 by ademski
http://micc.mitel.com/kb/KnowledgebaseArticle51069.aspx