Some things about SMO

SQL Server SMO/DMO

Nose77


Hey,

I have got two problems using SMO.

First I cannot start and stop the SQL Server Agent.

What property should I use I can only found

server.jobserver.restart but this didn't work.

Second Question is how to start a job from the server.jobserver.jobs.

Is it Job.start or Job.invoke I got an error message "Start failed for job "My Job" "

And how can I get the current status if a job is running, if it succeeded or failed.

I found: AJob.State which says that my job is idle and

AJob.LastRunOutcome which is false after I called AJob.start

Can anyone help

grtz,

Bart




Top

Re: Some things about SMO

Artur laksberg MSFT


Bart,

You can use classes from Wmi namespaces to Start/Stop SQL Server services. This example starts Agent service on the local default instance:

Code Snippet
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Wmi;
...
Service service = (new ManagedComputer()).Services["SQLSERVERAGENT"];
service.Start();


Job.Start is the right method to start a job.

A job might fail for different reasons. I would recommend trouble-shooting that via SSMS UI. Once you get your job to work there, you can then start it programmatically.

Hope that helps!






Top

Re: Some things about SMO

Nose77

Code Snippet

Dim mc As ManagedComputer

mc = New ManagedComputer("BCIP69")

Dim svc As Service

svc = mc.Services("SQLSERVERAGENT")

Try

If svc.ServiceState = ServiceState.Running Then

svc.Stop()

Do Until String.Format("{0}", svc.ServiceState) = "Stopped"

svc.Refresh()

Loop

Else

svc.Start()

Do Until String.Format("{0}", svc.ServiceState) = "Running"

svc.Refresh()

Loop

End If

Catch ex As Exception

MessageBox.Show("Exception")

End Try

Hi Artur,

Thanx for the reply. I have found the classed but I didn't managed to get the service started from code. My code runs into an exception.

I found out that there is also a readonly property service.AcceptsStop which is false, which says to me that the service cant be stopped @ all.

I looked at SQL Server to search for some parameter to set but didnt find anything. Sitting with my hands in my hair now.

thnx,

Bart






Top

Re: Some things about SMO

Papy Normand

Hello,

Which version of SQL Server are you using ( no Job Agent with SQL Server Express )

Have a nice day





Top

Re: Some things about SMO

Nose77

Hey,

I'm using SQL Server 2005.

thanx,

Bart





Top

Re: Some things about SMO

Nose77

Hey there,

I've logged in now with other credentials to SQL Server and the Accepsstop property is now true of the service but it still runs into an exception when service.stop is called.

This is the exception:

ex.InnerException {"StopService failed for Service 'SQLSERVERAGENT'."} System.Exception





Top

Re: Some things about SMO

Jens K. Suessmeyer

Which OS are you running, you will need administrative privilegdes or at least service control privilegdes to do this. Running VISTA might need to elevate the execution to an administrator account.

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---


Top