InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 01:43:54 GMT20http://www.sqlservercentral.com/Forums/Topic1231228-1550-1.aspxWorked this around by putting the script into the text file and running this from the job step:c:\temp\DSM.ps1Everything 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.Thu, 12 Jan 2012 20:19:38 GMTRoust_mhttp://www.sqlservercentral.com/Forums/Topic1231228-1550-1.aspx[quote][b]azdzn (1/12/2012)[/b][hr]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, PercentFreeThis comes from this article :http://www.sqlservercentral.com/articles/powershell/65196/[/quote]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.[code="plain"]$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 = $ConnectionGet-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()[/code]Thu, 12 Jan 2012 19:03:17 GMTRoust_mhttp://www.sqlservercentral.com/Forums/Topic1231228-1550-1.aspxHere 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, PercentFreeThis comes from this article :http://www.sqlservercentral.com/articles/powershell/65196/Thu, 12 Jan 2012 01:49:02 GMTazdznhttp://www.sqlservercentral.com/Forums/Topic1231228-1550-1.aspxAnyone? :unsure:Wed, 11 Jan 2012 19:10:48 GMTRoust_mhttp://www.sqlservercentral.com/Forums/Topic1231228-1550-1.aspx[quote][b]azdzn (1/6/2012)[/b][hr]Change$Output = Get-WmiObject Win32_logicaldisk Format-Table DeviceId, MediaType, Size, FreeSpace -auto to $Output = Get-WmiObject Win32_logicaldisk [b]|[/b] Format-Table DeviceId, MediaType, Size, FreeSpace -auto[/quote]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,0When I run $output from the command line in powershell, I get the correct information:DeviceId MediaType Size FreeSpace-------- --------- ---- ---------A: 5C: 12 146154967040 105137184768D: 12 536862916608 18457624576E: 11 Sun, 08 Jan 2012 16:11:20 GMTRoust_mhttp://www.sqlservercentral.com/Forums/Topic1231228-1550-1.aspxChange$Output = Get-WmiObject Win32_logicaldisk Format-Table DeviceId, MediaType, Size, FreeSpace -auto to $Output = Get-WmiObject Win32_logicaldisk [b]|[/b] Format-Table DeviceId, MediaType, Size, FreeSpace -autoFri, 06 Jan 2012 02:41:26 GMTazdznhttp://www.sqlservercentral.com/Forums/Topic1231228-1550-1.aspxHi,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() Thu, 05 Jan 2012 19:38:25 GMTRoust_m