Multiple connections to DB 2

[emozley]

Hi,

I have written a script that connects to each PC on our network one by one, retrives the list of Add/Remove programs and for each item in the list does the following:

1. Check if application name is in the database - if not then add it (applicationID, applicationame) and make a note of the applicationID. If it is in the database then retrieve the application ID.

2. Write the applicationID value and the PC identifier to another table.

This works quite well however rather than run the script on remote PCs one by one I would like the script to be run locally on each PC all at the same time (we have 150 PCs on our network).

If I do this is there a way I can be sure that two computers won't connect to the database and look up the same application at the same time and potentially create duplicate records - i.e. identical application name but with two applicationID's?

Thanks very much

Ed

 

[mrmovie]

the ADO connection should handle that type of thing for you, pesimistic locking etc. i dont write to db's that often so im not 100% up to speed.

i would say this much though. you only have 150 clients, you cant have more than 200 applications. you really arent talking about that much data. i would say a database is a little overkill (unless of course you want it to handle the locking / duplicate records that you describe ;-))

I Hear, I Forget
I See, I Remember
I Do, I Understand

Ronald McDonald

 

[dilettante]

You can use Jet's Alcatraz (row-level) locking to make this work a little smoother, and by called stored procedures as methods of the ADO Connection the code becomes simple. Database relations and constraints do a lot of the work.

Here is a standalone example simulating the process. On first run it will create an empty database if required.

You can run several copies at once, point the database location to a network share, run a copy on each of several machines, etc.

There is a limit of 255 simultaneous connections to the same Jet MDB though. I've never tried pushing things to that level so I can't say what you might do to recover from whatever errors you will get.

Harvest.wsf

<job>
  <!-- Harvest PC's Application List:

       Simulate a script running at a PC and updating a
       database with its list of applications by Tag number.
       Don't make duplicate entries.

       The purpose here is just to show the database actions.
  -->
  <reference object="Scripting.FileSystemObject"/>
  <object id="FSO" progId="Scripting.FileSystemObject"/>
  <reference object="ADODB.Connection"/>
  <resource id="DBCONN">
    Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;
    Jet OLEDB:Database Locking Mode=1;Data Source='$MDB$'
  </resource>
  <resource id="CREATEAPPS">
    CREATE TABLE Apps
       (AppID IDENTITY NOT NULL CONSTRAINT PK_AppID PRIMARY KEY,
        AppName TEXT(50) WITH COMPRESSION NOT NULL UNIQUE)
  </resource>
  <resource id="CREATEINSTANCES">
    CREATE TABLE Instances
       (InstID IDENTITY NOT NULL CONSTRAINT PK_InstID PRIMARY KEY,
        MachineTag TEXT(50) WITH COMPRESSION NOT NULL,
        AppID INTEGER NOT NULL CONSTRAINT FK_AppID
            REFERENCES Apps (AppID),
        CONSTRAINT CaptureOnce UNIQUE (MachineTag, AppID))
  </resource>
  <resource id="CREATEINSERTAPP">
    CREATE PROC InsertApp (NewAppName TEXT(50)) AS
        INSERT INTO Apps (AppName) VALUES (NewAppName)
  </resource>
  <resource id="CREATEINSERTINSTANCE">
    CREATE PROC InsertInstance (NewTag TEXT(50), NewApp TEXT(50)) AS
        INSERT INTO Instances (MachineTag, AppID)
            SELECT NewTag, AppId FROM Apps WHERE NewApp = AppName
  </resource>
  <resource id="CREATEINSTANCEVIEW">
    CREATE VIEW InstanceView (ID, MachineTag, AppName) AS
        SELECT InstID AS ID, MachineTag, AppName
            FROM Instances LEFT OUTER JOIN Apps
                ON Instances.AppID = Apps.AppID
  </resource>
  <script language="VBScript">
    Option Explicit

    'OPEN OR CREATE DATABASE.

    Private Const MDB = "Harvest.mdb" 'Relative name or full path.
    Private cnDB

    If FSO.FileExists(MDB) Then
      Set cnDB = CreateObject("ADODB.Connection")
      cnDB.Open Replace(getResource("DBCONN"), "$MDB$", MDB)
    Else
      With CreateObject("ADOX.Catalog")
        .Create Replace(getResource("DBCONN"), "$MDB$", MDB)
        Set cnDB = .ActiveConnection
      End With
      With cnDB
        .Execute getResource("CREATEAPPS"), , adCmdText
        .Execute getResource("CREATEINSTANCES"), , adCmdText
        .Execute getResource("CREATEINSERTAPP"), , adCmdText
        .Execute getResource("CREATEINSERTINSTANCE"), , adCmdText
        .Execute getResource("CREATEINSTANCEVIEW"), , adCmdText
      End With
    End If

    'SIMULATE APPLICATION INVENTORY SCAN UPDATING DATABASE.

    Private MyTag, I, FoundApp

    Randomize
    MyTag = Right("0000" & CStr(Int(Rnd() * 100000)), 5)

    With cnDB
      Do
        I = I + (Int(9 * Rnd()) + 1) * 10
        FoundApp = "Some App " & Right("00" & CStr(I), 3)
        'Insert FoundApp if unique, then insert MyTag/FoundApp
        'if unique as determined by database table constraints.
        On Error Resume Next
        .InsertApp FoundApp
        .InsertInstance MyTag, FoundApp
        On Error GoTo 0
        WScript.Sleep 250
      Loop While I < 900

      .Close
    End With

    MsgBox "Tag " & MyTag & vbNewLine & "Done", _
           vbOkOnly, _
           WScript.ScriptName
  </script>
</job>

 

[emozley]

Hi Diletttante,

Thanks for this and sorry for the slow reply - this is great and will make things a lot easier. Particularly liked the create db code at the start.

Thanks again

Ed