Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Clarification on using the Scheduling procedure Expand / Collapse
Posted Monday, November 15, 2004 7:13 AM


Group: General Forum Members
Last Login: Friday, November 21, 2008 9:10 AM
Points: 11, Visits: 5

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.


Kindest Regards,

Andre Vigneau MCSE, MCDBA

Post #146243
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse