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)

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

StackExchange Database Administrators Q#1403, answer score: 2