Checking to see if SQLAgent is running

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Checking to see if SQLAgent is running

Author

Topic

scootermcfly
Yak Posting Veteran

66 Posts

Posted - 2003-09-03 : 10:17:21

I have some scripts that run on installation of our product, and create some jobs. Right now, when the job scripts run, if the SQLAgent is not running, it returns a message: "SQLServerAgent is not currently running so it cannot be notified of this action"

I would like to check and see if he is running or not before running these scripts that create the jobs. Is there an easy way through tsql to get that, without going into the system tables?

Thans,
Scooter McFly

rharmon
Starting Member

41 Posts

Posted - 2003-09-03 : 12:35:08

Gotta love undocumented stored procedures!

exec master.dbo.xp_servicecontrol querystate,'sqlserveragent'

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-03 : 14:08:29

Hm... we prefer 'documented' stuff :)

declare @ob int, @st int
exec sp_OACreate 'SQLDMO.SQLServer', @ob output
exec sp_OAMethod @ob, 'Connect("yourServer", "sa", "pwd")'
exec sp_OAGetProperty @ob, 'JobServer.Status', @st output

select @st --- 1 means he's running... oh my god...

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-03 : 14:35:20

quote:
Originally posted by Stoad

Hm... we prefer 'documented' stuff :)

declare @ob int, @st int
exec sp_OACreate 'SQLDMO.SQLServer', @ob output
exec sp_OAMethod @ob, 'Connect("yourServer", "sa", "pwd")'
exec sp_OAGetProperty @ob, 'JobServer.Status', @st output

select @st --- 1 means he's running... oh my god...

Wow...impressive...

I'm assuming it's for an instance level..

'Connect("yourServer\Instance", "sa", "pwd")'

Put that one in the SQL toolbox...

Anyway to find all of the properties for a given object...not to mention a list of all the objects...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-03 : 15:09:50

Oh my god... Brett, I forgot to destroy the object:

exec sp_OADestroy @ob

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-03 : 15:18:28

If you close the session...won't the object go away?

Also I'm assuming @ob is going to be different everytime...no?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-03 : 16:04:36

Oh my god... Brett... exactly! Destroyed in the end of batch...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-03 : 19:27:55

DESTROY OBJECTS EXPLICITLY, ALL THE TIME, EVERY TIME, REGARDLESS OF WHICH LANGUAGE YOU PROGRAM IN.

IMHO & NSHO, this is even more important than documenting or indenting your code, as far as programming habits go. NEVER rely on a garbage collector to clean things up for you.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-04 : 11:12:30

quote:
Originally posted by robvolk

DESTROY OBJECTS EXPLICITLY, ALL THE TIME, EVERY TIME, REGARDLESS OF WHICH LANGUAGE YOU PROGRAM IN.

IMHO & NSHO, this is even more important than documenting or indenting your code, as far as programming habits go. NEVER rely on a garbage collector to clean things up for you.

I agree Rob...but tell us how you really fell about the subject...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources