Query SQL eXpress database

pollewops · 03-12-2009, 05:11 PM

I would like to, using a vbs script, query the SQL DS eXpress database and create a table with information like computername, ip address, mac address.

Is there someone sho can help me out on this ?

TRYDL · 04-06-2009, 02:19 PM

This came out of the MSFT script center

Quote:

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider=SQLOLEDB;Data Source=MYExpressDBServer;" & _
"Trusted_Connection=Yes;Initial Catalog=express;" & _
"User ID=DOMAINNAME\USERNAME;Password=PASSWORD;"

objRecordSet.Open "SELECT * FROM Computer", _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo objRecordSet.RecordCount

Anything particular you are looking for?

pollewops · 04-07-2009, 02:53 AM

Thanks for the update. Maybe I can use that as well, but I found already something else.
See my script code below. This works perfect !

Quote:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = wscript.createobject ("WScript.Shell")
MyServer="<sql_db_server_name>"
Set DBCon = CreateObject("ADODB.Connection")
DBCon.Open "Provider=SQLOLEDB;Server=" & MyServer & ";database=eXpress;Trusted_Connection=yes"
strHeader = "computername,groupname,serial,macaddress,ipaddres s,computertype,manufacturer,model,ostype,lastupdat e"
wscript.echo strHeader
objReportFile.WriteLine strHeader
SQL = "select comp.computer_name,grp.name,comp.serial_num,n.mac_ addr,netw.ip_address,comp.prod_name,comp.manuf,com p.model_num,comp.os,ses.last_update from dbo.computer comp inner join dbo.nics n ON comp.computer_id = n.computer_id inner join dbo.computer_display netw ON comp.computer_id = netw.computer_id inner join dbo.computer_group grp ON comp.group_id = grp.group_id left join dbo.sessions ses ON comp.computer_id = ses.computer_id order by comp.computer_name asc"
Set TargetGUID = DBCon.Execute(SQL)
Do While TargetGUID.EOF = False
MyGUID = TargetGUID.Fields("computer_name").Value & "," & TargetGUID.Fields("name").Value & "," & TargetGUID.Fields("serial_num").Value & "," & TargetGUID.Fields("mac_addr").Value & "," & trim(TargetGUID.Fields("ip_address").Value) & "," & TargetGUID.Fields("prod_name").Value & "," & TargetGUID.Fields("manuf").Value & "," & TargetGUID.Fields("model_num").Value & "," & TargetGUID.Fields("os").Value & "," & TargetGUID.Fields("last_update").Value
wscript.echo MyGUID
select case TargetGUID.Fields("model_num").Value
case "098Ch"
AmountT5710 = AmountT5710 + 1
case "0A24h"
AmountT5720 = AmountT5720 + 1
case Else
AmountTOther = AmountTOther + 1
end select
AmountTOT = AmountTOT + 1
TargetGUID.MoveNext
Loop
DBCon.Close