ExecuteNonQuery error inserting WMI data into sql server db

• geedeearr

  SSC Eights!

  Points: 800

  More actions

  July 2, 2009 at 8:53 am

  #211639

  I am attempting to learn/use PowerShell. The following script should be a simple insert of wmi computer system data into a table, but it fails with an error of:

  Exception calling "ExecuteNonQuery" with "0" argument(s): "The parameter data type of UInt32 is invalid."

  At :line:40 char:37

  + $RowsInserted = $cmd.ExecuteNonQuery <<<< ()[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null

  # . for the local computer

  # If you want to connect to a remote machine, specify the machine name here.

  $ComputerName = "."

  $CompSys = get-wmiobject -class "Win32_ComputerSystem" -namespace "root\CIMV2" -computername $ComputerName

  # Create SqlConnection object, define connection string, and open connection

  $con = New-Object System.Data.SqlClient.SqlConnection

  $con.ConnectionString = "Server=MySQLServer; Database=MyDatabase; Integrated Security=true"

  $con.Open()

  foreach ($property in $CompSys) {

  # Create SqlCommand object, define command text, and set the connection

  $cmd = New-Object System.Data.SqlClient.SqlCommand

  $cmd.CommandText = "INSERT INTO ComputerInformation(FullComputerName, ComputerDescription, ComputerSystemType, ComputerManufacturer, ComputerModel, NumberProcessors, TotalPhysicalMemory)

  VALUES (@Name, @ComputerDescription, @ComputerSystemType, @ComputerManufacturer, @ComputerModel, @NumberProcessors, @TotalPhysicalMemory)"

  $cmd.Connection = $con

  # Add parameters to pass values to the INSERT statement

  $cmd.Parameters.AddWithValue("@Name", $property.Name) | Out-Null

  $cmd.Parameters.AddWithValue("@ComputerDescription", $property.Description) | Out-Null

  $cmd.Parameters.AddWithValue("@ComputerSystemType", $property.SystemType) | Out-Null

  $cmd.Parameters.AddWithValue("@ComputerManufacturer", $property.Manufacturer) | Out-Null

  $cmd.Parameters.AddWithValue("@ComputerModel", $property.Model) | Out-Null

  $cmd.Parameters.AddWithValue("@NumberProcessors", $property.NumberOfProcessors) | Out-Null

  $cmd.Parameters.AddWithValue("@TotalPhysicalMemory", $property.TotalPhysicalMemory) | Out-Null

  # Execute INSERT statement

  $RowsInserted = $cmd.ExecuteNonQuery() <<<<**ERROR OCCURS HERE**
  }
  $con.Close()

  Could someone point me in the correct direction please?

  Thank you.

• geedeearr

  SSC Eights!

  Points: 800

  More actions

  July 3, 2009 at 7:56 am

  #1019774

  Thanks, but nevermind. I figured it out last night. I had to cast $property.NumberOfProcessors and $property.TotalPhysicalMemory into [Int32]$property.NumberOfProcessors and [Int64]$property.TotalPhysicalMemory. Silly datatypes anyway.

  Thanks.