Enumerating jobs on a SQL server using SMO - in C#

Hi all, I'm trying to connect to SQL and enumerate all Agent jobs to find a
particular and call job.envoke on it.
Looked every where but can't find a code snippet on it or a sample.
Anyone can assist? CheersYou can use this to list all the jobs.
USE msdb
EXEC sp_help_job
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Oops... Just read the subject line about SMO.. My fault..
You can try this link..
http://msdn2.microsoft.com/en-us/li...nt.
aspx
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Thanks omnibuzz, I've tried that uri but since I'm new to SQL prog I haven't
figured out how to use for instance jobbasecollection classes to find out if
the job I'm interested in exist on the server.
Any help there with the code\implementation? Thanks.
"Omnibuzz" wrote:

> Oops... Just read the subject line about SMO.. My fault..
> You can try this link..
> http://msdn2.microsoft.com/en-us/li...n
t.aspx
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>

Enterprise Manager problem : jobs and alerts flash, while Agent properties lock-up

I am having a problem with Enterprise Manager. The server is SQL
Server 2000, SP 2, with the hotfix (the server is used for an ePiphany
application, which does not support sp3).
Anyway, when you try to "double-click" a job, it flashes up once, then
disappears. The same can be said for the alerts, and it does not list
any operators (if you query sysoperators, you see all the operators).
In addition, if you try to display the SQL Agent properties,
Enterprise Manager locks up.
This problem has been narrowed down to only inflict the items under
Management>SQL Server Agent. It does not affect viewing current
activity, maint plans, backup devices, or the SQL Error log.
Their is a KB article about the same type of problem with maintenance
plans, and the fix is to alter the path for the log file of SQL Agent.
This is not possible with this problem because you cannot get to the
properties to alter the log file location for the agent.
Anybody seen this problem?
HGHumphreyYou can change the location of sql agent log file in the
registry, go to
SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent
under My Computer\HKEY_LOCAL_MACHINE
and change the file path in ErrorLogFile
Alternatively if you have another pc with Enterprise
Manager you can use it to manage your server.
hth.
>--Original Message--
>I am having a problem with Enterprise Manager. The
server is SQL
>Server 2000, SP 2, with the hotfix (the server is used
for an ePiphany
>application, which does not support sp3).
>Anyway, when you try to "double-click" a job, it flashes
up once, then
>disappears. The same can be said for the alerts, and it
does not list
>any operators (if you query sysoperators, you see all the
operators).
>In addition, if you try to display the SQL Agent
properties,
>Enterprise Manager locks up.
>This problem has been narrowed down to only inflict the
items under
>Management>SQL Server Agent. It does not affect viewing
current
>activity, maint plans, backup devices, or the SQL Error
log.
>Their is a KB article about the same type of problem with
maintenance
>plans, and the fix is to alter the path for the log file
of SQL Agent.
> This is not possible with this problem because you
cannot get to the
>properties to alter the log file location for the agent.
>Anybody seen this problem?
>HGHumphrey
>.
>|||That does not work either. Any other clues?
HGHumphrey
<anonymous@.discussions.microsoft.com> wrote in message news:<ebd201c3f0b2$60ca6e30$a601280a@.phx.gbl>...
> You can change the location of sql agent log file in the
> registry, go to
> SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent
> under My Computer\HKEY_LOCAL_MACHINE
> and change the file path in ErrorLogFile
> Alternatively if you have another pc with Enterprise
> Manager you can use it to manage your server.
> hth.
>
> >--Original Message--
> >I am having a problem with Enterprise Manager. The
> server is SQL
> >Server 2000, SP 2, with the hotfix (the server is used
> for an ePiphany
> >application, which does not support sp3).
> >
> >Anyway, when you try to "double-click" a job, it flashes
> up once, then
> >disappears. The same can be said for the alerts, and it
> does not list
> >any operators (if you query sysoperators, you see all the
> operators).
> >In addition, if you try to display the SQL Agent
> properties,
> >Enterprise Manager locks up.
> >
> >This problem has been narrowed down to only inflict the
> items under
> >Management>SQL Server Agent. It does not affect viewing
> current
> >activity, maint plans, backup devices, or the SQL Error
> log.
> >
> >Their is a KB article about the same type of problem with
> maintenance
> >plans, and the fix is to alter the path for the log file
> of SQL Agent.
> > This is not possible with this problem because you
> cannot get to the
> >properties to alter the log file location for the agent.
> >
> >Anybody seen this problem?
> >
> >HGHumphrey
> >.
> >|||This has been fixed. It was because we had an entry in the
Sysoperators table that was blank. Once this was deleted, and the
services were restarted, it was fine.
Thanks,
HGHumphrey
hhumphrey@.hrblock.com (HGHumphrey) wrote in message news:<188b70ac.0402101354.6b1ffbcb@.posting.google.com>...
> I am having a problem with Enterprise Manager. The server is SQL
> Server 2000, SP 2, with the hotfix (the server is used for an ePiphany
> application, which does not support sp3).
> Anyway, when you try to "double-click" a job, it flashes up once, then
> disappears. The same can be said for the alerts, and it does not list
> any operators (if you query sysoperators, you see all the operators).
> In addition, if you try to display the SQL Agent properties,
> Enterprise Manager locks up.
> This problem has been narrowed down to only inflict the items under
> Management>SQL Server Agent. It does not affect viewing current
> activity, maint plans, backup devices, or the SQL Error log.
> Their is a KB article about the same type of problem with maintenance
> plans, and the fix is to alter the path for the log file of SQL Agent.
> This is not possible with this problem because you cannot get to the
> properties to alter the log file location for the agent.
> Anybody seen this problem?
> HGHumphrey

Enterprise Manager problem : jobs and alerts flash, while Agent properties lock-up

I am having a problem with Enterprise Manager. The server is SQL
Server 2000, SP 2, with the hotfix (the server is used for an ePiphany
application, which does not support sp3).
Anyway, when you try to "double-click" a job, it flashes up once, then
disappears. The same can be said for the alerts, and it does not list
any operators (if you query sysoperators, you see all the operators).
In addition, if you try to display the SQL Agent properties,
Enterprise Manager locks up.
This problem has been narrowed down to only inflict the items under
Management>SQL Server Agent. It does not affect viewing current
activity, maint plans, backup devices, or the SQL Error log.
Their is a KB article about the same type of problem with maintenance
plans, and the fix is to alter the path for the log file of SQL Agent.
This is not possible with this problem because you cannot get to the
properties to alter the log file location for the agent.
Anybody seen this problem?
HGHumphreyThis has been fixed. It was because we had an entry in the
Sysoperators table that was blank. Once this was deleted, and the
services were restarted, it was fine.
Thanks,
HGHumphrey
hhumphrey@.hrblock.com (HGHumphrey) wrote in message news:<188b70ac.0402101354.6b1ffbcb@.p
osting.google.com>...
> I am having a problem with Enterprise Manager. The server is SQL
> Server 2000, SP 2, with the hotfix (the server is used for an ePiphany
> application, which does not support sp3).
> Anyway, when you try to "double-click" a job, it flashes up once, then
> disappears. The same can be said for the alerts, and it does not list
> any operators (if you query sysoperators, you see all the operators).
> In addition, if you try to display the SQL Agent properties,
> Enterprise Manager locks up.
> This problem has been narrowed down to only inflict the items under
> Management>SQL Server Agent. It does not affect viewing current
> activity, maint plans, backup devices, or the SQL Error log.
> Their is a KB article about the same type of problem with maintenance
> plans, and the fix is to alter the path for the log file of SQL Agent.
> This is not possible with this problem because you cannot get to the
> properties to alter the log file location for the agent.
> Anybody seen this problem?
> HGHumphrey

Enterprise Manager Job Scheduler and the Time change problem

We just noticed a problem with the SQL Server Agent's Job Scheduler. During
the time change the other week, the system clock would reset itself every
day. I've seen it go an hour ahead and an hour behind. During this time,
some of the jobs got out of synch.
Let's say we have a few jobs that run every 4 hours. one of the clock
changes happened in the hour before it was to run, so afterward, the current
time is now passed when the job was scheduled to run. Since the time for
it's next scheduled run has already passed, the job just sits there and neve
r
runs again.
Why does SQL Server not see that a jobs scheduled time has passed, and just
continue with the next run in the schedule?Hi
"Granola Pete" wrote:

> We just noticed a problem with the SQL Server Agent's Job Scheduler. Duri
ng
> the time change the other week, the system clock would reset itself every
> day. I've seen it go an hour ahead and an hour behind. During this time,
> some of the jobs got out of synch.
> Let's say we have a few jobs that run every 4 hours. one of the clock
> changes happened in the hour before it was to run, so afterward, the curre
nt
> time is now passed when the job was scheduled to run. Since the time for
> it's next scheduled run has already passed, the job just sits there and ne
ver
> runs again.
> Why does SQL Server not see that a jobs scheduled time has passed, and jus
t
> continue with the next run in the schedule?
Is the system time being effected and where/how is the time being
synchronised to?
John|||Yes the system clock is what was being affected. From about Monday the 12th
until about Wed 14th or Thurs 15th, the clock would reset itself each mornin
g
to a different time. It is synching itself to "time.windows.com". It has
stabilized itself now.
Having the time switch near the time when a scheduled job is supposed to run
doesn't seem much of a problem.
It is more troubling that the Agent Job Scheduler couldn't recover the
schedule after "missing" a run at a certain time. Granted, I should have
been checking the jobs, but there is usually no need unless I get
notification that an error has occured.
"John Bell" wrote:

> Hi
> "Granola Pete" wrote:
>
> Is the system time being effected and where/how is the time being
> synchronised to?
> John|||Hi
"Granola Pete" wrote:

> Yes the system clock is what was being affected. From about Monday the 12
th
> until about Wed 14th or Thurs 15th, the clock would reset itself each morn
ing
> to a different time. It is synching itself to "time.windows.com". It has
> stabilized itself now.
> Having the time switch near the time when a scheduled job is supposed to r
un
> doesn't seem much of a problem.
> It is more troubling that the Agent Job Scheduler couldn't recover the
> schedule after "missing" a run at a certain time. Granted, I should have
> been checking the jobs, but there is usually no need unless I get
> notification that an error has occured.
>
I am not sure how you are will reliably know that there has been a run
missed because of a time change! There could be numerous scenarios of time
changes that you would have to provide decissions on whether or not the job
should run.
John|||Well, I know that a run has been missed and that it hasn't run again simply
by the fact that the scheduler shows the "Last Run Status(Start Date)" field
shows the 11th and the "Next Run Date" shows the 12th and it is already the
20th, no history of the run since 11th.
the problem isn't so much that it missed it, but that it didn't hit the next
scheduled time to run.
The system doesn't necessarily have to be smart enough to look back and see
when the last time it ran. If it is on a recurring schedule, say every 4
hours, it doesn't need to know that it didn't run at noon, only that it need
s
to run at 4 and then 8, etc. but I would hope it is smart enough to know
that if it's "Next Run Date" is somehow an earlier date than the current
time, that it recovers or at least throws an error.
The thing is, if a job fails once, it still runs the next time it is
scheduled to run. How is that any different from missing a run time? It i
s
just odd to me that it would work in one instance, but not the other.
"John Bell" wrote:
> I am not sure how you are will reliably know that there has been a run
> missed because of a time change! There could be numerous scenarios of time
> changes that you would have to provide decissions on whether or not the jo
b
> should run.
> John

Enterprise Manager Job Scheduler and the Time change problem

We just noticed a problem with the SQL Server Agent's Job Scheduler. During
the time change the other week, the system clock would reset itself every
day. I've seen it go an hour ahead and an hour behind. During this time,
some of the jobs got out of synch.
Let's say we have a few jobs that run every 4 hours. one of the clock
changes happened in the hour before it was to run, so afterward, the current
time is now passed when the job was scheduled to run. Since the time for
it's next scheduled run has already passed, the job just sits there and never
runs again.
Why does SQL Server not see that a jobs scheduled time has passed, and just
continue with the next run in the schedule?Hi
"Granola Pete" wrote:
> We just noticed a problem with the SQL Server Agent's Job Scheduler. During
> the time change the other week, the system clock would reset itself every
> day. I've seen it go an hour ahead and an hour behind. During this time,
> some of the jobs got out of synch.
> Let's say we have a few jobs that run every 4 hours. one of the clock
> changes happened in the hour before it was to run, so afterward, the current
> time is now passed when the job was scheduled to run. Since the time for
> it's next scheduled run has already passed, the job just sits there and never
> runs again.
> Why does SQL Server not see that a jobs scheduled time has passed, and just
> continue with the next run in the schedule?
Is the system time being effected and where/how is the time being
synchronised to?
John|||Yes the system clock is what was being affected. From about Monday the 12th
until about Wed 14th or Thurs 15th, the clock would reset itself each morning
to a different time. It is synching itself to "time.windows.com". It has
stabilized itself now.
Having the time switch near the time when a scheduled job is supposed to run
doesn't seem much of a problem.
It is more troubling that the Agent Job Scheduler couldn't recover the
schedule after "missing" a run at a certain time. Granted, I should have
been checking the jobs, but there is usually no need unless I get
notification that an error has occured.
"John Bell" wrote:
> Hi
> "Granola Pete" wrote:
> > We just noticed a problem with the SQL Server Agent's Job Scheduler. During
> > the time change the other week, the system clock would reset itself every
> > day. I've seen it go an hour ahead and an hour behind. During this time,
> > some of the jobs got out of synch.
> >
> > Let's say we have a few jobs that run every 4 hours. one of the clock
> > changes happened in the hour before it was to run, so afterward, the current
> > time is now passed when the job was scheduled to run. Since the time for
> > it's next scheduled run has already passed, the job just sits there and never
> > runs again.
> >
> > Why does SQL Server not see that a jobs scheduled time has passed, and just
> > continue with the next run in the schedule?
> Is the system time being effected and where/how is the time being
> synchronised to?
> John|||Hi
"Granola Pete" wrote:
> Yes the system clock is what was being affected. From about Monday the 12th
> until about Wed 14th or Thurs 15th, the clock would reset itself each morning
> to a different time. It is synching itself to "time.windows.com". It has
> stabilized itself now.
> Having the time switch near the time when a scheduled job is supposed to run
> doesn't seem much of a problem.
> It is more troubling that the Agent Job Scheduler couldn't recover the
> schedule after "missing" a run at a certain time. Granted, I should have
> been checking the jobs, but there is usually no need unless I get
> notification that an error has occured.
>
I am not sure how you are will reliably know that there has been a run
missed because of a time change! There could be numerous scenarios of time
changes that you would have to provide decissions on whether or not the job
should run.
John|||Well, I know that a run has been missed and that it hasn't run again simply
by the fact that the scheduler shows the "Last Run Status(Start Date)" field
shows the 11th and the "Next Run Date" shows the 12th and it is already the
20th, no history of the run since 11th.
the problem isn't so much that it missed it, but that it didn't hit the next
scheduled time to run.
The system doesn't necessarily have to be smart enough to look back and see
when the last time it ran. If it is on a recurring schedule, say every 4
hours, it doesn't need to know that it didn't run at noon, only that it needs
to run at 4 and then 8, etc. but I would hope it is smart enough to know
that if it's "Next Run Date" is somehow an earlier date than the current
time, that it recovers or at least throws an error.
The thing is, if a job fails once, it still runs the next time it is
scheduled to run. How is that any different from missing a run time? It is
just odd to me that it would work in one instance, but not the other.
"John Bell" wrote:
> I am not sure how you are will reliably know that there has been a run
> missed because of a time change! There could be numerous scenarios of time
> changes that you would have to provide decissions on whether or not the job
> should run.
> John

Enterprise Manager 2/ Me 0

Oi! Not that anyone will ever see this post again - but it was McAfee's
Entercept Agent causing the problem - the version we had apparently didn't
like comments (!) in stored procedures, and when it came across one, would
destabilize the server and cause it to crash (v 42.58)..... the latest
version (42.59) fixed the problem.
"B@.DJJ" wrote:

> Ahoy All!
> Say I come in, bright-eyed-and-bushy-tailed in the AM and say "Hey! I thi
nk
> I'll add a field to a table this Bright and Glorious morning!" so I fire u
p
> Enterprise Manager (v 2.0) with the SQL Enterprise Manager (v 8.0) (runnin
g
> on XP SP2 hitting a SQL Server 2000 SP4 DB running on W2k3 Server) and go
to
> design view, add my tiny little field (this time an int, default 0), click
> Apply, click Yes Please (actually OK), and then, lo and behold, I get this
> little gem:
> 17310 :
> SqlDumpExceptionHandler: Process 848 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> No further access via EM or QA at that point.
> So I stop the SQLService on the server, count to 10 and sprinkle a little
> pixie dust about, restart the SQLService, and voila! sometimes my field i
s
> in the table, sometimes it isn't, and if it isn't, I add it in, click Appl
y,
> click Yes Please, and double-viola! It works like a charm - then, for the
> rest of the day, all is well (can add fields, update sprocs, modify trigge
rs,
> you-name-it).
> Any thoughts and suggestions would be appreciated. Haven't tried it via Q
A,
> though, next time I think I'll give 'er a whirl.
> Thanks
>Simply ridiculous.
ML
http://milambda.blogspot.com/