> 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.

>
> [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

> 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.

> 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 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.

> 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.

> 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?
>

> 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.

> 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.