Clarification on using the Scheduling procedure

Andre Vigneau — Mon, 15 Nov 2004 07:13:00 GMT

The commands you will give to the Windows scheduler will run under the Windows scheduler context as a command prompts (DOS) command.

You can run any exe, bat, cmd and so forth file type commands. When you need the scheduler to open a DOS window to run the command into it you add the "cmd /c" prefix to your command.

This will work

exec dbo.dbt_CreateSchedule

@CommandLine = 'cmd /c dir c:\'

This will not

exec dbo.dbt_CreateSchedule

@CommandLine = 'cmd /c dir c:\ > c:\DirResult.txt'

Like I was saying in the article the Scheduler (SCHTASKS or AT) has limitations with apostrophes (so with broken commands with spaces). You will have difficulties to have it run commands needing parameters. That is why I use batch files and encapsulate the parameters into it. It is easier to have the Scheduler run simple batch files.

For your example I suspect that you want the Scheduler to run a procedure in SQL Server context. For this you will need to run your command using OSQL context with all the connection parameters needed by the tool. Of course all this encapsulated in a batch file.

As an example your batch file would look like this.

OSQL -SYourServerName -E -Q"EXEC [ScheduleTool].[dbo].[dbt_ScheduleTest]"

I strongly suggest you don't store passwords into batch files... That is why the -E was used. There are many other parameters to set to better control the behavior of OSQL.

Supposing you stored the batch file on the C drive and named your batch file test.bat; The CreateSchedule command would then be

exec dbo.dbt_CreateSchedule

@CommandLine = 'cmd /c c:\test.bat'

Now it should run fine.

Hoping it helped clarified.

SQLServerCentral / Discuss Content Posted by Andre Vigneau / Article Discussions / Article Discussions by Author / Clarification on using the Scheduling procedure / Latest Posts

Clarification on using the Scheduling procedure