Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-query Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7600: ) (VM) 
Microsoft Server 2008 R2 Standard



ProductVersion 10.0.2531.0
ProductLevel   SP1
Edition        Enterprise Edition (64-bit)
EngineEdition  3


In trying to execute the following powershell-script...

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=localhost\myServer; Initial Catalog=SysAdmin; Integrated Security=SSPI")

$conn.Open()

$cmd = $conn.CreateCommand()

gwmi -query "select * from Win32_LogicalDisk where DriveType=3" | select Name, FreeSpace, Size | foreach {
   $Name      = $_.Name.substring(0,1)
   $FreeSpace = $_.FreeSpace
   $Size      = $_.Size

   $cmd.CommandText = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES ('"+ $Name + "', " + $FreeSpace + ", " + $Size + ")"
   $cmd.ExecuteNonQuery()

   $cmd.CommandText = "EXEC dbo.sp_diskspace @performAggregation=1"
   $cmd.ExecuteNonQuery()
}

$conn.Close()


...I get the following error


  Message
  Executed as user: Domain\SqlSrvAgentSer. 
  
  A job step received an error at line 7 in a PowerShell script. The corresponding line is 'gwmi -query "select * from Win32_LogicalDisk where DriveType=3" | select Name, FreeSpace, Size | foreach {'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near ','."  Incorrect syntax near ','.  '.  Process Exit Code -1.  The step failed.


Additional Information

CREATE PROCEDURE dbo.sp_diskspace
   @performAggregation BIT = 0
AS

SET NOCOUNT ON;

DECLARE @aggregrateDate DATETIME

BEGIN
  SET @aggregrateDate = DATEADD(month, -1, GETDATE())
  SET @aggregrateDate = DATEADD(dd, DATEDIFF(dd, 0, @aggregrateDate), 0)

  INSERT INTO dbo.diskspace (
     drive,
     MeasurementDate,
     [free(bytes)],
     [total(bytes)],
     isAggregated)
  SELECT 
      drive,
      DATEADD(dd, DATEDIFF(dd, 0, MeasurementDate), 0), 
      AVG([free(bytes)]),
      AVG([total(bytes)]),
      1
  FROM 
      dbo.diskspace
  WHERE
      MeasurementDate &lt; @aggregrateDate
      AND isAggregated &lt;&gt; 1
  GROUP BY
      drive,
      DATEADD(dd, DATEDIFF(dd, 0, MeasurementDate), 0)

  IF @@ERROR = 0 and @@ROWCOUNT &gt; 0
     BEGIN
        DELETE FROM dbo.diskspace 
        WHERE MeasurementDate &lt; @aggregrateDate 
        AND isAggregated &lt;&gt; 1

        IF @@ERROR = 0
           BEGIN
              RAISERROR('sp_diskspace : aggregation complete', 0, 1)
           END
     END
END


Any help on this would be greatly appreciated!
Don't know if you're still looking for a solution to this but I'd change the Powershell for the insert command just a little.  Add this after the line setting $size = $_.Size

   $sql = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES ('$Name', $FreeSpace, $Size)"
   $sql | Out-File "C:\tmp\debug.sql" –Append # to wherever is appropriate for your system
   $cmd.CommandText = $sql


To me using the extra variable but getting an easier to read insert command is a good tradeoff and the debug.sql file makes finding sql errors much easier.
HTH