Figures
Figure 2
Figure 2
Figure 2 Pause, Continue, and Stop the Service
Public Sub SQLServiceManager(NewState as Int)
    Dim oSQLServer As SQLDMO.SQLServer
    Set oSQLServer = New SQLServer
    oSQLServer.Name = "(local)"

    On Error GoTo errHandler

    Select Case Index
        Case 1 ' start
            If oSQLServer.Status = SQLDMOSvc_Stopped Then
                oSQLServer.Start False
            Else
                MsgBox "Cannot start server, server is either" _
                       & "running or paused", vbOKOnly, _
                "SQL Server Service Manager"
            End If
        Case 2 ' pause / continue
            If oSQLServer.Status = SQLDMOSvc_Paused Then
                oSQLServer.Continue
            ElseIf oSQLServer.Status = SQLDMOSvc_Running Then
                oSQLServer.Pause
            Else
                MsgBox "Cannot pause a server which is running", _
                       & "vbOKOnly, _
                "SQL Server Service Manager"
            End If
        Case 3 ' stop
            If oSQLServer.Status = SQLDMOSvc_Running Then
                oSQLServer.Stop
                ' gobjSQLServer.Shutdown will issue a "shutdown" TSQL
                ' command instead of using the service control manager
            Else
                MsgBox "Cannot stop a server which is not running", 
                       vbOKOnly, "SQL Server Service Manager"
            End If
    End Select
    
    Exit Sub
    
errHandler:
    MsgBox Err.Number & " " & Err.Description & " (" & Err.Source & " " _
        & Err.LastDllError & ")", _
    vbOKOnly, "SQL Server Service Manager"

End Sub
Figure 3 QueryResults Object to ADO Recordset
Function QueryResultToRecordset(qres As SQLDMO.QueryResults) _
    As ADODB.Recordset

    Dim qresString As String
    Dim rs As New ADODB.Recordset
    Dim rows() As String, cols() As String
    Dim rowIndex As Long, colIndex As Long
    
    ' get all the results as a long tab-delimited string
    ' use unusual col/row delimiter because some values may embed CRLFs
    qresString = qres.GetRangeString(, , , , Chr(2), Chr(1))
    ' the first row of the long string contains column headers
    rows() = Split(qresString, Chr(2))
    cols() = Split(rows(0), Chr(1))

    ' create recordset fields
    For colIndex = 0 To UBound(cols)
        rs.Fields.Append RTrim$(cols(colIndex)), adVarChar, _
            qres.ColumnMaxLength(colIndex + 1)
    Next
    
    ' now add all the rows
    rs.Open
    ' the second line, that is rows(1), contains a row of ———
    For rowIndex = 2 To UBound(rows)
        ' get individual columns
        cols() = Split(rows(rowIndex), Chr(1))
        ' add a new record
        rs.AddNew
        For colIndex = 0 To UBound(cols)
            rs.Fields(colIndex) = RTrim$(cols(colIndex))
        Next
        rs.Update
    Next
    
    ' return to caller
    Set QueryResultToRecordset = rs
    
End Function
Figure 4 Displaying QueryResults

Figure 4 Displaying QueryResults
Figure 6 List Tables and Columns in the Pubs Database
' assumes that SQLServer is connected
' to a SQL Server system
Dim db As SQLDMO.Database
Dim tbl As SQLDMO.Table
Dim col As SQLDMO.Column

Set db = SQLServer.Databases("pubs")
For Each tbl In db.Tables
    Print "TABLE " & tbl.Name
    For Each col In tbl.Columns
        Print "    " & col.Name
    Next
Next
Figure 7 Generating T-SQL Script for Tables
' generate the T-SQL script for all the tables in Pubs
' including their inner objects
Dim tbl As SQLDMO.Table
Dim allParams As Long, sql As String

' these constants are defined in DMO type library
allParams = SQLDMOScript_Default Or SQLDMOScript_Indexes 
    Or SQLDMOScript_DRI_AllConstraints Or _
    SQLDMOScript_Triggers Or SQLDMOScript_DRI_ForeignKeys
For Each tbl In pubsDB.Tables
    sql = sql & tbl.Script(param)
Next
Figure 8 Copy the Table Structure to a New Table
' To create a script for a table with a different name use
' the NewName parameter in the Script method like this

Dim oSQLServer As SQLDMO.SQLServer
Set oSQLServer = New SQLDMO.SQLServer

oSQLServer.LoginSecure = True
oSQLServer.Connect "(local)"

Dim oTable As SQLDMO.Table
Set oTable = oSQLServer.Databases("pubs").Tables("sales")

Dim sqlText As String
Dim ScriptOptions1 As SQLDMO.SQLDMO_SCRIPT_TYPE
Dim ScriptOptions2 As SQLDMO.SQLDMO_SCRIPT2_TYPE

ScriptOptions1 = SQLDMOScript_Default + _
                 SQLDMOScript_OwnerQualify
ScriptOptions2 = SQLDMOScript2_Default

sqlText = oTable.Script(ScriptOptions1, , "New Sales", ScriptOptions2)
    
' need to execute in the context of the database pubs
' to resolve the user-defined datatypes used in the sales table
' and there is no "use pubs" statement part of the sqlText
'
oSQLServer.Databases("pubs").ExecuteImmediate sqlText

' to execute from a server context remove the database references
' which in this example will cause an error because of the miss
' oSQLServer.ExecuteImmediate sqlText

oSQLServer.DisConnect

Set oSQLServer = Nothing
Figure 10 Check the Database and Generate a Log File
Dim SQLJob As New SQLDMO.Job

' —— create and init the Job object
SQLJob.Name = "Northwind Backup"
SQLJob.Description = "Check and Backup Northwind"
' add to the Jobs collection
SQLServer.JobServer.Jobs.Add SQLJob

' —— 1st step: check the database
Dim aJobStep As SQLDMO.JobStep
Set aJobStep = New SQLDMO.JobStep
aJobStep.Name = "Step 1: Check Northwind"
aJobStep.StepId = 1
aJobStep.SubSystem = "TSQL"
aJobStep.DatabaseName = "Northwind"
aJobStep.Command = "DBCC CHECKDB ('Northwind', REPAIR_FAST)
aJobStep.OutputFileName = "c:\temp\job1.tmp"
' if DBCC CHECKDB fails, jump to 3rd step
aJobStep.OnFailureStep = 3
aJobStep.OnFailureAction = SQLDMOJobStepAction_GotoStep

' —— 2nd step: back up the database
' you can reuse the JobStep object
Set aJobStep = New SQLDMO.JobStep
' decide where backup files must go
bakdir = SQLServer.Registry.SQLDataPath & "\backup"
aJobStep.Name = "Step 2: Backup Northwind"
aJobStep.StepId = 2
aJobStep.SubSystem = "TSQL"
aJobStep.DatabaseName = "Northwind"
aJobStep.Command = "DBCC CHECKDB ('Northwind', REPAIR_FAST)
aJobStep.Command = "BACKUP DATABASE [Northwind] TO DISK = '" _
    bakdir & "\Northwind.bak' "
' note that we use a different log file, 
' not to overwrite output from Step 1
aJobStep.OutputFileName = "c:\temp\job2.tmp"
' we go to next step in either case
aJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoNextStep
aJobStep.OnFailureAction = SQLDMOJobStepAction_GotoNextStep

' —— 3rd step: use a script create the definitive log file
Set aJobStep = New SQLDMO.JobStep
aJobStep.Name = "Gather output and delete tmp files"
aJobStep.StepID = 3
aJobStep.SubSystem = "ActiveScripting"
' when using a script, the DatabaseName 
' property is the language name
aJobStep.DatabaseName = "VBScript"
        
' create the script
Dim Script As String
Script = "Dim fso, stepId, file, text, filename" & vbCrLf & _
    "Set fso = CreateObject(""Scripting.FileSystemObject"")" & vbCrLf & _
    "' gather text from all temporary files and delete them '" & vbCrLf & _
    "For stepID = 1 To 2" & vbCrLf & _
    "    filename = ""c:\temp\job"" & CStr(stepID) & "".tmp""" & vbCrLf & _
    "    Set file = fso.OpenTextFile(filename, 1, True, True)" & vbCrLf & _
    "    text = text & file.ReadAll" & vbCrLf & _
    "    file.Close" & vbCrLf & _
    "    fso.DeleteFile filename" & vbCrLf & _
    "Next" & vbCrLf & _
    "Set file = fso.CreateTextFile(""" & txtFile & """, True)" & vbCrLf
' conclude the script - note that Job scripts
' MUST explicitly set all the objects to nothing
Script = Script & _
    "file.WriteLine ""Output from '" & SQLJob.Name _
    & "' job at "" & Now()" & vbCrLf & _
    "file.WriteLine String(40, ""-"")" & vbCrLf & _
    "file.Write text" & vbCrLf & _
    "file.Close" & vbCrLf & vbCrLf & _
    "Set fso = Nothing" & vbCrLf & _
    "Set file = Nothing" & vbCrLf

' assign to the JobStep            
aJobStep.Command = Script

' in all cases, the job quits here
aJobStep.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess
aJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
SQLJob.JobSteps.Add aJobStep

'——— apply the job
' set the first step
SQLJob.StartStepID = 1
' apply to local SQL server
SQLJob.ApplyToTargetServer "(local)"
Figure 11 Schedule a Job
' get a reference to the job to be scheduled
Dim SQLJob As SQLDMO.Job
Set SQLJob = SQLServer.JobServer.Jobs("Northwind Backup")

' a new JobSchedule for daily backup
Dim SQLSchedule As New SQLDMO.JobSchedule
SQLSchedule.Name = "Daily Backup"
SQLSchedule.Schedule.FrequencyType = SQLDMOFreq_Daily
SQLSchedule.Schedule.FrequencyInterval = 1
' start on October 18, 2000 - at 23.55
SQLSchedule.Schedule.ActiveStartDate = 20001018
SQLSchedule.Schedule.ActiveStartTimeOfDay = 235500
' this schedule has no end time or end date (they might be omitted)
SQLSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
SQLSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME

' add the schedule to the Job
SQLJob.BeginAlter
SQLJob.JobSchedules.Add SQLSchedule
SQLJob.DoAlter
Page view tracker