Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Roust_m
Roust_m
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1068 Visits: 1953
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()



azdzn
azdzn
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1554 Visits: 289
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
Roust_m
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1068 Visits: 1953
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
,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



Roust_m
Roust_m
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1068 Visits: 1953
Anyone? Unsure



azdzn
azdzn
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1554 Visits: 289
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
Roust_m
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1068 Visits: 1953
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
Roust_m
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1068 Visits: 1953
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search