MS SQL Server - Help Running a VBS Script Inside a Job SQL 2005 (Code Included)

Author

Help Running a VBS Script Inside a Job SQL 2005 (Code Included)

Matthew

2006-10-24, 6:29 pm

My Problem is really simple. I can run a VBS file via the command
prompt (No Problem, No Errors) Place it inside a ActiveX Step inside of
a job, the Step just sits there indefinitely, No errors, no time outs,
nothing nada, zilch.

Create a database called DBAdmin_Dev

Run these SQL Command to add all necessary Tables and Stored
Procedures.
Open up the table System_Monitor_List,
and add any system you can talk
to (be sure to set the MonitorEnabled and MonitorDiskSpace to True

[SQL Code]

USE [DBAdmin_Dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].& #91;System_Monitor_L
og_Disk_Space](
[IDAutoNum] [int] IDENTITY(1,1) NOT NULL,
[SystemName] [varchar](50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[Drive] [varchar](50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[Size] [decimal](18, 0) NULL,
[FreeSpace] [decimal](18, 0) NULL,
[AuditDateTime] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [DBAdmin_Dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].& #91;System_Monitor_L
og_SQL_Space](
[IDAutoNum] [int] IDENTITY(1,1) NOT NULL,
[SystemName] [varchar](50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[DBID] [smallint] NULL,
[FileID] [smallint] NULL,
[DBName] [varchar](50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[FileType] [varchar](10) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[TotalSpace] [bigint] NULL,
[UsedSpace] [bigint] NULL,
[AuditDateTime] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [DBAdmin_Dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].& #91;System_Monitor_L
og_Errors](
[IDAutoNum] [int] IDENTITY(1,1) NOT NULL,
[SystemName] [varchar](50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL,
[SystemError] [varchar](max) COLLATE SQL_Latin1_General_C
P1_CI_AS
NULL,
[AuditDateTime] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [DBAdmin_Dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].& #91;SysMon_Log_Error
s]
@ComputerName varchar(50),
@ErrorDesc varchar(MAX) as

INSERT INTO System_Monitor_Log_E
rrors(SystemName, SystemError,
AuditDateTime)
VALUES (@ComputerName, @ErrorDesc,getdate()
)

USE [DBAdmin_Dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].& #91;SysMon_Log_Disk_
Space]
@ComputerName varchar(50),
@Drive varchar(50),
@Size decimal(18,0),
@FreeSpace decimal(18,0)
AS

INSERT System_Monitor_Log_D
isk_Space (SystemName, Drive, Size,
FreeSpace, AuditDateTime)
VALUES (@ComputerName, @Drive, @Size/1024, @FreeSpace/1024, getdate())
[\SQL Code]

[VBS Code]
'Objective: Find Disk Free Space in all the listed servers and write to
a database, and add error log.
'Version 2.0
ON ERROR RESUME NEXT
Dim AdCn, AdRec, AdRec1
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=SDLC; Integrated
Security=SSPI; Persist SecurityInfo=False; Initial Catalog=DBADMIN_Dev;

user id=diskuser; password=disk"
SQL1 = "SELECT SystemName FROM System_Monitor_List WHERE MonitorEnabled
= 1 AND MonitorDiskSpace = 1 "
'wscript.echo SQL1 ' Debugging
AdRec1.Open SQL1, AdCn,1,1
DO UNTIL AdRec1.EOF
Computer = AdRec1("SystemName")
' wscript.echo Computer ' Debugging
SET objWMIService = GetObject("winmgmts://" & Computer)
If err.number <> 0 THEN
ErrorSQL="EXEC SysMon_Log_Errors " & "'" & Computer & "','" &
Err.description & "'"
' wscript.echo "Start Error :" & ErrorSQL ' Debugging
AdRec.Open ErrorSQL, AdCn,1,1
ELSE
SET colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk")
FOR EACH objLogicalDisk IN colLogicalDisk
IF err.number <> 0 THEN
ErrorSQL="EXEC SysMon_Log_Errors " & "'" & Computer & "','" &
Err.description & "'"
' wscript.echo "Loop Error: " & ErrorSQL ' Debugging
AdRec.Open SQL, AdCn,1,1
END IF
IF objLogicalDisk.drivetype=3 THEN
SQL="EXEC SysMon_Log_Disk_Spac
e " & "'" & Computer & "','" &
objLogicalDisk.DeviceID & "','" & objLogicalDisk.size & "','" &
objLogicalDisk.freespace & "'"
' wscript.echo sql ' Debugging
AdRec.Open SQL, AdCn,1,1
END IF
NEXT
END IF
AdRec1.movenext
Err.Clear
LOOP
'Clear Settings
SET AdCn = NOTHING
SET AdRec = NOTHING
SET AdRec1 = NOTHING
[\VBS Code]

Thanks

Aaron Bertrand [SQL Server MVP]

2006-10-24, 6:29 pm

> My Problem is really simple. I can run a VBS file via the command
> prompt (No Problem, No Errors) Place it inside a ActiveX Step inside of
> a job, the Step just sits there indefinitely, No errors, no time outs,
> nothing nada, zilch.

Does your VBS script raise a message box, prompt, etc.? Does the SQL Server
Agent account have full access to any files/folders/shares that the VBS
script touches? Remember that just because YOU can run something from the
command line doesn't mean SQL Server can. You should try logging onto the
machine as the same account that starts SQL Server Agent, and then try
running your script.

A

Matthew

2006-10-24, 6:29 pm

Thanks Aaron

The VBS Script does not prompt for any messages, (all the lines for
debugging are commented out) I am running the command with the correct
credentials. (those credentials are in the administrators group on the
other system)

Now for whats weird. and I can replicate this. If I run command as an
account that does not have access to the first system in the list. It
does correctly insert the message into the error log table. however as
soon as it get to the next system where it does have access to, it just
sits there doing nothing. The Blocking is occurring somewhere after the
first if statement.

In the mean time I have enabled xp_cmdshell and the script runs
correctly that way. But from a security aspect this is not a good
thing.

-Matt-

Aaron Bertrand [SQL Server MVP] wrote:
>
> Does your VBS script raise a message box, prompt, etc.? Does the SQL Server
> Agent account have full access to any files/folders/shares that the VBS
> script touches? Remember that just because YOU can run something from the
> command line doesn't mean SQL Server can. You should try logging onto the
> machine as the same account that starts SQL Server Agent, and then try
> running your script.
>
> A

Sponsored Links