Populate SQL 2000 Database through a VBS Script

[DS3Circuit 0]

I am buried under a multi site / multi version MS office migration to 2003 that has taken up much of my time.

 

Without the need of 3rd party products, my superiors wish to track the migration dynamically.

 

History:

 

1000+ users

Windows 2000 SP4

Active Directory DFS / GPO Software Deployment.

 

What I would like is to run a vbs login script that pulls exe version numbers, populates a SQL 2000 database, and then an ASP page pulls the data accordingly. I am stuck at how a VBS script would populate a SQL 2000 database.

 

If anyone has some URLs and/or examples. You would certainly be making my life easier.

 

If you need more information then what I have provided, fire away.

 

Thanks as always.

 

Edit: Alex, I hope you have run into this

[DS3Circuit 0]

I'll show my "ruff" vbs code here to get the info I am looking for. Thanks for the delphi examples. Looking at their logic always helps. Now I just need to figure out the code to populate a sql 2000 db. I have created a microsoft data link file (udl) file but this is where I get stuck.

 

Thank you so much for the help so far

 

********************

********************

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim RunSQL

 

WScript.Echo "Version of Office installed (0 equals none): " & GetOfficeVer()

 

If GetOfficeVer = "2003" Then

Wscript.Echo "Version of Outlook: " & objFSO.GetFileVersion("C:\Program Files\Microsoft Office\OFFICE11\outlook.exe")

Wscript.Echo "Version of Excel: " & objFSO.GetFileVersion("C:\Program Files\Microsoft Office\OFFICE11\excel.exe")

Wscript.Echo "Version of Word: " & objFSO.GetFileVersion("C:\Program Files\Microsoft Office\OFFICE11\winword.exe")

Wscript.Echo "Version of PowerPoint: " & objFSO.GetFileVersion("C:\Program Files\Microsoft Office\OFFICE11\powerpnt.exe")

End If

 

If GetOfficeVer = "2000" Then

Wscript.Echo "Version of Outlook: " & objFSO.GetFileVersion("C:\Program Files\Microsoft Office\OFFICE\outlook.exe")

Wscript.Echo "Version of Excel: " & objFSO.GetFileVersion("C:\Program Files\Microsoft Office\OFFICE\excel.exe")

Wscript.Echo "Version of Word: " & objFSO.GetFileVersion("C:\Program Files\Microsoft Office\OFFICE\winword.exe")

Wscript.Echo "Version of PowerPoint: " & objFSO.GetFileVersion("C:\Program Files\Microsoft Office\OFFICE\powerpnt.exe")

End If

 

If objFSO.FileExists("C:\Program Files\Microsoft Office\OFFICE\frontpg.exe") Then

Wscript.Echo "Version of Frontpage: " & objFSO.GetFileVersion("C:\Program Files\Microsoft Office\OFFICE\frontpg.exe")

Else

Wscript.Echo "Frontpage 2000 Not Installed."

End If

 

If objFSO.FileExists("C:\Program Files\Microsoft Office\OFFICE11\frontpg.exe") Then

Wscript.Echo "Version of Frontpage: " & objFSO.GetFileVersion("C:\Program Files\Microsoft Office\OFFICE11\frontpg.exe")

Else

Wscript.Echo "Frontpage 2000 Not Installed."

End If

 

If RunSQL = 1 then

RunSQLInstance

End if

 

 

' SQL 2000 Database Var Setup

 

 

Function RunSQLInstance

'Set con=createobject("adodb.connection")

'con.open "file name=dbconnect.udl"

'set cmd=createobject("adodb.command")

'cmd.ActiveConnection=con

'cmd.Commandtype=1

'End Function

 

Dim SQLConnection

Dim SQLCommand

 

Set SQLConnection = CreateObject("ADODB.Connection")

'SQL Connection String

SQLConnection.Open "Driver={SQL Server};server=(SQLSERVER.MYSITE.edu);database=Office;uid=sa;pwd=anything;"

 

 

 

 

Function GetOfficeVer()

 

RunSQL = 0

 

sRegPre = "HKLM\SOFTWARE\Microsoft\Office\"

sRegPost = "\Common\InstallRoot\"

Select Case True

Case RegKeyExists(sRegPre & "11.0" & sRegPost)

sOfficeVer = "2003"

Case RegKeyExists(sRegPre & "10.0" & sRegPost)

sOfficeVer = "2002"

Case RegKeyExists(sRegPre & "9.0" & sRegPost)

sOfficeVer = "2000"

Case RegKeyExists(sRegPre & "8.0" & sRegPost)

sOfficeVer = "97"

Case Else

sOfficeVer = "0"

End Select

GetOfficeVer = sOfficeVer

End Function

 

Function RegKeyExists(sRegKey)

Set oShell = CreateObject("WScript.Shell")

RegKeyExists = True

sRegKey = Trim (sRegKey)

If Not Right(sRegKey, 1) = "\" Then

sRegKey = sRegKey & "\"

End if

On Error Resume Next

RegReadReturn = oShell.RegRead(sRegKey)

If Err Then

If LCase(Left(err.description,7)) = "invalid" Then

'key not found...

RegKeyExists = False

End if

Err.clear

End if

On Error Goto 0

End Function

EDIT: Damm Smiley Faces

[Edited by ds3circuit on 2004-10-21 19:03:25]

 

[DS3Circuit 0]

Busy is an ...... understatement

 

Beautiful set of code. I am gonna go take it into the lab and pull it apart to see what works for us. If you are ever in my area, I owe you a cold one or two

 

I'll keep ya posted on the outcome.