SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clarification on using the Scheduling procedure


Clarification on using the Scheduling procedure

Author
Message
Andre Vigneau
Andre Vigneau
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search