Scheduling a vbscript

  • SeeCoolGuy

    SSCrazy

    Points: 2902

    I have the following script (see below) that I can run by double-clicking it on the SQL Server's Host (ie, ProdServer) I get two pieces of information from this, first it writes out a txt file to a webserver where we serve it up, when a server stops responding (ie, does not write out it's file) then the webserver alerts critical people (SysAdmin and Dba (Me)), the second part collects all drive information and places it into a 3rd sql server for a graphical representation. This allows us to watch space growth and can view the online charts to see what the prediction path is. (ie, 3 months before we run out of space ,etc...).

    but this code will not run from a job in sql server giving me the only error message that I can understand of "The command script does not destroy all the objects that it creates. Revise the command script. (Microsoft SQL Server, Error: 14277)"

    any ideas?

    '==========================================================================

    '

    ' NAME: DrivemonClient.vbs

    '

    ' AUTHOR: joe

    ' DATE : 9/26/2006

    '

    ' COMMENT:

    ' This script reports the drive usage of all fixed drives on the system ' it is run. The report will be posted to Const URL '

    '==========================================================================

    ' Constants for drive types

    Const Unknown = 0

    Const Removable = 1

    Const Fixed = 2

    Const Remote = 3

    Const CDROM = 4

    Const RAMDisk = 5

    dim svr

    ' general constants

    'use blat here or on server

    'Const MailServer = "127.0.0.1"

    'Const MailServerPort = "25"

    Const URL = "http://ws.PRODcnc.net/drivemon.asp?Drivedata="

    '====================================================================================

    ' Begin main code

    '====================================================================================

    on error resume next

    str = ""

    set oFs = WScript.CreateObject("Scripting.FileSystemObject")

    set oDrives = oFs.Drives

    svr = "(PROD) " & GetCurrentComputerName ' get name only once for performance reasons for each oDrive in oDrives

    for each oDrive in oDrives

    Select case oDrive.DriveType

    Case Fixed

    str = str & svr & _

    "|" & oDrive.DriveLetter & _

    "|" & oDrive.TotalSize & _

    "|" & oDrive.FreeSpace

    End Select

    if err.number = 0 then postdata str

    str=""

    next

    set oFs = Nothing

    set oDrives = Nothing

    set str = nothing

    dim txtFile

    dim mfile

    'EDIT WHERE TO WRITE THE FILE AND Server Name

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    mfile = "\\PRODinet\PRODinet_d_root\PRODinet\SqlCheckPROD\" & svr & ".txt"

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Dim fso

    set fso = createobject("Scripting.FileSystemObject")

    Set txtFile = fso.OpenTextFile(mfile, 2, True)

    txtFile.WriteLine now() & " | " & "Server: " & svr

    txtFile.Close

    Set txtFile = Nothing

    Set fso = Nothing

    'if err.number = 0 then postdata str

    ''''''''''''''''''''''''''''''''''''''''

    ' post to a page that stores the data

    ''''''''''''''''''''''''''''''''''''''''

    sub postData(DriveInfo)

    'msgbox DriveInfo

    Set WshShell = WScript.CreateObject("WScript.Shell")

    Set http = CreateObject("Microsoft.XmlHttp")

    http.open "GET", URL & driveinfo, FALSE

    http.send ""

    'msgbox http.responseText

    set WshShell = nothing

    set http = nothing

    end sub

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    ' get current computer name (from system environment variables) ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Function GetCurrentComputerName

    set oWsh = WScript.CreateObject("WScript.Shell")

    set oWshSysEnv = oWsh.Environment("PROCESS")

    GetCurrentComputerName = oWshSysEnv("COMPUTERNAME")

    set oWsh = Nothing

    set oWshSysEnv = Nothing

    End Function

    -- Francisco

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    I found an interesting thread on another site: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9283

    The solution here was to run the script in a DTS package and then schedule the DTS package.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • SeeCoolGuy

    SSCrazy

    Points: 2902

    I found that too and seems to work well for SS2000 but for SS2005, how do schedule an SSIS

    -- Francisco

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    Do a search on this site for "Schedule SSIS package".  There are several articles that describe how to do it.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • John Dodge

    Valued Member

    Points: 63

    ActiveX scripts in SQL Job appear to have a major bug in that they report Error: 14277 when ever the string "createobject(" appears more than once anywhere in the script.  It does not matter whether the string is just part of a character expression such as:  sTemp = "..... createobject( ...."; whether it appears in a comment or whether it is used to actually create an object.  Any combination of the above that puts "createobject(" in the script more than once will cause the 14277 error to appare when you try to close the Job modifier.

    There is a trick that I have found to overcome this.  That is to create any and all objects in a single common sub routine.  Even in this sub routine, you have to trick the system into thinking that you have just destroyed the object that you are trying to create.

    The sub routine is cobj.  It takes the variable that will become the object and a string that defines the activex control.  The "set ... = Nothing" that appears after the "Exit Sub" is the trick that makes the system think that the object is destroyed within the scope of cobj.  Note: be sure to destroy the object in the scope where the object variable was defined.

    Here is code sample that sends an email using ASPMAIL, which contains data from a SQL query.

    MailMe readSQL(1006), "mymail@mail.com"

    Sub cobj(newobj, ax)

       Set newobj = createobject(ax)

       exit sub

       Set newobj = Nothing

    End Sub

    sub MailMe (sMsg, sAddress)

       dim Mailer, vRet

       if instr(sAddress,"@")<1 then exit sub

      

       cobj Mailer, "SMTPsvg.Mailer"

      

       Mailer.FromName = "ASP_Debug"

       Mailer.FromAddress = sAddress

       Mailer.RemoteHost = "127.0.0.1"

       Mailer.AddRecipient "", sAddress

       Mailer.Subject = "Debug ActiveX Script - 14277 Error"

       Mailer.BodyText = sMsg

       Mailer.SendMail

       Set Mailer=Nothing

    end sub

    Function readSQL(ndx)

       Dim SQL, sConn, oRst

       readSQL = "No Record"

       SQL = "SELECT Note FROM NoteTable WHERE [ID]=" & CStr(ndx)

       sConn = "Provider=SQLOLEDB.1;Initial Catalog=xx;Data Source=zz"

      

       cobj oRst, "ADODB.Recordset"

      

       oRst.Open SQL, sConn

       If oRst.State = 1 Then

          readSQL = oRst(0)

          oRst.Close

       End If

       Set oRst = Nothing

    End Function

     

  • Steve Marak

    SSC Veteran

    Points: 220

    This trick works GREAT, and best of all it will work just fine on versions of SQL Server that don't exhibit this bizarre behavior, too. Thanks!!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply