Jeff

Below is routine for your specific needs using Stored Procedure with Table Valued Parameter (based upon schleep's contribution).

The huge advantages here are NO Dynamic SQL and a SINGLE call to SQL Server.

###############################################################################

First, the SQL Server bits:

CREATE TYPE DiskSpaceUpdateTable AS

TABLE

(

[DiskSpace_ImportID] [int] NOT NULL,

[SystemName] [varchar](128) NULL,

[DeviceID] [char](2) NULL,

[VolumeName] [varchar](128) NULL,

[Size] [bigint] NULL,

[FreeSpace] [bigint] NULL

PRIMARY KEY CLUSTERED

(

[DiskSpace_ImportID] ASC

) WITH (IGNORE_DUP_KEY = OFF)

)

CREATE PROCEDURE [dbo].[UpdateDiskSpace]

@DSUTable DiskSpaceUpdateTable READONLY

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO [dbo].[DiskSpace]

(

[SystemName]

,[DeviceID]

,[VolumeName]

,[Size]

,[FreeSpace]

)

SELECT

[SystemName]

,[DeviceID]

,[VolumeName]

,[Size]

,[FreeSpace]

FROM @DSUTable

ORDER BY [DiskSpace_ImportID]

END

###############################################################################

Now the PowerShell bit:

# Use a Stored Procedure with a Table Valued Parameter to send disk data to SQL database

# The advantage here is no Dynamic SQL and a single call to SQL Server

# Out-Null suppresses output to screen which happens when no assignment

# Construct a data table to hold the disk data

$driveTable = New-Object System.Data.DataTable

$driveTable.Columns.Add("ID", "System.Int32") | Out-Null

$driveTable.Columns.Add("SystemName", "System.String") | Out-Null

$driveTable.Columns.Add("DeviceID", "System.String") | Out-Null

$driveTable.Columns.Add("VolumeName", "System.String") | Out-Null

$driveTable.Columns.Add("Size", "System.Int64") | Out-Null

$driveTable.Columns.Add("FreeSpace", "System.Int64") | Out-Null

# Get drive info from System

$drives = Get-WmiObject Win32_LogicalDisk -computer 'SomeComputerName' | Select SystemName, DeviceID, VolumeName, Size, FreeSpace

# Insert data for $drives into $driveTable - this is the RBAR bit

$row = 1 # This is the row ID

foreach ($drive in $drives)

{

# Test for Null Values

if ($drive.SystemName -eq $null) { $drive.SystemName = "" }

if ($drive.DeviceID -eq $null) { $drive.DeviceID = "" }

if ($drive.VolumeName -eq $null) { $drive.VolumeName = "" }

if ($drive.Size -eq $null) { $drive.Size = 0 }

if ($drive.FreeSpace -eq $null) { $drive.FreeSpace = 0 }

$driveTable.Rows.Add($row, $drive.SystemName, $drive.DeviceID, $drive.VolumeName, $drive.Size, $drive.FreeSpace) | Out-Null

# Next row ID

$row++

}

# Uncomment next line to display driveTable contents

#$driveTable.Rows

# Ready to transfer data to SQL database table

# Open Connection

$conn = New-Object System.Data.SqlClient.SqlConnection

$connectionString = "Server=TestServer;Database=TestDB;Integrated Security=True;Connect Timeout=0"

$conn.ConnectionString = $connectionString

$conn.Open()

# Create the Command object to execute the Stored Procedure

$cmd = New-Object System.Data.SqlClient.SqlCommand

$cmd.Connection = $conn

$cmd.CommandType = [System.Data.CommandType]::StoredProcedure

$cmd.CommandText = "UpdateDiskSpace"

$cmd.Parameters.Add("@DSUTable", [System.Data.SqlDbType]::Structured) | Out-Null

$cmd.Parameters["@DSUTable"].Value = $driveTable

# Execute Command ONCE to insert data for ALL $drives

$cmd.ExecuteNonQuery() | Out-Null

# Tidy Up

$conn.Close()

###############################################################################