SQL Server Agent: How to "sleep"?

Question

In a scripting step in a scheduled task in SQL Server Agent 2005, I need to trigger a webscript that is running on a different server. I'm doing this:

Dim ie 
Set ie = CreateObject( "InternetExplorer.Application" )

ie.navigate "...to my dreamscript"

' Wait till IE is ready
Do While ie.Busy
    (1)
Loop

ie.Quit
set ie = Nothing

At (1) I would like to "sleep" (e.g. WScript.sleep(..)), but WScript is not available in this environment. Is there another way to "sleep" for a while?

Answer 1

You can write a console applicaton and execute the console app in SQL agent job.

Answer 2

You could execute the wscript by using a SQL Server Agent task with a type of "Operpating System (CmdExec)". This requires that xp_cmdshell be enabled and it is often disabled (by default) due to security concerns. However, it does allow you to initiate programs, such as wscript, that are run at the command prompt.

You could move the code into SQLCLR where you can write a stored procedure in C# or VB.Net. The VB.Net SQLCLR Code would be pretty similar to your original wscript.

Answer 3

If you're only trying to have the SQL SErver Agent task that waits for a time period use a T-SQL Task with the script

WAITFOR DELAY '01:00:00' -- wait for an hour

and change the time to the duration that you'd like to wait.

HTH Andy