May 22, 2007 at 6:26 pm
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
May 24, 2007 at 2:10 pm
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
May 24, 2007 at 5:59 pm
I found that too and seems to work well for SS2000 but for SS2005, how do schedule an SSIS
-- Francisco
May 25, 2007 at 9:25 am
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
June 20, 2007 at 8:02 am
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
January 7, 2015 at 10:20 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply