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'Ou tputInvoiceExpo rtTextFiles',
@step_id=2,
@cmdexec_succes s_code=0,
@on_success_act ion=4,
@on_success_ste p_id=3,
@on_fail_action =2,
@on_fail_step_i d=0,
@retry_attempts =0,
@retry_interval =0,
@os_run_priorit y=0, @subsystem=N'Ac tiveScripting',
@command=N'Dim oFilesys
Dim oFiletxt
Dim sFilename
Dim strPath
Dim sFullFileName
Dim strdbConnectStr ing
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("S cripting.FileSy stemObject")
strSourceFile = "C:\Program Files\TSM105\ED IConfigSettings .txt"
If (FsoObject.File Exists(strSourc eFile)) Then
Set OpenFile = FsoObject.OpenT extFile(strSour ceFile, cForReading)
Do Until Mid(strInputLin e, 1, 15) = "[CreateInvoice]"
strInputLine = OpenFile.ReadLi ne
Loop
Do Until OpenFile.AtEndO fStream = "True"
strInputLine = OpenFile.ReadLi ne
If Mid(strInputLin e, 1, 1) = "[" Then
Exit Do
End If
If Instr(strInputL ine, "=") 1 Then
sKey = Ltrim(Rtrim(Lef t(strInputLine, instr(strInputL ine,
"=") - 1)))
sValue = Ltrim(Rtrim(mid (strInputLine, instr(strInputL ine,
"=") + 1, 255)))
End if
If sKey = "Path" then
strPath = sValue
ElseIf sKey = "dbConnectStrin g" then
strdbConnectStr ing = sValue
ElseIf sKey = "DatabaseNa me" 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 + "]..tmpEDIInvoice Export WHERE RetailerHubCode IS NOT
NULL"
''create and open a connection to the sql server
Set dbConnection = CreateObject("A DODB.Connection ")
dbConnection.Op en = strdbConnectStr ing
''open the recordset to copy into the text file
Set rsGroup = CreateObject("A DODB.Recordset" )
rsGroup.Open sqlCommandGroup , dbConnection
If not (rsGroup.BOF) Then rsGroup.MoveFir st
''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("InvcNu m") + "." +
rsGroup("Retail erHubCode")
''combine the path and filename
sFullFileName = strPath + sFileName
''use a file system object to create a text file
Set oFilesys = CreateObject("S cripting.FileSy stemObject")
Set oFiletxt = oFilesys.Create TextFile(sFullF ileName, True)
''set the select statement to retrieve all records for the
current Retailer Hub Code
sqlCommand = "SELECT CombinedFields FROM [" + strDatabaseName
+ "]..tmpEDIInvoice Export WHERE RetailerHubCode = ''" +
rsGroup("Retail erHubCode") + "'' AND InvcNum = ''" +
rsGroup("InvcNu m") + "''"
''open the recordset to copy into the text file
Set rsOutput = CreateObject("A DODB.Recordset" )
rsOutput.Open sqlCommand, dbConnection
If not (rsOutput.BOF) Then rsOutput.MoveFi rst
''write each record into the text file
While (Not rsOutput.EOF)
oFiletxt.Write (rsOutput("Comb inedFields") + VbCrLf)
rsOutput.MoveNe xt
Wend
rsOutput.Close
oFiletxt.Close
set rsOutput = nothing
set oFiletxt = nothing
set oFileSys = nothing
rsGroup.MoveNex t
Wend
rsGroup.Close
dbConnection.Cl ose
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
<ckirbyATminds pringdottcomwro te:
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
Activescripti ng/VBScript ( @subsystem=N'Ac tiveScripting') and involve
creating text files and starting a ftp session to transfer them... And
that's a bit beyond me..
Thanks
