6
  • 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!

  • @user1005 - is powershell a requirement for this or would you be equally as satisfied with the vbscript equivalent of this? – johndacostaa Mar 4 '11 at 6:26
  • @user1005 would you share the code of sp_diskspace. looks interesting. : "EXEC dbo.sp_diskspace @performAggregation=1" – Marcello Miorelli Aug 12 '15 at 14:27
2

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

2

You could use parameters to solve this problem with the added benefit of execution plan reuse. Set the data types to match database.

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=ASD-SQL-62\LIVE01BMHUB; Initial Catalog=SysAdmin; Integrated Security=SSPI")

$conn.Open()

$cmd  = $conn.CreateCommand()
$cmd.CommandText = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES (@drive, @freespace, @size)"
$cmd.Parameters.Add("@drive", [System.Data.SqlDbType]::VarChar, 1)
$cmd.Parameters.Add("@freespace", [System.Data.SqlDbType]::Int)
$cmd.Parameters.Add("@size", [System.Data.SqlDbType]::Int)

$cmd2 = $conn.CreateCommand()
$cmd2.CommandText = "dbo.sp_diskspace"
$cmd2.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd2.Parameters.Add("@performAggregation", [System.Data.SqlDbType]::Int).Value = 1

gwmi -query "select * from Win32_LogicalDisk where DriveType=3" | select Name, FreeSpace, Size | foreach {
   $cmd.Parameters["@drive"].Value = $_.Name.substring(0,1)
   $cmd.Parameters["@freespace"].Value = $_.FreeSpace
   $cmd.Parameters["@size"].Value = $_.Size
   $cmd.ExecuteNonQuery()

   $cmd2.ExecuteNonQuery()
}

$conn.Close()
1

In the following part of the INSERT query:

('"+ $Name + "', " + $FreeSpace + ", " + $Size + ")"

If either $FreeSpace or $Size -eq $null, then it won't properly complete the query string. Either use command parameters just as you would in .NET (best method) or check for $null before insert.

  • Thanks Eric. I've had a look at this, but handling the NULLs is not going to help as I rely on information returned to update our monitoring dashboard. Is there any reason by the WMI-select query would return NULL values for the above-mentioned columns? – Nico Botes Feb 28 '11 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.