Batch File

One method is with a SQL Server agent CmdExec job step. Another is via xp_cmdshell. If you choose to use xp_cmdshell, be sure you fully understand the security implications and don’t grant direct execute permissions on the proc.

How to: Create a CmdExec Job Step

By default, only members of the sysadmin fixed server role can create CmdExec job steps. These job steps run under the context of the SQL Server Agent service account unless the sysadmin user creates a proxy account. Users who are not members of the sysadmin role can create CmdExec job steps if they have access to a CmdExec proxy account.

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, create a new job or right-click an existing job, and then click Properties.For more information about creating a job.
3. In the Job Properties dialog, click the Steps page, and then click New.
4. In the New Job Step dialog, type a job Step name.
5. In the Type list, choose Operating system (CmdExec).
6. In Run as list, select the proxy account with the credentials that the job will use. By default, CmdExec job steps run under the context of the SQL Server Agent service account.
7. In the Process exit code of a successful command box, enter a value from 0 to 999999.
8. In the Command box, enter the operating system command or executable program.
9. Click the Advanced page to set job step options, such as: what action to take if the job step succeeds or fails, how many times SQL Server Agent should try to execute the job step, and the file where SQL Server Agent can write the job step output. Only members of the sysadmin fixed server role can write job step output to an operating system file.

What is XP_CMDSHELL

“xp_cmdshell” is an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code. If needed the output of these commands will be returned to the calling routine.

Only users in sysadmin role can execute xp_cmdshell stored procedures.

If a login executing this extended stored procedure is a member of the sysadmin role then the submitted command will run under the security context associated with the SQL Server Service account in which it runs.

By default XP_CMDSHELL is turned off in SQL Server 2005

TO ENABLE XP_CMDSHELL run this code:

EXECUTE sp_configure ’show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure ‘xp_cmdshell’, ‘1′
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure ’show advanced options’, 0
RECONFIGURE WITH OVERRIDE
GO