November 15, 2004 at 7:13 am
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.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply