OSD VBscript/MSSQL issue

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

OSD VBscript/MSSQL issue

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> OSD VBscript/MSSQL issue

Page: [1]

Message

<< Older Topic Newer Topic >>

OSD VBscript/MSSQL issue - 7/3/2008 7:10:27 PM

ScottGill

Posts: 31
Score: 0
Joined: 3/1/2007
Status: offline

I'm working on a system similar to BDD/ZTI for our techs to use. Unfortunately, the role based system in SCCM is not going to work correctly in our environment as we don't really have roles so much as users requesting whatever software they want and getting it (that's a whole other story).

Anyway, I've built a table in the SCCM database and a web based front-end for our techs to use to image new machines entering such variables as which image to use, the machine name, asset tag, and what applications to install. I wrote a VBScript to put the basic information into the database when the OSD process starts for new machines (Service Tag, CPU and other info)...

My problem is that this script works perfectly in Windows, but I cannot for the life of me get it to work correctly in WinPE. The really difficult part is that the script is not throwing any kind of errors so I don't know what's wrong. Below is the script I'm using... Thanks for any help.

P.S. The actual query containing the real data is at the bottom, commented out b/c I was trying to see if there may have been some kind of variable issue.

quote:

Dim env, strSerialNumber, sccm

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")

Set colSMBIOS = objWMIService.ExecQuery("Select * from Win32_SystemEnclosure")
For Each objSMBIOS in colSMBIOS
strSerialNumber = objSMBIOS.SerialNumber
Next

Set sccm = CreateObject("ADODB.Connection")
sccm.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sccm;password=<password>;Initial Catalog=<database>;Data Source=<server>"
sccm.Open
strQuery = "INSERT INTO Image_Staging (Name, Serial, ProcType, ProcDataWidth, Model, Memory, OS, Marked) VALUES ('5555555', '5555555', 'Intel', 64, 'Optiplex 755', 4000000000, 'GD100001', 0)"
sccm.execute(strQuery)

Set objWMIService = GetObject("winmgmts:\\.\root\CIMV2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem",,48)
For Each objItem in colItems
strModel = objItem.Model
strMemory = objItem.TotalPhysicalMemory
Next

Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Processor",,48)
For Each objItem in colItems
strProcCaption = objItem.Caption
strProcDataWidth = objItem.DataWidth
Next

If Left(strProcCaption, 5) = "AMD64" Then
strProcType = "AMD64"
Else
strProcType = "Intel"
End If

If strProcType = "AMD64" Then
ImgID = "GD10005F"
ElseIf strProcType = "Intel" AND strMemory > 3000000000 Then
ImgID = "GD100067"
ElseIf strProcType = "Intel" AND strMemory < 3000000000 Then
ImgID = "GD100063"
End If

SET env = CreateObject("Microsoft.SMS.TSEnvironment")
env("ImgID") = ImgID

'strQuery = "INSERT INTO Image_Staging (Name, Serial, ProcType, ProcDataWidth, Model, Memory, OS, Marked) VALUES ('" & strSerialNumber & "', '" & strSerialNumber & "', '" & strProcType & "', " & strProcDataWidth & ", '" & strModel & "', " & strMemory & ", '" & ImgID & "', 0)"
'sccm.execute(strQuery)

< Message edited by ScottGill -- 7/3/2008 7:12:55 PM >

Post #: 1

RE: OSD VBscript/MSSQL issue - 7/4/2008 12:09:27 AM

rbennett806

Posts: 739
Score: 11
Joined: 6/14/2006
Status: offline

I had to fight a bunch of things to build a custom frontend so I can relate to what you're doing. I think your proble might be the code you're using to connect to your database. What works on a machine bound to the domain won't work on a machine not bound to the domain (like Windows PE). So when testing make sure to use a machine not bound to a domain. While this won't exactly mimic the Windows PE environment, you can get rid of the first layer of bugs in your code.

Here's an example code snippet of one way to connect...

Const ADS_SECURE_AUTHENTICATION = "&H1"
Const ADS_SERVER_BIND = "&H200"
Set objNS = GetObject("LDAP:")
Set objRootDSE = objNS.OpenDSObject("LDAP://" & strDomainControllerFQDN & "/RootDSE", strUserName, strPassword, ADS_SERVER_BIND Or ADS_SECURE_AUTHENTICATION)
strDNSDomain = objRootDSE.Get("defaultNamingContext")
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Properties("User ID") = strUserName
adoConnection.Properties("Password") = strPassword
adoConnection.Properties("Encrypt Password") = True
adoConnection.Properties("ADSI Flag") = ADS_SERVER_BIND Or ADS_SECURE_AUTHENTICATION
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = adoConnection
adoCommand.CommandText = "Select Name from 'LDAP://" & strDomainControllerFQDN & "/" & strDNSDomain & "' " & "Where objectCategory='Computer'"
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False
Set adoRecordset = adoCommand.Execute

It's really rough (and just a quick Copy/Paste from a much larger script), but it may get you on the right track.

You might also take a look at the HTA found here: http://www.myitforum.com/articles/11/view.asp?id=11652 for some other code examples...

(in reply to ScottGill)

Post #: 2

RE: OSD VBscript/MSSQL issue - 7/8/2008 1:47:20 PM

ScottGill

Posts: 31
Score: 0
Joined: 3/1/2007
Status: offline

Thanks, but that looks like a method to connect to AD, not SQL Server.

After some more digging I found that there was an error buried in the log. It seems that the DLL's necessary for SQL connections are not included in the latest WinPE. (I'm using WAIK SP1 with SCCM SP1 so I'm assuming it's the latest). I'm working on getting them added in there now but having a hell of a time with it. The directions from MyITForum on how to build custom WinPE images doesn't seem to be working for me, I get an error everytime I try to build in packages. So instead I've manually copied over the DLL's and other files from with Vista image and loaded the WinPE registry in offline mode and added in the keys necessary to register the DLL's (at least I think I got them all). I'm not getting that same error now but it's still not functioning right.

This is the error I was getting before (replacing the actual server path with \\server\path):

quote:

????4???????????4??????????????????????????????????Command line returned 0 Entering ReleaseSource() for \\server\pathreference count 1 for the source \\server\path\ before releasing Released the resolved source \\server\path\\\server\path\SCCMDBInsert.vbs(4, 1) Microsoft VBScript runtime error: ActiveX component can't create object: 'ADODB.Connection' ??????????????????????????????????????????????4??????????????????????????›?????????.

And this is what I'm getting now:

quote:

????4???????????4??????????????????????????????????Command line returned 0 Entering ReleaseSource() for \\server\pathreference count 1 for the source \\server\path\ before releasing Released the resolved source \\server\path\.

The first part on both look similar enough so I'm wondering if maybe it's the same error and for whatever reason SCCM is not parcing the status message correctly this time. I'm still tinkering with the WinPE image to try to get this working. Anyone else ever done this before?

(in reply to rbennett806)

Post #: 3

RE: OSD VBscript/MSSQL issue - 7/8/2008 2:56:20 PM

ScottGill

Posts: 31
Score: 0
Joined: 3/1/2007
Status: offline

Ok I think I'm on the right track... slowly getting there. Last test I got an error saying it couldn't find the module, and now I'm getting an error about "Failed to start because MSDART.DLL was not found." Now all I have to do is find that and get it properly registered in WinPE and that should do it (I hope).

(in reply to ScottGill)

Post #: 4

RE: OSD VBscript/MSSQL issue - 7/8/2008 7:38:45 PM

rbennett806

Posts: 739
Score: 11
Joined: 6/14/2006
Status: offline

Yeah, you gotta hate it when you post the wrong thing :)

So for anyone else stumbling across this post, here's how I connect to SCCM from within my HTA frontend...

Function ConnectToSCCM
' Attempts to use the provided information to connect to the SCCM environment.
On Error Resume Next
Set objLocator = CreateObject("WbemScripting.SWbemLocator")
Set objSMS = objLocator.ConnectServer(strSCCMServerFQDN, "root\sms\site_" & strSiteCode, strAccountUserName, strAccountPassword)
If Err then
   Err.Clear
   ConnectToSCCM = "NoAccess"
   Exit Function
End If
ConnectToSCCM = "Accessed"
End Function

I'm sure you can figure out the variables being used...

(in reply to ScottGill)

Post #: 5

RE: OSD VBscript/MSSQL issue - 7/9/2008 1:38:10 PM

ScottGill

Posts: 31
Score: 0
Joined: 3/1/2007
Status: offline

I thought about that already, the problem is when you connect to SCCM through WMI, you're limited to the built in SCCM functions. I'm just trying to connect directly to a SQL DB to make some changes. I am getting further however. I now have it to where all I need is to somehow get the provider SQLOLEDB.1 to be recognized in WinPE. I've got the drivers all in there and most of the registry entries... I'm pretty sure I'm just missing a registry entry somewhere, or something like that.

(in reply to rbennett806)

Post #: 6