The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

dmerida (13) [Avatar] Offline
#1
Hello all, I'm faced with an interesting scenario and would like some input as to what would be the best way to solve this problem. What I'm trying to do is gather some OS information from a list of remote servers and populate the data into a SQL Server database.

I am using Get-Wmiobject win32_operatingsystem to get some OS and service pack information from remote computers. I then store this information in a variable. So my command looks something like this:

PS> $command = gwmi win32_operatingsystem -computer $computers | select caption, othertypedescription, servicepackmajorversion, version

I've created a database with a table named ServicePack. I have the following columns: ServicePackLevel, OSName, OSVersion, OSBuild. Here is what I do next:

PS> foreach($item in $command){
>> $query = "Update ServicePack Set ServicePackLevel = '$item.servicepackmajorversion', OSName = '$item.caption', OSVers
ion = '$item.othertypedescription', OSBuild = '$item.version'"
>> $cmd = New-Object system.data.oledb.oledbcommand
>> $cmd.connection = $conn
>> $cmd.commandtext = $query
>> $cmd.executenonquery()
>> }

The error I get is:
Exception calling "ExecuteNonQuery" with "0" argument(s): "The statement has been terminated.
String or binary data would be truncated."

I understand that $item.caption, $item.version, etc. are system.string objects (except $item.servicepackmajorversion, that's a system.uint16 object). But of course, to enter data into a database, you need to surround your values with single quotes. '$item.caption' will only give you the literal value $item.caption. But "$item.caption" gives you:

@{caption=Microsoft(R) Windows(R) Server 2003, Standard Edition; othertypedescription=R2; servicepackmajorversion=2; version=5.2.3790}.caption

So my command $query = "Update ServicePack Set ServicePackLevel = '$item.servicepackmajorversion', OSName = '$item.caption', OSVers
ion = '$item.othertypedescription', OSBuild = '$item.version'" won't work because each value gets expanded to the complete string, instead of the piece I want.


See why this is an interesting scenario? This was probably a little verbose, so please feel free to ask for more clarification. Thanks in advance!
dmerida (13) [Avatar] Offline
#2
Re: Tough scenario with PowerShell and SQL 2005
Nevermind, a colleague and I managed to figure this one out. Seeing as how PowerShell evaluates everything between double-quotes and gives you the literal value of anything between single quotes, it involved some trickery and a lack of finesse. Here's what we did:

PS C:> $command = gwmi -query "select caption, othertypedescription, servicepackmajorversion, version from win32_operatingsystem"
-computer $computers | select caption, othertypedescription, servicepackmajorversion, version
PS C:> $sinquote = "'"
PS C:> foreach($item in $command){
>> $version = $sinquote+$item.othertypedescription+$sinquote
>> $sp = $sinquote+$item.servicepackmajorversion+$sinquote
>> $build = $sinquote+$item.version+$sinquote
>> $caption = $sinquote+$item.caption+$sinquote
>> $query = "Insert into ServicePack (ServicePackLevel, OSName, OSVersion, OSBuild) values ($sp, $caption, $version, $build)"
>> $cmd = New-Object system.data.oledb.oledbcommand
>> $cmd.connection = $conn
>> $cmd.commandtext = $query
>> $cmd.executenonquery()
>> }

Checked out SSMS and all my information is in my database where everything is supposed to be. Is this the best solution? Probably not. I'm sure there are dozens of ways I could have approached this to get the results I wanted. But it worked...

Thanks anyway to those who viewed this thread and who, like myself, scratched their head in confusion =)
mosaic (6) [Avatar] Offline
#3
Re: Tough scenario with PowerShell and SQL 2005
If you use the parameter collection of the command object, you won't need to quote literal values.
Using parameters also protects against SQL injection. In your case that does not matter because the input comes from WMI and will be clean, but in cases where the parameters come from user input it is important.

Outside the foreach loop
Open the connection and create the command
Use placeholders in the INSERT command text, like this:
$query = "Insert into ServicePack (ServicePackLevel, OSName, OSVersion, OSBuild) values (?, ?, ?, ?)
Add four named parameters to the command
$cmd.parameters.add("@SPLevel", [System.Data.OleDb.OleDbType]::varchar, 5)
$cmd.parameters.add("@OSName", [System.Data.OleDb.OleDbType]::varchar, 50)
$cmd.parameters.add("@OSVersion", [System.Data.OleDb.OleDbType]::varchar, 10)
$cmd.parameters.add("@OSBuild", [System.Data.OleDb.OleDbType]::varchar, 10)
Tell SQL server to compile the command - it will re-use the query plan each time you run the command
$cmd.Prepare()

In the foreach loop
Set the value of each parameter and run the query, like this:
$cmd.parameters["@SPLevel"].value = $item.servicepackmajorversion
$cmd.parameters["@OSName"].value = $item.caption
$cmd.parameters["@OSVersion"].value = $item.othertypedescription
$cmd.parameters["@OSBuild"].value = $item.version
$cmd.executenonquery()