[dba-SQLServer] scheduling a vbscript

Yes, it runs from the desktop just fine... in fact I just decided to
schedule it via windows scheduler for now, but I broke it up so that the
part that writes out the file goes through the agent, this way I know that
sql server is online, and I still get my DB snapshot



On 5/22/07, MartyConnelly <martyconnelly at shaw.ca> wrote:
>
> Does DrivemonClient.vbs run normally when you double click in win
> explorer?
> It could be that you haven't pre declared
> Dim oFs , oDrives, oDrive
>
> oops you haven't set ODrive to nothing, it's a class too
>
>
>
>
>
> Francisco Tapia wrote:
>
> >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
> >
> >
> >
> >
>
> --
> Marty Connelly
> Victoria, B.C.
> Canada
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...