SMO and CurrentRunStatus Always Idle

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 2008 Forums

Other SQL Server 2008 Topics

SMO and CurrentRunStatus Always Idle

Author

Topic

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-11-09 : 13:48:44

I am writing a program that should return the status of a SQL Server job. I'm using SMOs for this task and I am having issues with it.

This is SQL Server 2008 Developer Edition running on my local system. All the SQL service (agent/db engine/etc) accounts are running under my domain account. My domain account is also a sysadmin on the instance.

I have a WCF service that starts the job and should also report on its current status. It always returns idle. I created a SQL Server account and added it as a sysadmin and gave it access to msdb and added it to the SQLAgent roles in msdb. Same issue - always idle. I then wrote this PowerShells script to allow me to watch the job as I execute it inside SSMS.

I figured I would just write a simple C# console app that starts the job and tries to get the status. Same issue.


[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$server = new-object "Microsoft.SqlServer.Management.Smo.Server" "localhost"
$i = 25

while($i -gt 0)
{
	Start-Sleep -Seconds 1
	$server.JobServer.Jobs | ?{ $_.Name -match "Test Job" } | select Name,CurrentRunStatus,LastRunDate
	$i--
}

Still idle! The job is excuting properly. I see the results just fine. I just cannot in any way check the current status. Any suggestions?

===
http://www.ElementalSQL.com/

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-11-09 : 14:11:03

I just needed to call the refresh method on the job object.

===
http://www.ElementalSQL.com/

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-11-09 : 14:59:35

Here is the final version of the PoSh script:


[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$server = new-object "Microsoft.SqlServer.Management.Smo.Server" "localhost"
$job = $server.JobServer.Jobs["Test Job"]
$now = Get-Date
do
{
 Start-Sleep -Seconds 1
 $job | select Name,CurrentRunStatus,LastRunDate
 $job.Refresh()
}
while($job.LastRunDate -lt $now)
$job | select Name,CurrentRunStatus,LastRunDate

===
http://www.ElementalSQL.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources