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