Scheduling VBScript via SQL Agent
Timothy Ford, MCSD
Servers for available space. The code runs successfully when I either
double-click it or right-click and select open while in Windows Explorer. If
I embed it either as a step in a DTS package or as a step in a SQL Agent Job
it does not seem to run (well, it runs, but never completes, never inserts
data...) but yet I get no error message. I can also create a batch file (see
Example 2) that runs successfully, but the cmd window never closes and a
prompt never displays after the records are inserted.
Does anyone have a solution to getting this running in an agent job, so once
run I can email out a report of all instances where there is a threshold
violation on the space left on the data drive(s)?
EXAMPLE 1 VBSCRIPT CODE
'Objective: Find Disk Free Space in all the listed servers
'in a table and write to a database table
Const MBCONVERSION= 1048576
Dim AdCn
Dim AdRec
Dim i, SQL
Set AdCn = CreateObject("ADODB.Connection")
Set AdRec = CreateObject("ADODB.Recordset")
Set AdRec1 = CreateObject("ADODB.Recordset")
' NOTE: Change the connection string according to your environment.
AdCn.Open = "Provider=SQLNCLI.1;Password=XXXXX;Persist Security
Info=True;User ID=XXXX;Initial Catalog=SQLInventory;Data Source=XXXXXXX"
SQL1 = "Select Server_Name from tblServers WHERE StatusID <> 0 AND
(Server_Name NOT LIKE '%\%' AND Server_Name NOT IN ('DCMSWSB01')) ORDER BY
Server_Name"
'wscript.echo SQl1
AdRec1.Open SQL1, AdCn,1,1
'Adrec1.movefirst
while not Adrec1.EOF
Computer = Adrec1("Server_Name")
Set objWMIService = GetObject("winmgmts://" & Computer)
Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk")
For Each objLogicalDisk In colLogicalDisk
if objLogicalDisk.drivetype=3 then
SQL = "Insert into tblServerSpace
(Computer,Drive,DiskSize,FreeSpace,Percentage,date) values('"_
&Computer&"','" & objLogicalDisk.DeviceID &"',"&
objLogicalDisk.size/MBCONVERSION &_
"," & objLogicalDisk.freespace/MBCONVERSION &_
","
&((objLogicalDisk.freespace/MBCONVERSION)/(objLogicalDisk.size/MBCONVERSION))*100_
&",'" &now() &"')"
'wscript.echo sql
AdRec.Open SQL, AdCn,1,1
end if
Next
Adrec1.movenext
Wend
Set objWMIService = nothing
Set colLogicalDisk = nothing
AdRec1.close
'AdRec.close
AdCn.close
SET AdCn = Nothing
SET AdRec = Nothing
SET AdRec1 = Nothing
EXAMPLE 2 CONTENTS OF BAT FILE
cscript D:\DiskSpace.vbs /B/nologo/T:90
--
I support The Professional Association for SQL Server (PASS). Visit
www.sqlpass.org today!
Aaron Bertrand [SQL Server MVP]
(1) vbscript that runs a script like the below, and stores the information
in a table.
(2) a sql agent job that queries the data and, based on server/drive
thresholds that are configured in a relatively static table, conditionally
sends email(s) out about particular problems. For example, on our local
file shares we have a 10% free threshold, but on our SAN drives (which are
at least 10x as big) the threshold is much lower (2.5% free).
This allows the VBScript to run as a Windows account, therefore you do not
need to apply special permissions to the SQL Server Agent account. This
also allows you to review the data retrieved on the last run without having
to run it manually. And further, you can store history, so you can perform
trend analysis and/or pre-empt any really heavy growers, instead of only
being able to see the .
The problem probably occurs while you have the wscript.echo command (or
anything else that would raise a prompt of any kind, such as permissions
errors... remember that running the vbscript manually when you are logged in
is not the same as SQL Server Agent running the job, because it is likely
not running as you -- and drive letters/shares will have different
permissions and will probably be invisible to SQL Server in a default
scenario).
A
"Timothy Ford, MCSD" <tim...@foo.com> wrote in message
news:DF752B1C-222F-4CB5...@microsoft.com...
Aaron Bertrand [SQL Server MVP]
> in a table.
Sorry, I meant to also mention that the VBScript is scheduled using Windows
Task Scheduler, which is more apt for things that require windows-based
and/or domain permissions...
Timothy Ford, MCSD
because I end up doing the following:
6:50am
SQL Agent Job that truncates the table storing space info
7:00am
Scheduled task to run vbscript
7:10am
DTS Package called from SQL Agent that will send out an email with records
requiring attention, if any exist.
Lather, rinse, repeat in the afternoon.
I was hoping to get this all in a single agent job. I am running the job
under the context of a domain admin account that I am logged in as when I
manually run the vbscript.
Any suggestions?
--
I support The Professional Association for SQL Server (PASS). Visit
www.sqlpass.org today!
Aaron Bertrand [SQL Server MVP]
> SQL Agent Job that truncates the table storing space info
>
> 7:00am
> Scheduled task to run vbscript
Why can't the VBScript do the truncate and the insert?
> 7:10am
> DTS Package called from SQL Agent that will send out an email with records
> requiring attention, if any exist.
Why do you need a DTS package to send an e-mail?
A
Timothy Ford, MCSD
offending servers so I have it as soon as I walk in the door in the morning.
Less repetitive tasks on my part.
I suppose I could fire off the email in the vbscript if that is what your
proposing.
--
I support The Professional Association for SQL Server (PASS). Visit
www.sqlpass.org today!
"Aaron Bertrand [SQL Server MVP]" wrote:
Matthew
that I got working on my end. In the script I also included a log table
that will log any servers that it cannot find. I would like some one to
peer review for errors, VBS is not my specialty. My next step is to
shoot of an e-mail the question is do I want to use vbs sendmail or
create a trigger in SQL and send it that way.
[CODE]
'Objective: Find Disk Free Space in all the listed servers and write to
a database, and add error log.
'Original Author: MAK
http://groups.google.com/group/microsoft.public.scripting.vbscript/browse_frm/thread/a038e65339d0ee16/6986d41d634f6ff4
'Modified by MKruer
ON ERROR RESUME NEXT
CONST MBCONVERSION= 1048576
DIM AdCn
DIM ErrorSQL
DIM AdRec
DIM i, SQL
SET AdCn = CreateObject("ADODB.Connection")
SET AdRec = CreateObject("ADODB.Recordset")
SET AdRec1 = CreateObject("ADODB.Recordset")
' NOTE: Change the connection string according to your environment.
AdCn.Open = "Provider=SQLOLEDB.1; Data Source=SERVERNAME;Integrated
Security=SSPI;Persist SecurityInfo=False; Initial Catalog=DATABASENAME;
user id=USER; password=PASSWORD"
ErrorSQL="insert into logtable(notes) values ('Disk Monitoring -
Started')"
AdRec.Open ErrorSQL, AdCn,1,1
SET iFSO = CreateObject("Scripting.FilesyStemObject")
InputFile="c:\servers.txt"
SET ifile = iFSO.OpenTextFile(inputfile)
DO UNTIL ifile.AtEndOfLine
Computer = ifile.ReadLine
SET objWMIService = GetObject("winmgmts://" & Computer)
If err.number <> 0 THEN
ErrorSQL="insert into logtable(notes) values ('" + Computer + ":
--Error-- " + Err.description+ "')"
AdRec.Open ErrorSQL, AdCn,1,1
ELSE
SET colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk")
FOR EACH objLogicalDisk IN colLogicalDisk
IF err.number <> 0 THEN
ErrorSQL="insert into logtable(notes) values ('" + Computer + ":
--Error-- " + Err.description+ "')"
END IF
IF objLogicalDisk.drivetype=3 THEN
SQL = "Insert into FreeSpace
(Computer,Drive,DiskSize,FreeSpace,Percentage,date) values('"
&Computer&"','" & objLogicalDisk.DeviceID &"',"&
objLogicalDisk.size/MBCONVERSION & "," &
objLogicalDisk.freespace/MBCONVERSION & "," &
((objLogicalDisk.freespace/MBCONVERSION)/(objLogicalDisk.size/MBCONVERSION))*100
&",'" &now() &"')"
'wscript.echo sql ' Debugging
AdRec.Open SQL, AdCn,1,1
END IF
NEXT
END IF
err.Clear
LOOP
Adrec1.movenext
AdRec.Open "insert into logtable(notes) values ('Disk Monitoring -
Completed')", AdCn,1,1