[dba-SQLServer] scheduling a vbscript

Michael Maddison michael at ddisolutions.com.au
Tue May 22 22:03:29 CDT 2007


Just a guess but does it have sufficient permissions when being run by
the scheduler?
You can set it to run under a specific account.

cheers


Michael M

Subject: Re: [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...
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com





More information about the dba-SQLServer mailing list