Issues with Microsoft.SqlServer.Smo C# Job.EnumHistory()

Question

All-

I am getting a C# Exception:

"Enumerate history failed for Job 'xxxxxxx'. "

on line:

at Microsoft.SqlServer.Management.Smo.Agent.Job.EnumHistory(JobHistoryFilter filter)

What is strange is that I am unable to consistently reproduce this error. It happens every few days on a service I'm using to monitor a SQL Server Agent Job.

Using Powershell command:

$v = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

I have confirmed we are using Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91.

We are using Microsoft SQL Server 2008 R2.

What causes this error?

Any help is appreciated.

Thanks,

-Kevin

Answer 1

Hello Kevin,

Can you try the following power shell script and let us know if you are seeing same issue?

http://blogs.msdn.com/b/sqlagent/archive/2011/11/11/gathering-failed-job-history-using-power-shell.aspx

Thanks

Sethu  Srinivasan  [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent

Answer 2

Sethu-

I ran this script successfully for the job that is throwing an exception. There was no output from the script for this job.

-Kevin

Answer 3

Any other suggestions out there?

Answer 4

bump

Answer 5

Hello,

Have you looked at the contain of the script ? It is written in PowerShell with a call to EnumHistory. What is the value of the parameter of the EnumHistory ?

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 6

Kevin, Could you please post code sample in this forum?

Thanks

Sethu Srinivasan [MSFT]

SQL Server

Answer 7

Kevin, Could you please post code sample in this forum?

Thanks

Sethu Srinivasan [MSFT]

SQL Server

/// <summary>
        /// Get last job result of the Sql Server Monitor.
        /// </summary>
        /// <returns></returns>
        public IMonitorResult GetJobHistory()
        {
            const string connectionNotOpen = "Sql Server Connection is not open";
 
            int successfulJobs = 0;
            int failedJobs = 0;
 
            Job job;
            IList<string> jobRunDescriptions = new List<string>();
 
            //IMonitorResult monitorResult = null;
 
            if (_serverConnection.IsOpen)
            {
                string[] monitoredJobs = _sqlserverAgentMonitor.MonitorJobs.Split(',');
 
                DataTable jobHistory;
 
                foreach (string monitorJob in monitoredJobs)
                {
                    // Find the job on SQLServer.
                    job = _server.JobServer.Jobs[monitorJob.Trim()];
 
                    if (job != null)
                    {
                        if (!job.IsEnabled)
                        {
                            _thresholdResult = ThresholdType.Warning;
                            jobRunDescriptions.Add(Msg.ErrorJobDisabled + monitorJob);
                            failedJobs++;
                        }
                        else if (CheckJobRan(job))
                        {
                            // Create job filter to retrieve job history.
                            JobHistoryFilter jobHistoryFilter = new JobHistoryFilter
                                                                    {
                                                                        JobID = job.JobID,
                                                                        OldestFirst = false,
                                                                        JobName = monitorJob
                                                                    };
 
                            // Get job history.
                            jobHistory = job.EnumHistory(jobHistoryFilter);
 
                            jobRunDescriptions.Add(jobHistory.Rows[0]["JobName"] + ":" + jobHistory.Rows[0]["Message"] +
                                                   Environment.NewLine);
 
                            if (job.LastRunOutcome != CompletionResult.Succeeded)
                            {
                                _thresholdResult = ThresholdType.Critical;
                                failedJobs++;
                            }
                            else
                            {
                                successfulJobs++;
                            }
                        }
                        else
                        {
                            _thresholdResult = ThresholdType.Warning;
                            jobRunDescriptions.Add("Job did not run at the scheduled time: " + monitorJob);
                            failedJobs++;
                        }
                    }
                    else
                    {
                        _thresholdResult = ThresholdType.Critical;
                        jobRunDescriptions.Add("Job not found:" + monitorJob);
                        failedJobs++;
                    }
                }
 
                int totalJobs = failedJobs + successfulJobs;
                _monitorResult = new MonitorResultImpl(_sqlserverAgentMonitor, _thresholdResult, 0,
                                                       Environment.NewLine + "Total Jobs: " + totalJobs
                                                       + Environment.NewLine + "Failed Jobs: " + failedJobs
                                                       + Environment.NewLine + "Successful Jobs: " + successfulJobs
                                                       + (jobRunDescriptions.Any() ? Environment.NewLine + string.Join(Environment.NewLine, jobRunDescriptions.ToArray()) : string.Empty));
 
                return _monitorResult;
            }
            _monitorResult = new MonitorResultImpl(_sqlserverAgentMonitor,
                                                   ThresholdType.Critical,
                                                   0, connectionNotOpen);
            return _monitorResult;
        }

Answer 8

Kevin, Could you please post code sample in this forum?

Thanks

Sethu Srinivasan [MSFT]

SQL Server

I am definitely passing in the required job information. The code is working as expected except for on this machine.

Answer 9

This seems to only be happening on a few select machines. It is working as expected in most places. Everything I see though says that I am running the 10.x DLL.

Answer 10

What about dependent DLLs? Can you verify that you are loading the correct version of Microsoft.SqlServer.SqlEnum?

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 11

What about dependent DLLs? Can you verify that you are loading the correct version of Microsoft.SqlServer.SqlEnum?

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Version 10.x of the above DLL is being loaded

Confirmed with: $v = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlEnum')

in powershell.

Answer 12

Please verify the following too and compare to what is being loaded in the machines that do work:

Microsoft.SqlServer.Management.Sdk.Sfc.dll

Also, is what you wrote in your original post all you have for the exception?

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 13

Please verify the following too and compare to what is being loaded in the machines that do work:

Microsoft.SqlServer.Management.Sdk.Sfc.dll

Also, is what you wrote in your original post all you have for the exception?

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Monica-

I will look into this DLL too. What I have in the original post is the whole exception minus some of the stack trace.

-Kevin

Answer 14

Please verify the following too and compare to what is being loaded in the machines that do work:

Microsoft.SqlServer.Management.Sdk.Sfc.dll

Also, is what you wrote in your original post all you have for the exception?

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Monica-

I will look into this DLL too. What I have in the original post is the whole exception minus some of the stack trace.

-Kevin

10.50.2500.0 is being used on the environment throwing this exception.

Answer 15

If you're loading 10.00.x of Microsoft.SqlServer.Smo.dll, this might be causing the problem.

I would suggest getting the right version to run your app.

See the the answer in this link: http://social.msdn.microsoft.com/Forums/en/sqlnetfx/thread/1d2cb76b-642f-4067-9667-aacdbc142f74

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.