How to populate a SQL Server table via a VBS script?

[sotando]

Hello All,

I found the below script on the net and I modified it slightly. What I want to do is to store the data extract into a SQL Server table. I need help in creating the connection string and populating the database table instead of outputting the results to a text file. Does anyone know how to accomplish this task?

VBS Script:

Const HKLM = &H80000002 'HKEY_LOCAL_MACHINE
On Error Resume Next
arrComputers = Array("FIRNSA341","FIRNS342")

For Each strComputer In arrComputers
WScript.Echo
WScript.Echo "===================================="
WScript.Echo "ServerName: "& strComputer
WScript.Echo "===================================="
strKey = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"
strEntry1a = "DisplayName"
strEntry1b = "QuietDisplayName"
strEntry2 = "InstallDate"
strEntry3 = "VersionMajor"
strEntry4 = "VersionMinor"
strEntry5 = "EstimatedSize"

Set objReg = GetObject("winmgmts://" & strComputer & _
"/root/default:StdRegProv")
objReg.EnumKey HKLM, strKey, arrSubkeys
WScript.Echo "Installed Applications" & VbCrLf
For Each strSubkey In arrSubkeys
intRet1 = objReg.GetStringValue(HKLM, strKey & strSubkey, _
strEntry1a, strValue1)
If intRet1 <> 0 Then
objReg.GetStringValue HKLM, strKey & strSubkey, _
strEntry1b, strValue1
End If
If strValue1 <> "" Then
WScript.Echo VbCrLf & "Display Name: " & strValue1
End If
objReg.GetStringValue HKLM, strKey & strSubkey, _
strEntry2, strValue2
If strValue2 <> "" Then
WScript.Echo "Install Date: " & strValue2
End If
objReg.GetDWORDValue HKLM, strKey & strSubkey, _
strEntry3, intValue3
objReg.GetDWORDValue HKLM, strKey & strSubkey, _
strEntry4, intValue4
If intValue3 <> "" Then
WScript.Echo "Version: " & intValue3 & "." & intValue4
End If
objReg.GetDWORDValue HKLM, strKey & strSubkey, _
strEntry5, intValue5
If intValue5 <> "" Then
WScript.Echo "Estimated Size: " & Round(intValue5/1024, 3) & " megabytes"
End If
Next
Next

 

[PHV]

Use an ADODB.Recordset object.
As for the connection, have a look here:

http://www.connectionstrings.com/

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sotando]

Thanks for the response PHV, I have add

strComputerSQL = "MSSQLSERVER"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open _
"Provider=SQLOLEDB;Data Source=" & strComputerSQL & ";" & _
"Trusted_Connection=Yes;Initial Catalog=TESTDB"

to my script but not sure it is correct, please verify. Also how can I insert data into my SQL Server with this script? The table structure is below:

CREATE TABLE [dbo].[InstalledSoftware](
[DisplayName] [varchar](100) NULL,
[InstallDate] [datetime] NULL,
[VersionMajor] [varchar](15) NULL,
[EstimatedSize] [varchar](10) NULL,
[ServerName] [varchar](10) NULL
) ON [PRIMARY]


Updated VBS Script:


Const HKLM = &H80000002 'HKEY_LOCAL_MACHINE
On Error Resume Next
arrComputers = Array("FIRNSA341","FIRNS342")

For Each strComputer In arrComputers
WScript.Echo
WScript.Echo "===================================="
WScript.Echo "ServerName: "& strComputer
WScript.Echo "===================================="
strKey = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"
strEntry1a = "DisplayName"
strEntry1b = "QuietDisplayName"
strEntry2 = "InstallDate"
strEntry3 = "VersionMajor"
strEntry4 = "VersionMinor"
strEntry5 = "EstimatedSize"

Set objReg = GetObject("winmgmts://" & strComputer & _
"/root/default:StdRegProv")
objReg.EnumKey HKLM, strKey, arrSubkeys
WScript.Echo "Installed Applications" & VbCrLf

strComputerSQL = "MSSQLSERVER"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open _
"Provider=SQLOLEDB;Data Source=" & strComputerSQL & ";" & _
"Trusted_Connection=Yes;Initial Catalog=TESTDB"

For Each strSubkey In arrSubkeys
intRet1 = objReg.GetStringValue(HKLM, strKey & strSubkey, _
strEntry1a, strValue1)
If intRet1 <> 0 Then
objReg.GetStringValue HKLM, strKey & strSubkey, _
strEntry1b, strValue1
End If
If strValue1 <> "" Then
WScript.Echo VbCrLf & "Display Name: " & strValue1
End If
objReg.GetStringValue HKLM, strKey & strSubkey, _
strEntry2, strValue2
If strValue2 <> "" Then
WScript.Echo "Install Date: " & strValue2
End If
objReg.GetDWORDValue HKLM, strKey & strSubkey, _
strEntry3, intValue3
objReg.GetDWORDValue HKLM, strKey & strSubkey, _
strEntry4, intValue4
If intValue3 <> "" Then
WScript.Echo "Version: " & intValue3 & "." & intValue4
End If
objReg.GetDWORDValue HKLM, strKey & strSubkey, _
strEntry5, intValue5
If intValue5 <> "" Then
WScript.Echo "Estimated Size: " & Round(intValue5/1024, 3) & " megabytes"
End If
Next
Next

 

[PHV]

Use an INSERT INTO ... VALUES command text.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sotando]

PHV,

I have inserted the insert statment in the script... The script works but no data was populated to the table, below is the revised script.

Undated VBS script:

Const HKLM = &H80000002 'HKEY_LOCAL_MACHINE
On Error Resume Next
arrComputers = Array(".")

For Each strComputer In arrComputers
WScript.Echo
WScript.Echo "===================================="
WScript.Echo "ServerName: "& strComputer
WScript.Echo "===================================="
strKey = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"
strEntry1a = "DisplayName"
strEntry1b = "QuietDisplayName"
strEntry2 = "InstallDate"
strEntry3 = "VersionMajor"
strEntry4 = "VersionMinor"
strEntry5 = "EstimatedSize"

Set objReg = GetObject("winmgmts://" & strComputer & _
"/root/default:StdRegProv")
objReg.EnumKey HKLM, strKey, arrSubkeys
WScript.Echo "Installed Applications" & VbCrLf

strComputerSQL = "MSSQLSERVER"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open _
"Provider=SQLOLEDB;Data Source=" & strComputerSQL & ";" & _
"Trusted_Connection=Yes;Initial Catalog=TESTDB"

For Each strSubkey In arrSubkeys
intRet1 = objReg.GetStringValue(HKLM, strKey & strSubkey, _
strEntry1a, strValue1)
If intRet1 <> 0 Then
objReg.GetStringValue HKLM, strKey & strSubkey, _
strEntry1b, strValue1
End If
If strValue1 <> "" Then
WScript.Echo VbCrLf & "Display Name: " & strValue1
End If
objReg.GetStringValue HKLM, strKey & strSubkey, _
strEntry2, strValue2
If strValue2 <> "" Then
WScript.Echo "Install Date: " & strValue2
End If
objReg.GetDWORDValue HKLM, strKey & strSubkey, _
strEntry3, intValue3
objReg.GetDWORDValue HKLM, strKey & strSubkey, _
strEntry4, intValue4
If intValue3 <> "" Then
WScript.Echo "Version: " & intValue3 & "." & intValue4
End If
objReg.GetDWORDValue HKLM, strKey & strSubkey, _
strEntry5, intValue5
If intValue5 <> "" Then
WScript.Echo "Estimated Size: " & Round(intValue5/1024, 3) & " megabytes"
End If

sql = "INSERT INTO InstalledSoftware (DisplayName, InstallDate, VersionMajor, EstimatedSize) VALUES ('" _
& objItem.strValue1 & "','" & objItem.strValue2 & "','" & objItem.intValue3 & "." & intValue4 _
& "','" & objItem.Round(intValue5/1024, 3) & " megabytes" & "')"
objConnection.Execute(sql)

Next
Next

What am I doing wrong?

Please advice.

Thanks.

 

[sotando]

Does anyone why the data is not being populated to the SQL Server table with my script?

Please advice.

Thanks.

 

[tsuji]

>What am I doing wrong?
Plenty.

[0] By far, the biggest mistake is to put "on error resume next" at the top. That makes people - no need to identify yourself with the people - forever script copier.

[1] To use execute() method, your connection object needs to be flawlessly open. But to login, you need to provide proper credential. To simplify the matter, this modification presume you have a sql server nt user account properly setup with username and password. (In particular when you want to do remoting too!)
[tt]
objConnection.Open _
"Provider=SQLOLEDB;Data Source=" & strComputerSQL & ";" & _
"Trusted_Connection=Yes;Initial Catalog=TESTDB"[red], "<username>", "<password>"[/red]
[/tt]
[1.1] Otherwise, you might use, in case, windows integration authentication for the login user to the os. In that case, provide the integration authentication connection.
[tt]
objConnection.Open _
"Provider=SQLOLEDB;Data Source=" & strComputerSQL & ";" & _
"Trusted_Connection=Yes;Initial Catalog=TESTDB[red];Integrated Security=SSPI;[/red]"
[/tt]

[2] Then you have copied the insert sql without knowing there is a stranger object objItem.
[tt]
sql = "INSERT INTO InstalledSoftware (DisplayName, InstallDate, VersionMajor, EstimatedSize) VALUES ('" _
& strValue1 & "','" & strValue2 & "','" & intValue3 & "." & intValue4 _
& "','" & Round(intValue5/1024, 3) & " megabytes" & "')"
[/tt]
[3] And then, there are some sql related issue. To insert with the above syntax, you have to prepare yourself the data, in particular you need to escape the apostroph in string data. Take the example of displayname which is the most susceptible to contain apostrophes. What you need to do is to add this.
[tt]
strValue1=replace(strValue1,"'","''")
[/tt]
[3.1] Same for any other string data, in fact.

[4] And then the script lines collecting data strValue1, etc are seriously flawed. You may not believe it. Your "on error resume next" blind you all along.

[4.1] Every entry to a certain strSubkey, you need to have them all result in a definite value shielded from their previous value. Some values may not exist for certain subkey, you have to deal with them and give them a desired value, null, empty, 0 or whatever, like "na". At present, the algorithm is seriously defective.

[4.2] I suggest you control the inexistence like you do with intRet1 that suddenly you abandoned the method thereafter.

[4.3] Using round() on something which can be undefined or wrongly defined is also troublesome both for wscript.echo and inside the sql. You won't see it with "on error resume next" directive.

That's enough of corrections to make sofar.