Create SQL Server Job in VB

bwoods

I am trying to create a scheduled job in SQL Server using VB. The help and docs that I have is not up to much... But I am still searching. Anyone got any code samples that I could for this.

Thanks in advance.

Brendan Wodos

kayos

Hmm,

A good question at last firstly I've a few questions for you:
What SQL sever are you using 6.5 or 7
and
Are you using OLE/DMO?

Using DMO is fairly simple so try using it. i've used it a good bit so I'll look into it for you when I get the chance.

Kayos

bwoods

I am using DMO... or trying to use it. Have found some stuff but am going through it ... slowly.

Any code snippets would help.

Thanks

kayos

here is 3 examples from msdn just look up Creating SQL Server Agent Jobs (SQL-DMO)
to get the page

1. Add a job with a T-SQL step

' Table object used in iteration over Tables collection.

Dim oTable As SQLDMO.Table

  

Dim oJob As New SQLDMO.Job

Dim oJobStep As SQLDMO.JobStep

Dim idStep As Integer

  

' Create the SQL Server Agent job. Job will perform an update

' of all optimizer-supporting data distribution statistics.

oJob.Name = "Northwind_Statistics_Update"

oSQLServer.JobServer.Jobs.Add oJob

  

' Alter the job, adding job steps and setting starting step.

oJob.BeginAlter

  

' Each JobStep contains the Transact-SQL command batch

' updating statistics for a table.

idStep = 0

For Each oTable In oSQLServer.Databases("Northwind").Tables

    ' Only applies to user defined tables....

    If oTable.Attributes <> SQLDMOTabAtt_SystemObject Then

        Set oJobStep = New SQLDMO.JobStep

  

        idStep = idStep + 1

  

        oJobStep.Name = "Northwind_Statistics_Update_Step_" & idStep

        oJobStep.StepID = idStep

  

        oJobStep.DatabaseName = "Northwind"

        oJobStep.SubSystem = "TSQL"

  

        ' TSQL uses the [] syntax to quote table identifers.

        oJobStep.Command = "UPDATE STATISTICS [" & oTable.Name & _

            "] WITH FULLSCAN, NORECOMPUTE"

  

        ' Default logic. Amended below.

        oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure

        oJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoNextStep

  

        oJob.JobSteps.Add oJobStep

    End If

Next oTable

  

' Reset the logic flow for the last job step to indicate success.

oJob.JobSteps.ItemByID(idStep).OnSuccessAction = _

    SQLDMOJobStepAction_QuitWithSuccess

  

' Set the starting step for the job.

oJob.StartStepID = 1

  

' Alter the job.

oJob.DoAlter

2. Creating a Job Containing an Operating System Command

Dim oJob As New SQLDMO.Job

Dim oJobStep As New SQLDMO.JobStep

  

Dim strQuote As String

  

strQuote = Chr$(34)

  

' Create the SQL Server Agent job. Job will send a network

' popup message.

oJob.Name = "NetSend"

oSQLServer.JobServer.Jobs.Add oJob

  

' Alter the job, adding job steps and setting starting step.

oJob.BeginAlter

  

' The job is implemented using a single step.

oJobStep.Name = "NetSend_1"

oJobStep.StepID = 1

  

' Set the job step exucatable subsystem. For operating

' system command job steps, the subsystem is "CmdExec"

oJobStep.SubSystem = "CmdExec"

  

' Job step script is:

'

' Net Send SEATTLE1 "Now is the time for all good men " & _

'    "to come to the aid of the party."

oJobStep.Command = _

    "Net Send SEATTLE1 " & strQuote & _

    "Now is the time for all good men to come to the " & _

    "aid of the party." & strQuote

  

' Logic for a single-step job.

oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure

oJobStep.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess

  

oJob.JobSteps.Add oJobStep

  

' Set the starting step for the job.

oJob.StartStepID = 1

  

' Alter the job.

oJob.DoAlter

3.Creating a Job Containing an Active Script Command

Dim oJob As New SQLDMO.Job

Dim oJobStep As New SQLDMO.JobStep

  

Dim strNewLine As String

Dim strQuote As String

  

strNewLine = Chr$(13) & Chr$(10)

strQuote = Chr$(34)

  

' Create the SQL Server Agent job. Job will perform an update

' of all optimizer-supporting data distribution statistics.

oJob.Name = "Northwind_Statistics_Update_ActiveScript"

oSQLServer.JobServer.Jobs.Add oJob

  

' Alter the job, adding job steps and setting starting step.

oJob.BeginAlter

  

' Define the job's single step.

oJobStep.Name = "Northwind_Statistics_Update_ActiveScript_1"

oJobStep.StepID = 1

  

' Set the job step executable subsystem. For ActiveX Script

' job steps, the DatabaseName property records the script

' interpreter selected.

oJobStep.SubSystem = "ActiveScripting"

oJobStep.DatabaseName = "VBScript"

  

' Job step script is:

'

' Set oSQLServer = CreateObject("SQLDMO.SQLServer")

'

' oSQLServer.LoginSecure = True

' oSQLServer.Connect

'

' oSQLServer.Databases("Northwind").UpdateIndexStatistics

'

' oSQLServer.DisConnect

' Set oSQLServer = Nothing

  

oJobStep.Command = _

    "Set oSQLServer = CreateObject(" & _

    strQuote & "SQLDMO.SQLServer" & strQuote & ")"

  

oJobStep.Command = oJobStep.Command & strNewLine & strNewLine

  

oJobStep.Command = oJobStep.Command & _

    "oSQLServer.LoginSecure = True"

  

oJobStep.Command = oJobStep.Command & strNewLine

  

oJobStep.Command = oJobStep.Command & _

    "oSQLServer.Connect"

  

oJobStep.Command = oJobStep.Command & strNewLine & strNewLine

  

oJobStep.Command = oJobStep.Command & _

    "oSQLServer.Databases(" & strQuote & "Northwind" & _

    strQuote & ").UpdateIndexStatistics"

  

oJobStep.Command = oJobStep.Command & strNewLine & strNewLine

  

oJobStep.Command = oJobStep.Command & _

    "oSQLServer.DisConnect"

  

oJobStep.Command = oJobStep.Command & strNewLine

  

oJobStep.Command = oJobStep.Command & _

    "Set oSQLServer = Nothing"

  

oJobStep.Command = oJobStep.Command & strNewLine

  

' Logic for a single-step job.

oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure

oJobStep.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess

  

oJob.JobSteps.Add oJobStep

  

' Set the starting step for the job.

oJob.StartStepID = 1

  

' Alter the job.

oJob.DoAlter

hope this helps.

kayos

bwoods

Thanks KAYOS. Will try.

bwoods

Job is being created OK. + Schedule etc.

Getting a message in SQL Server that the Job is not runnable because (Add Target Server to Job)???

Any suggesstions.

bwoods

Got it going. Thanks a mill!