So I should be able to take the text from the VBScript job step and
save it to a vbs file, then run it using the cscript.exe?
Here's the text of the Job, step 2 :
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
@step_name=N'OutputInvoiceExportTextFiles',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=3,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'ActiveScripting',
@command=N'Dim oFilesys
Dim oFiletxt
Dim sFilename
Dim strPath
Dim sFullFileName
Dim strdbConnectString
Dim dbConnection
Dim rsOutput
Dim sqlCommand
Dim strDatabaseName
Dim sqlCommandGroup
Dim rsGroup
Dim sKey
Dim sValue
Dim FsoObject
Dim strSourceFile
Dim OpenFile
Dim strInputLine
Const cForReading = 1
Const cForWriting = 2
Const cForAppending = 8
Call LoadParameters()
Public Sub LoadParameters()
Set FsoObject = CreateObject("Scripting.FileSystemObject")
strSourceFile = "C:\Program Files\TSM105\EDIConfigSettings.txt"
If (FsoObject.FileExists(strSourceFile)) Then
Set OpenFile = FsoObject.OpenTextFile(strSourceFile, cForReading)
Do Until Mid(strInputLine, 1, 15) = "[CreateInvoice]"
strInputLine = OpenFile.ReadLine
Loop
Do Until OpenFile.AtEndOfStream = "True"
strInputLine = OpenFile.ReadLine
If Mid(strInputLine, 1, 1) = "[" Then
Exit Do
End If
If Instr(strInputLine, "=") 1 Then
sKey = Ltrim(Rtrim(Left(strInputLine, instr(strInputLine,
"=") - 1)))
sValue = Ltrim(Rtrim(mid(strInputLine, instr(strInputLine,
"=") + 1, 255)))
End if
If sKey = "Path" then
strPath = sValue
ElseIf sKey = "dbConnectString" then
strdbConnectString = sValue
ElseIf sKey = "DatabaseName" then
strDatabaseName = sValue
End If
Loop
OpenFile.Close
Set FsoObject = nothing
Else
Err.Raise vbObjectError, "LoadParameters", "Error: " &
strSourceFile & " does not exist."
End If
End Sub
''set the select statement to retrieve each distinct Retailer Hub Code
sqlCommandGroup = "SELECT DISTINCT RetailerHubCode, InvcNum FROM [" +
strDatabaseName + "]..tmpEDIInvoiceExport WHERE RetailerHubCode IS NOT
NULL"
''create and open a connection to the sql server
Set dbConnection = CreateObject("ADODB.Connection")
dbConnection.Open = strdbConnectString
''open the recordset to copy into the text file
Set rsGroup = CreateObject("ADODB.Recordset")
rsGroup.Open sqlCommandGroup, dbConnection
If not (rsGroup.BOF) Then rsGroup.MoveFirst
''loop through each Retailer Hub Code
While (Not rsGroup.EOF)
''build the invoice number based file name using the Retailer
Hub Code as the file extension
sFileName = "IN" + rsGroup("InvcNum") + "." +
rsGroup("RetailerHubCode")
''combine the path and filename
sFullFileName = strPath + sFileName
''use a file system object to create a text file
Set oFilesys = CreateObject("Scripting.FileSystemObject")
Set oFiletxt = oFilesys.CreateTextFile(sFullFileName, True)
''set the select statement to retrieve all records for the
current Retailer Hub Code
sqlCommand = "SELECT CombinedFields FROM [" + strDatabaseName
+ "]..tmpEDIInvoiceExport WHERE RetailerHubCode = ''" +
rsGroup("RetailerHubCode") + "'' AND InvcNum = ''" +
rsGroup("InvcNum") + "''"
''open the recordset to copy into the text file
Set rsOutput = CreateObject("ADODB.Recordset")
rsOutput.Open sqlCommand, dbConnection
If not (rsOutput.BOF) Then rsOutput.MoveFirst
''write each record into the text file
While (Not rsOutput.EOF)
oFiletxt.Write (rsOutput("CombinedFields") + VbCrLf)
rsOutput.MoveNext
Wend
rsOutput.Close
oFiletxt.Close
set rsOutput = nothing
set oFiletxt = nothing
set oFileSys = nothing
rsGroup.MoveNext
Wend
rsGroup.Close
dbConnection.Close
set dbConnection = nothing
set rsGroup = nothing',
@database_name=N'VBScript',
@flags=0
I should take the text in the @command line and move that out to a vbs
file, right?
On Fri, 21 Nov 2008 07:42:46 -0700, Tom van Stiphout
<to*************@cox.netwrote:
>On Fri, 21 Nov 2008 00:43:10 -0500, Carlton Kirby
<ckirbyATmindspringdottcomwrote:
Jobs seem tightly related to SQLAgent. So you either upgrade, or have
someone with VBScript skills take a look at those scripts.
If a VBScript was in a file, it could be executed like this:
cscript.exe c:\my.vbs
Scripts can extract data from a SQLServer, e.g. using ADO.
You may also be able to export the data some other way, e.g. BCP.
-Tom.
Microsoft Access MVP
>>I need to execute a job on a SQL Express 2005 instance (no SQLAgent).
The job will be executed manually by a user, so it doesn't need to be
scheduled to run automatically. I thought I could execute the job
through a stored procedure, but it appears that SQL Agent is necessary
even for that.
The job was given to me by a software vendor to add EDI capabilities
to an accounting package, but the assumption was made that a full
version of SQL was available.
Is there another way to execute a job on an Express instance? The job
has 4 steps. Two are just executing stored procedures, which I could
easily do via another stored procedure. But the other two steps are
Activescripting/VBScript ( @subsystem=N'ActiveScripting') and involve
creating text files and starting a ftp session to transfer them... And
that's a bit beyond me..
Thanks
