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

Question

Post reply

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()

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

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 · Answer 2

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

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 · Answer 3

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]