Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  • 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 ProductLevel Edition EngineEdition

10.0.2531.0 SP1 Enterprise Edition (64-bit) 3

In trying to execute the following powershell-script:

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=ASD-SQL-62\LIVE01BMHUB; 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:

/* Use ALTER so as not to destroy any permissions - just in case */ CREATE PROCEDURE dbo.sp_diskspace @performAggregation BIT = 0 /***********************************************

Author : Unknown - rewritten by xxx to support new SQL2008 Job Created : Unknown - rewritten March 2010 Purpose : Return latest disk space info SQL Svr : 2005, 2008

Testing : EXEC dbo.sp_diskspace

***********************************************/ AS /* Displays the free space,free space percentage plus total drive size for a server */ SET NOCOUNT ON;

/* 20100414 : RD : introduced aggregation to keep number of tables rows down */ DECLARE @aggregrateDate DATETIME

IF @performAggregation <> 1 BEGIN WITH Latest (drive, MeasurementDate) AS ( SELECT drive, MAX(MeasurementDate) FROM dbo.DiskSpace GROUP BY drive )

  SELECT 
     DS.drive,
     DS.[free(mb)],
     DS.[total(mb)],
     DS.[free(%)]
  FROM dbo.DiskSpace DS
     JOIN Latest L
        ON L.drive = DS.drive
        AND L.MeasurementDate = DS.MeasurementDate 
  ORDER BY
     DS.drive

END ELSE 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!

share|improve this question
    
@user1005 - is powershell a requirement for this or would you be equally as satisfied with the vbscript equivalent of this? –  John DaCosta Mar 4 '11 at 6:26
add comment

3 Answers

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()
share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
    
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? –  user1005 Feb 28 '11 at 11:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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