Update Information in SQL from a VBS script

I have a vbs script that calls a list of systems inside a SQL database.
It then uses that list to go out and collect additional information on
those systems and inserts the collected data into another table.

My question is what is the best way to remove redundant date. Should I
just update the information in the existing cells (if so how?) or
should I create a clean up script that looks for duplicate items and
delete the ones with the oldest time stamp?

Thanks

-Matt-

Bob
Tue Sep 12 12:47:04 CDT 2006

Matthew wrote:
> I have a vbs script that calls a list of systems inside a SQL
> database. It then uses that list to go out and collect additional
> information on those systems and inserts the collected data into
> another table.

This sounds like a task that could be performed with a single
INSERT...SELECT statement.

>
> My question is what is the best way to remove redundant date. Should
> I just update the information in the existing cells (if so how?) or
> should I create a clean up script that looks for duplicate items and
> delete the ones with the oldest time stamp?
>
This question is impossible to answer without details about your table
structures.
http://www.aspfaq.com/

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Matthew
Tue Sep 12 13:10:59 CDT 2006

Thanks for the reply Bob,

Here is the code, I didn't post it the first time because it tends to
freak people out.
Basically what I am trying to accomplish is to insert if new or update
the time stamp if nothing has changed, based upon specified criteria,
in this case it would be Computer name and the HotFixID. If these two
are already in the database i just want to update the time stamp on
that row.

[Code]
'Objective: Find HotFix Information in all the listed servers and write
to a database, and add error log if a Error occures.
'Version 2.0
ON ERROR RESUME NEXT
Dim AdCn, AdRec, AdRec1, 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=SDLC; Integrated
Security=SSPI; Persist SecurityInfo=False; Initial Catalog=xxxx; user
id=xxxx; password=xxxx"
SQL1 = "SELECT SystemName FROM System_Monitor_List WHERE MonitorEnabled
= 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="INSERT INTO System_Monitor_Log_Errors (SystemName,
SystemError) VALUES ('" & Computer & "','" & Err.description & "')"
' wscript.echo "Start Error :" & ErrorSQL ' Debugging
AdRec.Open ErrorSQL, AdCn,1,1
ELSE
SET colItems = objWMIService.ExecQuery("Select * from
Win32_QuickFixEngineering",,48)
FOR EACH objItem IN colItems
IF err.number <> 0 THEN
ErrorSQL="INSERT INTO System_Monitor_Log_Errors (SystemName,
SystemError) VALUES ('" & Computer & "','" & Err.description & "')"
' wscript.echo "Loop Error: " & ErrorSQL ' Debugging
AdRec.Open SQL, AdCn,1,1
END IF
IF objItem.HotFixID <> "File 1" THEN
SQL="INSERT INTO
System_Monitor_Information_Win32_QuickFixEngineering (SystemName,
HotFixID, Description, InstalledBy, InstallDate, AuditDateTime) VALUES
('" & Computer & "','" & objItem.HotFixID & "','" & objItem.Description
& "','" & objItem.InstalledBy & "','" & objItem.InstalledOn & "','" &
now() & "')"
' 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

[/Code]

Thanks

-Matt-


Bob Barrows [MVP] wrote:
> Matthew wrote:
> > I have a vbs script that calls a list of systems inside a SQL
> > database. It then uses that list to go out and collect additional
> > information on those systems and inserts the collected data into
> > another table.
>
> This sounds like a task that could be performed with a single
> INSERT...SELECT statement.
>
> >
> > My question is what is the best way to remove redundant date. Should
> > I just update the information in the existing cells (if so how?) or
> > should I create a clean up script that looks for duplicate items and
> > delete the ones with the oldest time stamp?
> >
> This question is impossible to answer without details about your table
> structures.
> http://www.aspfaq.com/
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Bob
Tue Sep 12 13:39:30 CDT 2006

Matthew wrote:
> Thanks for the reply Bob,
>
> Here is the code, I didn't post it the first time because it tends to
> freak people out.

:-)
I can see why. there are a lot of problems with it... ;-)

> Basically what I am trying to accomplish is to insert if new or update
> the time stamp if nothing has changed, based upon specified criteria,
> in this case it would be Computer name and the HotFixID. If these two
> are already in the database i just want to update the time stamp on
> that row.

Again, without the table definitions, the prior statement is practically
meaningless. But read on:

>
> [Code]
> 'Objective: Find HotFix Information in all the listed servers and
> write to a database, and add error log if a Error occures.
> 'Version 2.0
> ON ERROR RESUME NEXT
> Dim AdCn, AdRec, AdRec1, SQL
> Set AdCn = CreateObject("ADODB.Connection")
> Set AdRec = CreateObject("ADODB.Recordset")
> Set AdRec1 = CreateObject("ADODB.Recordset")

> AdCn.Open = "Provider=SQLOLEDB.1; Data Source=SDLC; Integrated
> Security=SSPI; Persist SecurityInfo=False; Initial Catalog=xxxx; user
> id=xxxx; password=xxxx"
> SQL1 = "SELECT SystemName FROM System_Monitor_List WHERE
> MonitorEnabled = 1"
> 'wscript.echo SQL1 ' Debugging
> AdRec1.Open SQL1, AdCn,1,1

First problem: inappropriate cursor type.From what I can see there is no
need for such an expensive cursor. The default firehose (forward-only,
read-only) cursor will perform better and should be all you need:

Set AdRec1 = AdCn.Execute(SQL1,,1) '1=adCmdText

So is it at this point you need to either insert or update? i would
create a stored procedure that handled that task.

> DO UNTIL AdRec1.EOF
> Computer = AdRec1("SystemName")
> ' wscript.echo Computer ' Debugging
> SET objWMIService = GetObject("winmgmts://" & Computer)
> IF err.number <> 0 THEN
> ErrorSQL="INSERT INTO System_Monitor_Log_Errors (SystemName,
> SystemError) VALUES ('" & Computer & "','" & Err.description & "')"
> ' wscript.echo "Start Error :" & ErrorSQL ' Debugging
> AdRec.Open ErrorSQL, AdCn,1,1

Second problem: using a recordset to run a sql statement that does not
return records:

AdCn.Execute ErrorSQL,,129
'129=1 + 128 = adCmdText+adExecuteNoRecords



> ELSE
> SET colItems = objWMIService.ExecQuery("Select * from
> Win32_QuickFixEngineering",,48)
> FOR EACH objItem IN colItems
> IF err.number <> 0 THEN
> ErrorSQL="INSERT INTO System_Monitor_Log_Errors (SystemName,
> SystemError) VALUES ('" & Computer & "','" & Err.description & "')"
> ' wscript.echo "Loop Error: " & ErrorSQL ' Debugging
> AdRec.Open SQL, AdCn,1,1

See above

> END IF
> IF objItem.HotFixID <> "File 1" THEN
> SQL="INSERT INTO
> System_Monitor_Information_Win32_QuickFixEngineering (SystemName,
> HotFixID, Description, InstalledBy, InstallDate, AuditDateTime) VALUES
> ('" & Computer & "','" & objItem.HotFixID & "','" &
> objItem.Description & "','" & objItem.InstalledBy & "','" &
> objItem.InstalledOn & "','" & now() & "')"
> ' wscript.echo sql ' Debugging
> AdRec.Open SQL, AdCn,1,1

See above

> END IF
> NEXT
> END IF
> AdRec1.movenext
> Err.Clear
> LOOP
> 'Clear Settings
> SET AdCn = NOTHING
> SET AdRec = NOTHING
> SET AdRec1 = NOTHING
>
> [/Code]
>
> Thanks
>
> -Matt-
>
>
> Bob Barrows [MVP] wrote:
>> Matthew wrote:
>>> I have a vbs script that calls a list of systems inside a SQL
>>> database. It then uses that list to go out and collect additional
>>> information on those systems and inserts the collected data into
>>> another table.
>>
>> This sounds like a task that could be performed with a single
>> INSERT...SELECT statement.
>>
>>>
>>> My question is what is the best way to remove redundant date.
>>> Should I just update the information in the existing cells (if so
>>> how?) or should I create a clean up script that looks for duplicate
>>> items and delete the ones with the oldest time stamp?
>>>
>> This question is impossible to answer without details about your
>> table structures.
>> http://www.aspfaq.com/
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Matthew
Tue Sep 12 14:03:02 CDT 2006

Thats not a problem, I am not a VBS programmer. I am alway looking for
how best to optimize a script. here is the SQL code for the two
required tables. Between these and the VBS script you should have
everything you need to run and insert information into the tables.

[Code]
USE [xxxx]
GO
/****** Object: Table
[dbo].[System_Monitor_Information_Win32_QuickFixEngineering] Script
Date: 09/12/2006 11:57:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE
[dbo].[System_Monitor_Information_Win32_QuickFixEngineering](
[SystemName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HotFixID] [varchar](260) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[InstalledBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InstallDate] [smalldatetime] NULL,
[AuditDateTime] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
[/Code]

[Code]
USE [xxxx]
GO
/****** Object: Table [dbo].[System_Monitor_List] Script Date:
09/12/2006 11:59:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[System_Monitor_List](
[SystemName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[SystemType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[MonitorEnabled] [bit] NOT NULL CONSTRAINT
[DF__Temporary__Monit__5441852A] DEFAULT ((0)),
CONSTRAINT [aaaaaserver_PK] PRIMARY KEY NONCLUSTERED
(
[SystemName] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
[/Code]


Bob Barrows [MVP] wrote:
> Matthew wrote:
> > Thanks for the reply Bob,
> >
> > Here is the code, I didn't post it the first time because it tends to
> > freak people out.
>
> :-)
> I can see why. there are a lot of problems with it... ;-)
>
> > Basically what I am trying to accomplish is to insert if new or update
> > the time stamp if nothing has changed, based upon specified criteria,
> > in this case it would be Computer name and the HotFixID. If these two
> > are already in the database i just want to update the time stamp on
> > that row.
>
> Again, without the table definitions, the prior statement is practically
> meaningless. But read on:
>
> >
> > [Code]
> > 'Objective: Find HotFix Information in all the listed servers and
> > write to a database, and add error log if a Error occures.
> > 'Version 2.0
> > ON ERROR RESUME NEXT
> > Dim AdCn, AdRec, AdRec1, SQL
> > Set AdCn = CreateObject("ADODB.Connection")
> > Set AdRec = CreateObject("ADODB.Recordset")
> > Set AdRec1 = CreateObject("ADODB.Recordset")
>
> > AdCn.Open = "Provider=SQLOLEDB.1; Data Source=SDLC; Integrated
> > Security=SSPI; Persist SecurityInfo=False; Initial Catalog=xxxx; user
> > id=xxxx; password=xxxx"
> > SQL1 = "SELECT SystemName FROM System_Monitor_List WHERE
> > MonitorEnabled = 1"
> > 'wscript.echo SQL1 ' Debugging
> > AdRec1.Open SQL1, AdCn,1,1
>
> First problem: inappropriate cursor type.From what I can see there is no
> need for such an expensive cursor. The default firehose (forward-only,
> read-only) cursor will perform better and should be all you need:
>
> Set AdRec1 = AdCn.Execute(SQL1,,1) '1=adCmdText
>
> So is it at this point you need to either insert or update? i would
> create a stored procedure that handled that task.
>
> > DO UNTIL AdRec1.EOF
> > Computer = AdRec1("SystemName")
> > ' wscript.echo Computer ' Debugging
> > SET objWMIService = GetObject("winmgmts://" & Computer)
> > IF err.number <> 0 THEN
> > ErrorSQL="INSERT INTO System_Monitor_Log_Errors (SystemName,
> > SystemError) VALUES ('" & Computer & "','" & Err.description & "')"
> > ' wscript.echo "Start Error :" & ErrorSQL ' Debugging
> > AdRec.Open ErrorSQL, AdCn,1,1
>
> Second problem: using a recordset to run a sql statement that does not
> return records:
>
> AdCn.Execute ErrorSQL,,129
> '129=1 + 128 = adCmdText+adExecuteNoRecords
>
>
>
> > ELSE
> > SET colItems = objWMIService.ExecQuery("Select * from
> > Win32_QuickFixEngineering",,48)
> > FOR EACH objItem IN colItems
> > IF err.number <> 0 THEN
> > ErrorSQL="INSERT INTO System_Monitor_Log_Errors (SystemName,
> > SystemError) VALUES ('" & Computer & "','" & Err.description & "')"
> > ' wscript.echo "Loop Error: " & ErrorSQL ' Debugging
> > AdRec.Open SQL, AdCn,1,1
>
> See above
>
> > END IF
> > IF objItem.HotFixID <> "File 1" THEN
> > SQL="INSERT INTO
> > System_Monitor_Information_Win32_QuickFixEngineering (SystemName,
> > HotFixID, Description, InstalledBy, InstallDate, AuditDateTime) VALUES
> > ('" & Computer & "','" & objItem.HotFixID & "','" &
> > objItem.Description & "','" & objItem.InstalledBy & "','" &
> > objItem.InstalledOn & "','" & now() & "')"
> > ' wscript.echo sql ' Debugging
> > AdRec.Open SQL, AdCn,1,1
>
> See above
>
> > END IF
> > NEXT
> > END IF
> > AdRec1.movenext
> > Err.Clear
> > LOOP
> > 'Clear Settings
> > SET AdCn = NOTHING
> > SET AdRec = NOTHING
> > SET AdRec1 = NOTHING
> >
> > [/Code]
> >
> > Thanks
> >
> > -Matt-
> >
> >
> > Bob Barrows [MVP] wrote:
> >> Matthew wrote:
> >>> I have a vbs script that calls a list of systems inside a SQL
> >>> database. It then uses that list to go out and collect additional
> >>> information on those systems and inserts the collected data into
> >>> another table.
> >>
> >> This sounds like a task that could be performed with a single
> >> INSERT...SELECT statement.
> >>
> >>>
> >>> My question is what is the best way to remove redundant date.
> >>> Should I just update the information in the existing cells (if so
> >>> how?) or should I create a clean up script that looks for duplicate
> >>> items and delete the ones with the oldest time stamp?
> >>>
> >> This question is impossible to answer without details about your
> >> table structures.
> >> http://www.aspfaq.com/
> >>
> >> --
> >> Microsoft MVP -- ASP/ASP.NET
> >> Please reply to the newsgroup. The email account listed in my From
> >> header is my spam trap, so I don't check it very often. You will get
> >> a quicker response by posting to the newsgroup.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Bob
Tue Sep 12 14:41:39 CDT 2006

Matthew wrote:
> Thats not a problem, I am not a VBS programmer.

So are you up to the task of writing a stored procedure? If so, you
should write one that accepts the supplied values , uses IF EXISTS() to
check the table for pre-existing records with the same information,
using an UPDATE statement to update the desired columns if it exists,
and an INSERT statement to insert the data if it doesn't exist.

If the above is Greek to you, post back and I will return to this when I
get home from work.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Matthew
Tue Sep 12 15:51:06 CDT 2006

I am up for writing stored procedures, I just never done one like this
before, so i will have to do it this once and adapt it to the other WMI
queries I am pulling. I don't know how much of the VBS I can off load
in to SQL. I just made this quick and dirty to see if i could do it,
now it is how best to do it. Also i am using SQL2005 so I should be
able to run it as a ActiveX Script in a Job, which makes me happier
because its all in SQL, and not all over the place.

I now have a stored Procedure for the Error Trap

Replace
ErrorSQL="INSERT INTO System_Monitor_Log_Errors (SystemName,
SystemError) VALUES ('" & Computer & "','" & Err.description & "')"

With
ErrorSQL="exec LogError " & "'" & Computer & "','" &
Err.description & "'"

Add SP
[code]
USE [xxxx]
GO
/****** Object: StoredProcedure [dbo].[LogError] Script Date:
09/12/2006 13:47:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[LogError] @ComputerName varchar(50), @ErrorDesc
varchar(255) as

insert into System_Monitor_Log_Errors(SystemName, SystemError) values
(@ComputerName, @ErrorDesc)
[/code]

That Reminds me I for got this table, If anything blow up it logs what
happens.
[Code]
USE [xxxx]
GO
/****** Object: Table [dbo].[System_Monitor_Log_Errors] Script
Date: 09/12/2006 13:48:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[System_Monitor_Log_Errors](
[IDAutoNum] [int] IDENTITY(1,1) NOT NULL,
[SystemName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SystemError] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[AuditDateTime] [datetime] NULL CONSTRAINT
[DF__DiskMonito__date__44FF419A] DEFAULT (getdate())
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
[/Code]


Bob Barrows [MVP] wrote:
> Matthew wrote:
> > Thats not a problem, I am not a VBS programmer.
>
> So are you up to the task of writing a stored procedure? If so, you
> should write one that accepts the supplied values , uses IF EXISTS() to
> check the table for pre-existing records with the same information,
> using an UPDATE statement to update the desired columns if it exists,
> and an INSERT statement to insert the data if it doesn't exist.
>
> If the above is Greek to you, post back and I will return to this when I
> get home from work.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Matthew
Wed Sep 13 09:09:00 CDT 2006

I am trying to understand the changes you have suggested to the VBS
script, but i not sure what if i am doing it correctly.

are you saying

Replace: Set AdRec1 = CreateObject("ADODB.Recordset")
With: Set AdRec1 = AdCn.Execute(SQL1,,1)

and

Replace: AdRec.Open ErrorSQL, AdCn,1,1
With: AdCn.Execute ErrorSQL,,129

Is this correct?

Thanks

-Matt-

Matthew wrote:
> I am up for writing stored procedures, I just never done one like this
> before, so i will have to do it this once and adapt it to the other WMI
> queries I am pulling. I don't know how much of the VBS I can off load
> in to SQL. I just made this quick and dirty to see if i could do it,
> now it is how best to do it. Also i am using SQL2005 so I should be
> able to run it as a ActiveX Script in a Job, which makes me happier
> because its all in SQL, and not all over the place.
>
> I now have a stored Procedure for the Error Trap
>
> Replace
> ErrorSQL="INSERT INTO System_Monitor_Log_Errors (SystemName,
> SystemError) VALUES ('" & Computer & "','" & Err.description & "')"
>
> With
> ErrorSQL="exec LogError " & "'" & Computer & "','" &
> Err.description & "'"
>
> Add SP
> [code]
> USE [xxxx]
> GO
> /****** Object: StoredProcedure [dbo].[LogError] Script Date:
> 09/12/2006 13:47:12 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> Create Procedure [dbo].[LogError] @ComputerName varchar(50), @ErrorDesc
> varchar(255) as
>
> insert into System_Monitor_Log_Errors(SystemName, SystemError) values
> (@ComputerName, @ErrorDesc)
> [/code]
>
> That Reminds me I for got this table, If anything blow up it logs what
> happens.
> [Code]
> USE [xxxx]
> GO
> /****** Object: Table [dbo].[System_Monitor_Log_Errors] Script
> Date: 09/12/2006 13:48:25 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[System_Monitor_Log_Errors](
> [IDAutoNum] [int] IDENTITY(1,1) NOT NULL,
> [SystemName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [SystemError] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL,
> [AuditDateTime] [datetime] NULL CONSTRAINT
> [DF__DiskMonito__date__44FF419A] DEFAULT (getdate())
> ) ON [PRIMARY]
>
> GO
> SET ANSI_PADDING OFF
> [/Code]
>
>
> Bob Barrows [MVP] wrote:
> > Matthew wrote:
> > > Thats not a problem, I am not a VBS programmer.
> >
> > So are you up to the task of writing a stored procedure? If so, you
> > should write one that accepts the supplied values , uses IF EXISTS() to
> > check the table for pre-existing records with the same information,
> > using an UPDATE statement to update the desired columns if it exists,
> > and an INSERT statement to insert the data if it doesn't exist.
> >
> > If the above is Greek to you, post back and I will return to this when I
> > get home from work.
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.

Bob
Wed Sep 13 09:18:25 CDT 2006

Matthew wrote:
> I am trying to understand the changes you have suggested to the VBS
> script, but i not sure what if i am doing it correctly.
>
> are you saying
>
> Replace: Set AdRec1 = CreateObject("ADODB.Recordset")
> With: Set AdRec1 = AdCn.Execute(SQL1,,1)
>
> and
>
> Replace: AdRec.Open ErrorSQL, AdCn,1,1
> With: AdCn.Execute ErrorSQL,,129
>
> Is this correct?
>

Yes, but that was before you started using a stored procedure. You can
read about my preferred technique for calling stored procedures here:
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Matthew
Wed Sep 13 09:55:09 CDT 2006

Still it would probably be best to upgrade my current VBS script with
the changes, to speed up the process as much as I can. This way then
next item I add I can use this it as a template.

Bob Barrows [MVP] wrote:
> Matthew wrote:
> > I am trying to understand the changes you have suggested to the VBS
> > script, but i not sure what if i am doing it correctly.
> >
> > are you saying
> >
> > Replace: Set AdRec1 = CreateObject("ADODB.Recordset")
> > With: Set AdRec1 = AdCn.Execute(SQL1,,1)
> >
> > and
> >
> > Replace: AdRec.Open ErrorSQL, AdCn,1,1
> > With: AdCn.Execute ErrorSQL,,129
> >
> > Is this correct?
> >
>
> Yes, but that was before you started using a stored procedure. You can
> read about my preferred technique for calling stored procedures here:
> http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Matthew
Thu Sep 14 13:43:21 CDT 2006

Do you know why in the Job using ActiveX Scrip, this script runs
through the first loop, telling me what servers it does not have access
to or can not find on the network, but once it enters the second loop.
It gets stuck, never reporting or inserting anything.

The code runs fine if you through it into a vbs file and run it from
the command prompt.

Thanks

-Matt-

[Code]
'Objective: Find HotFix Information in all the listed servers and write
to a database, and add error log if Error occures.
'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"
'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="INSERT INTO System_Monitor_Log_Errors (SystemName,
SystemError) VALUES ('" & Computer & "','" & Err.description & "')"
ErrorSQL="EXEC System_Monitor_Log_Errors " & "'" & Computer & "','" &
Err.description & "'"
' wscript.echo "Start Error :" & ErrorSQL ' Debugging
AdRec.Open ErrorSQL, AdCn,1,1
ELSE
SET colItems = objWMIService.ExecQuery("Select * from
Win32_QuickFixEngineering",,48)
FOR EACH objItem IN colItems
IF err.number <> 0 THEN
' ErrorSQL="EXEC SysMon_Log_Errors " & "'" & Computer & "','" &
Err.description & "'"
ErrorSQL="INSERT INTO System_Monitor_Log_Errors (SystemName,
SystemError) VALUES ('" & Computer & "','" & Err.description & "')"
' wscript.echo "Loop Error: " & ErrorSQL ' Debugging
AdRec.Open SQL, AdCn,1,1
END IF
IF objItem.HotFixID <> "File 1" THEN
SQL="INSERT INTO
System_Monitor_Information_Win32_QuickFixEngineering (SystemName,
HotFixID, Description, InstalledBy, InstallDate, AuditDateTime) VALUES
('" & Computer & "','" & objItem.HotFixID & "','" & objItem.Description
& "','" & objItem.InstalledBy & "','" & objItem.InstalledOn & "','" &
now() & "')"
' SQL="EXEC SysMon_Win32_QuickFixEngineering " & "'" & Computer &
"','" & objItem.HotFixID & "','" & objItem.Description & "','" &
objItem.InstalledBy & "','" & objItem.InstalledOn & "'"
' wscript.echo sql ' Debugging
AdRec.Open SQL, AdCn,1,1
END IF
NEXT
END IF
AdRec1.movenext
Err.Clear
LOOP
'WScript.Sleep
'Clear Settings
SET AdCn = NOTHING
SET AdRec = NOTHING
SET AdRec1 = NOTHING
[\Code]

Matthew wrote:
> Still it would probably be best to upgrade my current VBS script with
> the changes, to speed up the process as much as I can. This way then
> next item I add I can use this it as a template.
>
> Bob Barrows [MVP] wrote:
> > Matthew wrote:
> > > I am trying to understand the changes you have suggested to the VBS
> > > script, but i not sure what if i am doing it correctly.
> > >
> > > are you saying
> > >
> > > Replace: Set AdRec1 = CreateObject("ADODB.Recordset")
> > > With: Set AdRec1 = AdCn.Execute(SQL1,,1)
> > >
> > > and
> > >
> > > Replace: AdRec.Open ErrorSQL, AdCn,1,1
> > > With: AdCn.Execute ErrorSQL,,129
> > >
> > > Is this correct?
> > >
> >
> > Yes, but that was before you started using a stored procedure. You can
> > read about my preferred technique for calling stored procedures here:
> > http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.