Sometimes when you need to automate some stuff from the SQL server you need to create a batch file and run it in the Task Scheduler. This way you can run the jobs you need to do and then run the executable or batch file you need from a Stored Procedure.

Firstly you need to make sure that the XP_CMDSHELL is enabled. This will allow you to execute the files from SQL.

This can be easily done from the Surface Area Configuration tool or by executing the following code:

EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE

This should enable the XP_CMDSHELL. Now to execute a file you need to add the following SQL code to your present Stored procedure:

DECLARE @myfile varchar(200)
SET @myfile = ‘C:\sysfiles\copytootherserver.bat’
EXEC master..xp_cmdshell @myfile