Powershell: "a positional parameter cannot be found that accepts argument 'system.object '"

• Roust_m

  SSCoach

  Points: 17410

  More actions

  January 5, 2012 at 7:41 pm

  #250864

  Hi,

  I am trying to insert some data from a PS command into a table, but getting this error:

  "a positional parameter cannot be found that accepts argument 'system.object '"

  Any ideas?

  Thanks.

  $serverName = "MyServer"

  $databaseName = "DBMonitor"

  $Connection = New-Object System.Data.SQLClient.SQLConnection

  $Output = New-Object WMISearcher

  $Connection.ConnectionString ="Server=$serverName;Database=$databaseName;trusted_connection=true;"

  $Connection.Open()

  $Command = New-Object System.Data.SQLClient.SQLCommand

  $Command.Connection = $Connection

  $Item = @("DeviceId", "MediaType", "Size", "FreeSpace")

  $Output = Get-WmiObject Win32_logicaldisk Format-Table DeviceId, MediaType, Size, FreeSpace -auto

  foreach ($row in $Output) {

  $Command.CommandText ="INSERT into DiskSpace ([Drive], [MediaType], [Size], [FreeSpace]) VALUES ('$($Output.DeviceId)', '$($Output.MediaType)', '$($Output.Size)', '$($Output.FreeSpace)')"

  $Command.ExecuteNonQuery() | out-null

  }

  $Connection.Close()

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  January 6, 2012 at 1:11 am

  #1429469

  You are missing the pipe to Table-Format i.e. what was line 13 for me should be as follows:

  $Output = Get-WmiObject Win32_logicaldisk | Format-Table DeviceId, MediaType, Size, FreeSpace -auto

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• Roust_m

  SSCoach

  Points: 17410

  More actions

  January 8, 2012 at 4:09 pm

  #1430236

  It seems like this fixed the previous error, but when I try to put it into a job step, the job fails with this error message:

  Unable to start execution of step 1 (reason: line(18): Syntax error). The step failed.

  Line 18 would be this one:

  $Command.CommandText ="INSERT into DiskSpace ([Drive], [MediaType], [Size], [FreeSpace]) VALUES ('$($Output.DeviceId)', '$($Output.MediaType)', '$($Output.Size)', '$($Output.FreeSpace)')"

  Strange enough, when I run this script from the powershell command line, it runs without errors, but it inserts twice as many records into the table as there are rows in $output. There are 4 rows in $output and it inserts 8. It would not be so bad, but the data is all zeros:

  Drive,MediaType,Size,FreeSpace

  ,0,0,0

  ,0,0,0

  ,0,0,0

  ,0,0,0

  ,0,0,0

  ,0,0,0

  ,0,0,0

  ,0,0,0

  When I run $output from the command line in powershell, I get the correct information:

  DeviceId MediaType Size FreeSpace

  -------- --------- ---- ---------

  A: 5

  C: 12 146154967040 105137184768

  D: 12 536862916608 18457624576

  E: 11

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 8, 2012 at 4:29 pm

  #1430246

  Please don't cross post. You're wasting resources by getting duplicate answers.

  Original post: http://www.sqlservercentral.com/Forums/FindPost1231228.aspx

  No further replies to this thread please!

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]