Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Powershell: "a positional parameter cannot be found that accepts argument 'system.object '" Expand / Collapse
Author
Message
Posted Thursday, January 5, 2012 7:38 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 PM
Points: 987, Visits: 1,634
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()






Post #1231228
Posted Friday, January 6, 2012 2:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:52 PM
Points: 1,511, Visits: 254
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



Post #1231309
Posted Sunday, January 8, 2012 4:11 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 PM
Points: 987, Visits: 1,634
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





Post #1232158
Posted Wednesday, January 11, 2012 7:10 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 PM
Points: 987, Visits: 1,634
Anyone?


Post #1234506
Posted Thursday, January 12, 2012 1:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:52 PM
Points: 1,511, Visits: 254
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/




Post #1234565
Posted Thursday, January 12, 2012 7:03 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 PM
Points: 987, Visits: 1,634
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()



Post #1235334
Posted Thursday, January 12, 2012 8:19 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 PM
Points: 987, Visits: 1,634
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.



Post #1235342
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse