Job Scripting Error

  • Hi,

    I am using this script to script all the job on my SQL server :

    -------------------------------------------------

    Dim conServer

    Dim fso

    Dim iFile

    Dim oJB

    Dim strJob

    Dim strFilename

    Const ioModeAppend = 8

    Set conServer = CreateObject("SQLDMO.SQLServer")

    conServer.LoginSecure = True

    conServer.Connect "MySQLServer\Instance3"

    strFilename = "\\Someserver\apps\253\SQLServer\jobs\Filename.JOBS"

    For Each oJB In conServer.JobServer.Jobs

        strJob = strJob & "--------------------------------------------------" & vbCrLf

        strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf

        strJob = strJob & "--------------------------------------------------" & vbCrLf

        strJob = strJob & oJB.Script() & vbCrLf

    Next

    Set conServer = Nothing

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set iFile = fso.CreateTextFile(strFilename, True)

    iFile.Write (strJob)

    iFile.Close

    Set fso = Nothing

    --------------------------------------

    I get the following error :

    Executed as user: Domain\DomainUID. Error Code: 0  Error Source= Microsoft VBScript runtime error  Error Description: Invalid procedure call or argument    Error on Line 24.  The step failed.

    This very same code works for other instalnces. Just for this one instance I am getting this error. Any help is appreaciated.

    It is a MS SQL 2000 Server with SP3a ( 8.00.818 ) on Windows NT 5.2 (Build 3790: ).

    Thanks.

     

  • are you member of the sysadm-sqlserver-role on the instance where it fails ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Yes, I am a sysadm on all the instances on that box. It works on instance1 , instance2 and instance4.

    I tried connecting as 'SA' but still getting the same thing.

    Thanks.

  • this what I use with VB6 sqldmo :

    I hope this can help sort things out

    Private Sub ScriptJobs()

        On Error GoTo ErrorHandler

       

        strStatementId = "ScriptJobs-" & strServerNaam

       

        'melden dat ik met jobscripting bezig ben

        blnScriptingJobs = vbTrue

       

        If blnDefaultTargetFile = vbTrue Then

          'bij default-settings worden ddl en jobs in aparte files gescript.

          strStatementId = "ScriptJobs - CloseScriptFile-" & strServerNaam

          Call CloseScriptFile

          strStatementId = "ScriptJobs - InitJobFile-" & strServerNaam

          Call initJobFile

       

        End If

       

        ' current DB op master zetten

        'use db

        WriteScriptPart (" use Master -- gebruik MasterDB" & vbNewLine & "GO " & vbNewLine)

       

        Dim bitmapScript1 As SQLDMO_SCRIPT_TYPE

        Dim bitmapscript2 As SQLDMO_SCRIPT_TYPE

        Dim sScript As String

       

        For Ix = 1 To MaxIxDB

            'set the bitmap

            If Mid$(arrstrDBScriptType(Ix, 1), 1, 1) >= "K" Then

                bitmapScript1 = bitmapScript1 Or CLng(Mid$(arrstrDBScriptType(Ix, 1), 2))

            Else

                bitmapscript2 = bitmapscript2 Or CLng(Mid$(arrstrDBScriptType(Ix, 1), 2))

            End If

       

       

        Next Ix

       

        'Alles loggen ?

        Logpunt ("Jobs")

       

        strStatementId = "ScriptJobs - JobServer-" & strServerNaam

       

        'script de database

        Dim oJobserver As SQLDMO.JobServer

        'Get the database

        Set oJobserver = globDMOCnn.JobServer

       

        WriteScriptPart ("-- ALZSQLScripts: Jobsystem --")

       

        WriteScriptPart ("-- ALZSQLScripts: Jobsystem - Sysmessages (> 49999) --")

       

        Dim oDatabase As SQLDMO.Database2

        Set oDatabase = globDMOCnn.Databases("Master")

           

        Dim strSQLstmt As String

        strSQLstmt = "select 'exec sp_addmessage  @msgnum = ' + convert(varchar(10),error) + ', @severity = ' + convert(varchar(10),severity) + '  , @msgtext = ''' + description + ''' , @with_log = ''true'' ' + char(13) + 'GO' " & _

            "  From master.dbo.sysmessages Where Error > 49999 order by error "

        Dim oRsMessages As SQLDMO.QueryResults2

        'Set oRsMessages = db.ExecuteWithResults("select name as naam from sysfiles") ' EnumCandidateKeys

       

        Set oRsMessages = oDatabase.ExecuteWithResults(strSQLstmt)

        If oRsMessages.ResultSets = 1 Then

            'verwerken usermessages

            For Ix = 1 To oRsMessages.Rows

                'en nu scripten maar ...

                sScript = oRsMessages.GetColumnString(Ix, 1)

                WriteScriptPart (sScript)

            Next

        End If

        Set oRsMessages = Nothing

        Set oDatabase = Nothing

       

        WriteScriptPart ("-- ALZSQLScripts: Jobsystem - Alerts --")

        strStatementId = "ScriptJobs - Jobsystem - Alerts " & strServerNaam

        'Script de Alerts

        'blijkbaar zitten ALLE Alerts in één collection, en worden ze steeds volledig gescript

            sScript = oJobserver.Alerts.Script

            WriteScriptPart (sScript)

        'Next

       

        WriteScriptPart ("-- ALZSQLScripts: Jobsystem - Operators --")

        strStatementId = "ScriptJobs - Jobsystem - Operators " & strServerNaam

        'Script de Operators

        'blijkbaar zitten ALLE Operators in één collection, en worden ze steeds volledig gescript

            sScript = oJobserver.Operators.Script

            WriteScriptPart (sScript)

        'Next

       

        WriteScriptPart ("-- ALZSQLScripts: Jobsystem - Jobs --")

        strStatementId = "ScriptJobs - Jobsystem - Jobs " & strServerNaam

        'script de Jobs

        'Dim oJob As SQLDMO.Job

        'For Each oJob In oJobserver.Jobs

        'blijkbaar zitten ALLE jobs in één collection, en worden ze steeds volledig gescript

            sScript = oJobserver.Jobs.Script

            WriteScriptPart (sScript)

        'Next

        strStatementId = "ScriptJobs - end -" & strServerNaam

        Exit Sub

       

    ErrorHandler:

       

        If blnCommandlineAction = vbFalse Then

            MsgBox "*ScriptJobs ging mis. Reden: " & Err.Number & "  " & Err.Description, vbExclamation

        Else

            ' Loggen van de fout in de locale EventLog

            '

            LogError ("Last statement : " & strStatementId)

            '

            'error doorgeven aan oproepende routine !

            Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext

           

        End If

       

    End Sub

    Private Sub InitScriptTypeDB()

        strStatementId = "InitScriptTypeDB"

       

            Ix = 1

           

            arrstrDBScriptType(Ix, 1) = "K" & SQLDMOScript_DatabasePermissions

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript_DatabasePermissions"

            Ix = Ix + 1

           

            arrstrDBScriptType(Ix, 1) = "K" & SQLDMOScript_Default

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript_Default"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "K" & SQLDMOScript_IncludeHeaders

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript_IncludeHeaders"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "K" & SQLDMOScript_ObjectPermissions

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript_ObjectPermissions"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "K" & SQLDMOScript_OwnerQualify

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript_OwnerQualify"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "K" & SQLDMOScript_Permissions

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript_Permissions"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "J" & SQLDMOScript_PrimaryObject

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript_PrimaryObject"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "K" & SQLDMOScript_UseQuotedIdentifiers

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript_UseQuotedIdentifiers"  '14

            Ix = Ix + 1

       

            If blnSQL7Scripting = vbTrue Then

                arrstrDBScriptType(Ix, 1) = "A" & SQLDMOScript2_70Only

                arrstrDBScriptType(Ix, 2) = "SQLDMOScript2_70Only"

                Ix = Ix + 1

            End If

            arrstrDBScriptType(Ix, 1) = "A" & SQLDMOScript2_AgentAlertJob

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript2_AgentAlertJob"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "A" & SQLDMOScript2_AgentNotify

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript2_AgentNotify"

            Ix = Ix + 1

       

            arrstrDBScriptType(Ix, 1) = "A" & SQLDMOScript2_Default

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript2_Default"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "A" & SQLDMOScript2_ExtendedProperty

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript2_ExtendedProperty"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "A" & SQLDMOScript2_FullTextCat

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript2_FullTextCat"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "A" & SQLDMOScript2_FullTextIndex

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript2_FullTextIndex"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "A" & SQLDMOScript2_JobDisable

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript2_JobDisable"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "A" & SQLDMOScript2_MarkTriggers

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript2_MarkTriggers"

            Ix = Ix + 1

            arrstrDBScriptType(Ix, 1) = "B" & SQLDMOScript2_UnicodeFile

            arrstrDBScriptType(Ix, 2) = "SQLDMOScript2_UnicodeFile"

            Ix = Ix + 1

       

            'bewaren aantal entries

            MaxIxDB = Ix - 1

           

                 

                 

    End Sub

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply