By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

Executing a job using TSQL or Stored procedure on SQL 2005 Express

P: n/a
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

Nov 21 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 21 '08 #2

P: n/a
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
Nov 21 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.