- 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 < @aggregrateDate
AND isAggregated <> 1
GROUP BY
drive,
DATEADD(dd, DATEDIFF(dd, 0, MeasurementDate), 0)
IF @@ERROR = 0 and @@ROWCOUNT > 0
BEGIN
DELETE FROM dbo.diskspace
WHERE MeasurementDate < @aggregrateDate
AND isAggregated <> 1
IF @@ERROR = 0
BEGIN
RAISERROR('sp_diskspace : aggregation complete', 0, 1)
END
END
END
Any help on this would be greatly appreciated!