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: 17405
More actions
January 5, 2012 at 7:38 pm

#262567

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 7 posts - 1 through 6 (of 6 total)

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

azdzn SSCarpal Tunnel Points: 4128 More actions · Answer 1

Change

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

to

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

Roust_m SSCoach Points: 17405 More actions · Answer 2

azdzn (1/6/2012)
Change
$Output = Get-WmiObject Win32_logicaldisk Format-Table DeviceId, MediaType, Size, FreeSpace -auto
to
$Output = Get-WmiObject Win32_logicaldisk | Format-Table DeviceId, MediaType, Size, FreeSpace -auto

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

Roust_m SSCoach Points: 17405 More actions · Answer 3

Roust_m

SSCoach

Points: 17405

January 11, 2012 at 7:10 pm

#1432247

Anyone? :unsure:

azdzn SSCarpal Tunnel Points: 4128 More actions · Answer 4

Here it is :

param ( [string]$ComputerName = "." )

Get-WmiObject -computername "$ComputerName" Win32_LogicalDisk -filter "DriveType=3" | foreach {

add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd"))

add-member -in $_ -membertype noteproperty SizeGB $([math]::round(($_.Size/1GB),2))

add-member -in $_ -membertype noteproperty FreeGB $([math]::round(($_.FreeSpace/1GB),2))

add-member -in $_ -membertype noteproperty PercentFree $([math]::round((([float]$_.FreeSpace/[float]$_.Size) * 100),2)) -passThru } |

Select UsageDT, SystemName, DeviceID, VolumeName, SizeGB, FreeGB, PercentFree

This comes from this article :

http://www.sqlservercentral.com/articles/powershell/65196/

Roust_m SSCoach Points: 17405 More actions · Answer 5

azdzn (1/12/2012)
Here it is :
param ( [string]$ComputerName = "." )
Get-WmiObject -computername "$ComputerName" Win32_LogicalDisk -filter "DriveType=3" | foreach {
add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd"))
add-member -in $_ -membertype noteproperty SizeGB $([math]::round(($_.Size/1GB),2))
add-member -in $_ -membertype noteproperty FreeGB $([math]::round(($_.FreeSpace/1GB),2))
add-member -in $_ -membertype noteproperty PercentFree $([math]::round((([float]$_.FreeSpace/[float]$_.Size) * 100),2)) -passThru } |
Select UsageDT, SystemName, DeviceID, VolumeName, SizeGB, FreeGB, PercentFree
This comes from this article :
http://www.sqlservercentral.com/articles/powershell/65196/

Yeah, but this article does not mention how do I insert the data into the table. I've got my script working from the PS command line, but, when run from a job, it complains about syntax error in the insert command.

$serverName = "."

$databaseName = "DBMonitor"

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

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

$Connection.Open()

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

$Command.Connection = $Connection

Get-WmiObject Win32_logicaldisk | %{

$Command.CommandText ="INSERT into DiskSpace ([Drive], [MediaType], [Size], [FreeSpace]) VALUES ('$($_.DeviceId)', '$($_.MediaType)', '$($_.Size)', '$($_.FreeSpace)')" >> c:\log.txt

$Command.ExecuteNonQuery() | out-null

}

$Connection.Close()

Roust_m SSCoach Points: 17405 More actions · Answer 6

Worked this around by putting the script into the text file and running this from the job step:

c:\temp\DSM.ps1

Everything works now, but it would be interesting to find out how to make it work when the script is inside the job step, as synchronizing those text files across multiple servers is a bit of a headache.