vbscript logon script getting return data from sql sp

From: "terryteppo" <terryteppo@xxxxxxxxxxxxxxxxxxxxxxxxx>
Date: Wed, 18 May 2005 15:18:03 -0700

i have several vbscripts that run during the logon process. It has been
asked of me to do a complex invendor of the servers in an automated fashion.

i have had problems in the past with stored procedure execution in logon
scripts. But I have figured out a way and have enclosed two examples.

the problem is the new solution/stuff is so complex that i need to return
SCOPE_IDENTITY and use it for the forgien key of another stored procedure for
insert into another table. i have tried adCmdSPStoredProc but it does not
work outside of asp. any idea on returning data from stored procedure in
vbscript used for logon. I like stored procedures becuase the execute much
faster and it keeps the people from having to have access to the tables which
we consider a security breach.

first example:
On Error Resume Next
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from
Win32_NetworkAdapter",,48)
For Each objItem in colItems
if objItem.NetConnectionStatus="2" then
'Wscript.Echo "MAC Address: " & objItem.MACAddress
v1 = ""& objItem.MACAddress
'Wscript.Echo "Manufacturer: " & objItem.Manufacturer
v2 = ""& objItem.Manufacturer
end if
Next
'------------------------------------------
Set IPConfigSet = objWMIService.ExecQuery _
("Select * from Win32_NetworkAdapterConfiguration where IPEnabled=TRUE")
For Each IPConfig in IPConfigSet
If Not IsNull(IPConfig.IPAddress) Then
For i=LBound(IPConfig.IPAddress) to UBound(IPConfig.IPAddress)
'Wscript.Echo "Ip Address: " & IPConfig.IPAddress(i)
'Wscript.Echo "IP Subnet: " & IPConfig.IPSubnet(i)
'Wscript.Echo "Default IP Gateway: " & IPConfig.DefaultIPGateway(i)
'Wscript.Echo "MAC Address: " & IPConfig.MACAddress(i)
'Wscript.Echo "DNS Host Name: " & IPConfig.DNSHostName
v3 = ""& IPConfig.IPAddress(i)
v4 = ""& IPConfig.IPSubnet(i)
v5 = ""& IPConfig.DefaultIPGateway(i)
v6 = ""& IPConfig.MACAddress(i)
v7 = ""& IPConfig.DNSHostName
Next
End If
Next
'-------------------------------

Set colOperatingSystems = objWMIService.ExecQuery _
("Select * from Win32_OperatingSystem")
For Each objOperatingSystem in colOperatingSystems
'Wscript.Echo objOperatingSystem.Caption & " " & objOperatingSystem.Version
'Wscript.Echo objOperatingSystem.ServicePackMajorVersion & "." &
objOperatingSystem.ServicePackMinorVersion
v8 = ""& objOperatingSystem.Caption & " " & objOperatingSystem.Version
v9 = ""& objOperatingSystem.ServicePackMajorVersion & "." &
objOperatingSystem.ServicePackMinorVersion
Next

'----------------------------------------
Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor")
For Each objItem in colItems
'Wscript.Echo "Current Clock Speed: " & objItem.CurrentClockSpeed
'Wscript.Echo "Data Width: " & objItem.DataWidth
'Wscript.Echo "Description: " & objItem.Description
'Wscript.Echo "Device ID: " & objItem.DeviceID
'Wscript.Echo "Family: " & objItem.Family
'Wscript.Echo "Level: " & objItem.Level
'Wscript.Echo "Manufacturer: " & objItem.Manufacturer
'Wscript.Echo "Name: " & objItem.Name
'Wscript.Echo "Processor Id: " & objItem.ProcessorId
'Wscript.Echo "Stepping: " & objItem.Stepping
'Wscript.Echo "Unique Id: " & objItem.UniqueId
'Wscript.Echo "Version: " & objItem.Version

v10 = ""& objItem.CurrentClockSpeed
v11 = ""& objItem.DataWidth
v12 = ""& objItem.Description
v13 = ""& objItem.DeviceID
v14 = ""& objItem.Family
v15 = ""& objItem.Level
v16 = ""& objItem.Manufacturer
v17 = ""& objItem.Name
v18 = ""& objItem.ProcessorId
v19 = ""& objItem.Stepping
v20 = " " '& objItem.UniqueId
v21 = " " '& objItem.Version
Next

'---------------------------
set CmdSP = CreateObject("ADODB.Command")
CmdSP.ActiveConnection = "Provider=SQLOLEDB;Data Source=servername;Initial
Catalog=database;Integrated Security=SSPI;Persist Security Info=False"'
cmdstr= "allinone "&chr(34)& v1 &
chr(34)&","&chr(34)&v2&chr(34)&","&chr(34)&v3&chr(34)&","&chr(34)&v4&chr(34)&","&chr(34)&v5&chr(34)&","&chr(34)&v6&chr(34)&","&chr(34)&v7&chr(34)&","&chr(34)&v8&chr(34)&","&chr(34)&v9&chr(34)&","&chr(34)&v10&chr(34)&","&chr(34)&v11&chr(34)&","&chr(34)&v12&chr(34)&","&chr(34)&v13&chr(34)&","&chr(34)&v14&chr(34)&","&chr(34)&v15&chr(34)&","&chr(34)&v16&chr(34)&","&chr(34)&v17&chr(34)&","&chr(34)&v18&chr(34)&","&chr(34)&v19&chr(34)&","&chr(34)&v8&chr(34)&","&chr(34)&v9&chr(34)
'Wscript.Echo cmdstr
CmdSP.CommandText = "spname "&chr(34)& v1 &
chr(34)&","&chr(34)&v2&chr(34)&","&chr(34)&v3&chr(34)&","&chr(34)&v4&chr(34)&","&chr(34)&v5&chr(34)&","&chr(34)&v6&chr(34)&","&chr(34)&v7&chr(34)&","&chr(34)&v10&chr(34)&","&chr(34)&v11&chr(34)&","&chr(34)&v12&chr(34)&","&chr(34)&v13&chr(34)&","&chr(34)&v14&chr(34)&","&chr(34)&v15&chr(34)&","&chr(34)&v16&chr(34)&","&chr(34)&v17&chr(34)&","&chr(34)&v18&chr(34)&","&chr(34)&v19&chr(34)&","&chr(34)&v8&chr(34)&","&chr(34)&v9&chr(34)
' CmdSP.CommandType = adCmdSPStoredProc
CmdSP.Execute()
Set CmdSP = Nothing
cmdsp.close

second example:

'-------------------------------------------------------------------------------------------------
'
'
'
'
' dec 17 2004
'' create by trt for taliant to install over old version of macromedia flash
' changed because of error with set on macro 12/20/2004
' changed the way we get computer name to be faster 12/20/2004
'--------------------------------------------------------------------------------------------------------
'
'
'
'====================
' error because of mdac version problem

On Error Resume Next

'===================
'
'--------------------------------------------------------------------------------------------------------
' create file system object to check for the flash file
'--------------------------------------------------------------------------------------------------------

Set objFSO = CreateObject("Scripting.FileSystemObject")

'--------------------------------------------------------------------------------------------------------------------------------------------------------
'
' the following nested ifs are because i do not know how to use %systemroot%
'inside the program and the older versions had different file names. This ifs
'attempt to find the file using the known names and locations.
'
'--------------------------------------------------------------------------------------------------------------------------------------------------------

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'check for the flash file location 1
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

If objFSO.FileExists("c:\winnt\system32\Macromed\Flash\Flash.ocx") Then

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'make the variable macroversion equal to the version number of the flash file
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

macroversion =
objFSO.GetFileVersion("c:\winnt\system32\Macromed\Flash\Flash.ocx")

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' call the sub routine what2do it figures out what to do from here
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

call what2do(macroversion,"Flash.ocx","c:\winnt\system32\Macromed\Flash\")

Else

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'check for the flash file location 2
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

If objFSO.FileExists("c:\windows\system32\Macromed\Flash\Flash.ocx") Then

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'make the variable macroversion equal to the version number of the flash
file
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

macroversion =
objFSO.GetFileVersion("c:\windows\system32\Macromed\Flash\Flash.ocx")

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' call the sub routine what2do it figures out what to do from here
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

call what2do(macroversion,"Flash.ocx","c:\windows\system32\Macromed\Flash\")

else
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'check for the flash file location 3
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

If objFSO.FileExists("c:\winnt\system32\Macromed\Flash\swFlash.ocx") Then

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'make the variable macroversion equal to the version number of the flash
file
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

macroversion =
objFSO.GetFileVersion("c:\winnt\system32\Macromed\Flash\swFlash.ocx")

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' call the sub routine what2do it figures out what to do from here
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

call
what2do(macroversion,"swFlash.ocx","c:\winnt\system32\Macromed\Flash\")

else
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'check for the flash file location 4
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

If objFSO.FileExists("c:\windows\system32\Macromed\Flash\swFlash.ocx") Then

'------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'make the variable macroversion equal to the version number of the flash
file
'---------------------------------------------------------------------------------------------------------------------------------------------------------------

macroversion =
objFSO.GetFileVersion("c:\windows\system32\Macromed\Flash\swFlash.ocx")

'------------------------------------------------------------------------------------------------------------------------------------------------------
' call the sub routine what2do it figures out what to do from here
'------------------------------------------------------------------------------------------------------------------------------------------------------

call
what2do(macroversion,"swFlash.ocx","c:\windows\system32\Macromed\Flash\")

else
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' this is if nothing is installed which should not happen
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

macroversion = "no file"
call what2do(macroversion,"nf","nf")

end if
end if
end if

End If

'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' figer out what to do and do it *************** version desicion
subroutine
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

sub what2do(macroversion,filename,filelocation)

'------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' check the version to findout if it is the one we want
'------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

if macroversion="7.0.19.0" then

'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' this is the version we whant
' yes! yaaaaa it is the one now just recored some data in the database
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Set WSHNetwork = WScript.CreateObject("WScript.Network")
computername=WSHNetwork.ComputerName

'-------------------------------------------------------------------------------------------------
' call the sub to connect to sql and input some data
'-------------------------------------------------------------------------------------------------

call sqlinput("1",filename,filelocation,macroversion,computername,"iwish")

else
'--------------------------------------------------------------------------------------------------------------------
' check the version to findout if it is the one we want
'--------------------------------------------------------------------------------------------------------------------

if macroversion="no file" then

'-------------------------------------------------------------------------------------------------
' this bad nothing insgtall per john this is not possible or highly
improbable
'-------------------------------------------------------------------------------------------------

Set WSHNetwork = WScript.CreateObject("WScript.Network")
computername=WSHNetwork.ComputerName

'-------------------------------------------------------------------------------------------------
' call the sub to connect to sql and input some data
'-------------------------------------------------------------------------------------------------

call sqlinput("0",filename,filelocation,macroversion,computername,"iwish")

else

Set WSHNetwork = WScript.CreateObject("WScript.Network")
computername=WSHNetwork.ComputerName

'-------------------------------------------------------------------------------------------------
' call the sub to connect to sql and input some data
'-------------------------------------------------------------------------------------------------

call sqlinput("2",filename,filelocation,macroversion,computername,"hope")

'-------------------------------------------------------------------------------------------------
' call the sub to UPGRADE / INSTALL NEWER VERSION HIGHLY IMPORTANT
'
'--------------------------------------------------------------------------------------------------------------

call installflash

end if
end if

end sub

'-----------------------------------------------------------------------------------------------------------
'
' Installation sub it installs the new verision of the machine this is
really important
' ********************** install flsh subroutine
'-----------------------------------------------------------------------------------------------------------

sub installflash

strComputer = "."
Set objService = GetObject("winmgmts:" &
"{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set objSoftware = objService.Get("Win32_Product")

'-----------------------------------------------------------------------------------------------------------
' the net line is important "the majic happends here folks
'-----------------------------------------------------------------------------------------------------------

errReturn =
objSoftware.Install("\\scfaz.com\dfsrootp\FlashPlayer\mm_fl_sw_installer.msi",,True)
if errReturn =0 then

'-----------------------------------------------------------------------------------------------------------
' do nothing it is ok note: database gets updated next time
'-----------------------------------------------------------------------------------------------------------

else

'-----------------------------------------------------------------------------------------------------------
' if there is an error input that data into database for checking
'-----------------------------------------------------------------------------------------------------------

Set WSHNetwork = WScript.CreateObject("WScript.Network")
computername=WSHNetwork.ComputerName

'-----------------------------------------------------------------------------------------------------------
' call sql input because the wase an erorr during installation and we
need to know
'-----------------------------------------------------------------------------------------------------------

call
sqlinput("3",errreturn,"errorlocation",macroversion,computername,"error")

end if

end sub

'-------------------------------------------------------------------------------------------------------------------
' sql input - inputs data to database
*************************************** sql subroutine
'---------------------------------------------------------------------------------------------------------------------

sub
sqlinput(flashinstalled,flashfilename,flashlocation,flashfileversion,computername,username)

'-------------------------------------------------------------------------------------------------------------------
' create object for connecting
'---------------------------------------------------------------------------------------------------------------------

set CmdSP = CreateObject("ADODB.Command")

'-------------------------------------------------------------------------------------------------------------------
' connection string
'---------------------------------------------------------------------------------------------------------------------

CmdSP.ActiveConnection = "Provider=SQLOLEDB;Data Source=servername;Initial
Catalog=databasename;Integrated Security=SSPI;Persist Security Info=False"
'-------------------------------------------------------------------------------------------------------------------
' sql command to be executed please note this is an sp called flashin on
the other end
'---------------------------------------------------------------------------------------------------------------------

cmdstr= "flashin "&chr(34)&flashinstalled&
chr(34)&","&chr(34)&flashfilename&chr(34)&","&chr(34)&flashlocation&chr(34)&","&chr(34)&flashfileversion&chr(34)&","&chr(34)&computername&chr(34)&","&chr(34)&username&chr(34)
CmdSP.CommandText = "flashin "&chr(34)&flashinstalled&
chr(34)&","&chr(34)&flashfilename&chr(34)&","&chr(34)&flashlocation&chr(34)&","&chr(34)&flashfileversion&chr(34)&","&chr(34)&computername&chr(34)&","&chr(34)&username&chr(34)

'-------------------------------------------------------------------------------------------------------------------
' execute command
'---------------------------------------------------------------------------------------------------------------------

CmdSP.Execute()
Set CmdSP = Nothing
end sub

.

Follow-Ups:
- Re: vbscript logon script getting return data from sql sp
  - From: Richard Mueller [MVP]

Prev by Date: Script to remove the New Mail icon
Next by Date: Getting Error: 800A01AD
Previous by thread: Script to remove the New Mail icon
Next by thread: Re: vbscript logon script getting return data from sql sp
Index(es):
- Date
- Thread

Relevant Pages

WMI Win32_TCPIPPrinterPort InstallDate
... I am trying to use WMI in VBScript to install remote printers. ... that it is correctly installing a port, driver, and printer, I want to ... I am testing it in the sub InstallPrinterPort, ... 'InstallPrinterDriver strDriverName, strDriverSource, strDriverInf ...
(microsoft.public.scripting.vbscript)
Its finally done.
... -25 watts per channel RMS ... JBL BP150.1 Class D subwoofer Amp ... Jensen KS210 DVC 10" Sub ... One of the fantastic things about this install is that you DON'T hear ...
(rec.audio.car)
RowLiner addin How to Start and Stop
... Pearson's www.cpearson.com RowLiner that would do the job just fine. ... If I turn it off manually, run my calc sub and then turn it back on it ... If it is not it needs to install it from a shared folder ... MsgBox "RowLiner add-in is not installed. ...
(microsoft.public.excel.programming)
Re: Sequential Auto Launch of exes
... In the Action Setting, I right clicked on the logo, ... As to the Macro it looked like this: ... Sub Launch() ... Employee\My Documents\IWS Install Files\ ...
(microsoft.public.powerpoint)
Re: RowLiner addin How to Start and Stop
... You can turn the RowLiner's drawing function on and off with VBA ... > If I turn it off manually, run my calc sub and then turn it ... > rowliner addin I need a way to check to see if the RowLiner.xla ... If it is not it needs to install it from a shared ...
(microsoft.public.excel.programming)