Fog Creek Software
Discussion Board




MSSQL Procedure Request

I am in need of a stored procedure that allows me to schedule a one-step, one-time only SQL Server Agent job and checks if the SQL Server Agent is running and starts it if it's not, on the target server.

Do any of you SQL gurus know of something like this?

Basically, I would like the call to look something like this:

exec spScheduleJob @statement='update tbl set fld=0', @time='7/22/2004 11:59 PM', @checkService=1

Thanks!

Wayne
Friday, July 23, 2004

Homework?

Formerly someone else
Friday, July 23, 2004

Well, I'm working at home today but no, this is not homework.

The reason I need this is because often times I need to modify the database with a simple DDL statement, but I can't do it while people are working and it would save me many steps to create it manually.

Anyway, I found a good way to get the code to schedule the job, basically you manually create what you want in Enterprise Manager and then right-click the job and click Generate SQL Script under All Tasks.

This gave me the SQL that I needed to create the job, and I can parameterize it and make it a procedure.  Now all I need is a way to see if SQL Server Agent is running from Transact-SQL.

Wayne
Friday, July 23, 2004

google?

sir_flexalot
Friday, July 23, 2004

On approach:

Figure out how, from the command line, to see if the service is running.  You want a command that will never prompt you, just return some sort of text.  There is probably something in the NT resource kit.

Next, create a table with with two columns, and int and a varchar(256).  Set the int column to be an identity column.

Then run:

INSERT tableyoucreated(varcharcolumnname)
EXEC master..xp_cmdshell 'cmd to test service running'

You an now query the table to see the results from the command.  The int column that is an identity will let you make sure of the sequence.

To start the service you should be able to use:

EXEC master..xp_cmdshell 'NET START servicename'

Good luck!

RH
Friday, July 23, 2004

OK RH, thank you.

I did google for this, but there are so many results to sift through, I thought someone might know.

I ended up just doing it myself (as usual :)

Wayne
Friday, July 23, 2004

you might find some good info at http://www.sqlteam.com

----
http://sleepyhead81.blogspot.com

Espen Antonsen
Friday, July 23, 2004

*  Recent Topics

*  Fog Creek Home