smo disbale job

10 Dec 2005 4:25 PM

B. Mark McKinney

In the following function a JobId is passed in that the user wants to
disable. The function then retrieves the job collection, finds the correct
job, sets the IsEnabled to false and then tries to update the job. This
results in this error:

Alter failed for Job <job.name>

Does anyone have a suggestion as to why Alter errors out. Also is there a
simpler way to do this without searching through the whole jobs collection?
It does not appear as though you can use the job filter to filter by JobId.

Thanks

   Public Shared Function DisableJob(ByVal JobId As Guid) As Boolean
        Dim RetVal As Boolean = False
        Dim srv As Server = CreateServer()
        Dim AllJobs As JobCollection
        Dim oJob As Job

        Try
        AllJobs = srv.JobServer.Jobs()
        For Each oJob In AllJobs
            If oJob.JobID = JobId Then
                oJob.IsEnabled = False
                oJob.Alter()
                RetVal = True
                Exit For
            End If
            Next
        Catch ex As Exception
            Throw
        End Try
        DisableJob = RetVal
    End Function

10 Dec 2005 10:18 PM

B. Mark McKinney

I am attempting to run this code on a stand alone laptop running XP SP2,
Visual Studio 2005, and SQL Server 2000 Developer Edition (SP4).

I have simplified the function to this:

    Public Shared Function DisableJob(ByVal JobName As String) As Boolean
        Dim RetVal As Boolean = False
        Dim srv As Server = CreateServer()
        Try
            srv.JobServer.Jobs(JobName).IsEnabled = True
            srv.JobServer.Jobs(JobName).Alter()
            RetVal = True
        Catch ex As SmoException
            Throw
        End Try
        DisableJob = RetVal
    End Function

When the Alter() method throws the error the inner exection reads as follows:

{System.IO.FileNotFoundException}
    System.IO.FileNotFoundException: {"Could not load file or assembly
'Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system
cannot find the file specified.":"Microsoft.SqlServer.BatchParser,
Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"}
    Data: {System.Collections.ListDictionaryInternal}
    HelpLink: Nothing
    InnerException: Nothing
    Message: "Could not load file or assembly
'Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system
cannot find the file specified."
    Source: "Microsoft.SqlServer.ConnectionInfo"
    StackTrace: "   at
Microsoft.SqlServer.Management.Common.ServerConnection.GetStatements(String
query, ExecutionTypes executionType, Int32& statementsToReverse)
   at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
   at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at
Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
   at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries)
   at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingOptions so)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplWorker()
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()"
    TargetSite: {System.Reflection.RuntimeMethodInfo}

It appears as though I am missing the 'BatchParser' assembly on my system
but am unsure if this is the problem nor what I need to do to correct the
situation if indeed that is the problem. I have looked on my other computers
and none has a dll called BatchParser, although there is a different version
of the file on a system running SQL Server 2005.