The Script In The Batch File Using Sqlcmd

I often create batch files to run SQL scripts (especially SPS), and then schedule jobs using SQL Agent / Task Scheduler. In this post, I will show you how to create a batch file with SQL commands.

First, the command used to execute SQL queries commnd SQLCMD system. The general syntax of this command is available on the link. I'm going with a single as follows

Server Name sqlcmd-Q-ES-o Query "Output Path"

here

E = secure connection (Windows Authentication)

-Server Name = S

-Q = CommandLineQuery

O = output file path

a simple command prompt can commnad

C: \ Users \ geekEpisodes> sqlcmd-IS "Local-SRV01"-Q "Select top 10 * from MyTestDatabase .. [ForecastHistory]"-o "C: \ New Folder \ output.txt"

All commands you execute the command can be run from batch control / files. to create a batch file with SQL commands to follow the steps below


1: Create a file with a bat or cmd (for example testfile.bat, test.cmd) ...

2: Add the file commands such as

sqlcmd-IS "Local-SRV01"-Q "Select top 10 * from MyTestDatabase .. [ForecastHistory]"-o "C: \ New Folder \ output.txt"

sqlcmd-IS "Local-SRV01"-Q "select count (1) of MyTestDatabase .. [ForecastHistory]"-o "C: \ New Folder \ Output2.txt"

sqlcmd-ES "SRV01 local"-Q "Exec MyTestDatabase .. [spPopulateData]"

3: Now save it.

4: Double-click the file and query the file exeuted. This file can be used in the Task Scheduler and other applications.