How to insert the results of a powershell command into a table?

Question

Post reply

How to insert the results of a powershell command into a table?

Roust_m

SSCoach

Points: 17410
More actions
January 4, 2012 at 8:33 pm

#249135

Hi,
I've got a PS command and a table on an SQL server below. How do I insert the output of the command into the table?
Thanks.
$Item = @("DeviceId", "MediaType", "Size", "FreeSpace")
Get-WmiObject -query "Select * from Win32_logicaldisk" | Format-Table $item -auto
CREATE TABLE [dbo].[DiskSpace](
[Drive] [char](2) NOT NULL,
[MediaType] [smallint] NOT NULL,
[Size] [bigint] NULL,
[FreeSpace] [bigint] NULL
)

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Roust_m SSCoach Points: 17410 More actions · Answer 1

Tried different things, coming closer now:

$serverName = "MyServer"

$databaseName = "DBMonitor"

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Output = New-Object WMISearcher

$Connection.ConnectionString ="Server=$serverName;Database=$databaseName;trusted_connection=true;"

$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand

$Command.Connection = $Connection

$Item = @("DeviceId", "MediaType", "Size", "FreeSpace")

$Output = Get-WmiObject Win32_logicaldisk Format-Table DeviceId, MediaType, Size, FreeSpace -auto

foreach ($row in $Output) {

$Command.CommandText ="INSERT into DiskSpace ([Drive], [MediaType], [Size], [FreeSpace]) VALUES ('$($Output.DeviceId)', '$($Output.MediaType)', '$($Output.Size)', '$($Output.FreeSpace)')"

$Command.ExecuteNonQuery() | out-null

}

$Connection.Close()

But still getting errors:

"a positional parameter cannot be found that accepts argument 'system.object '"

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 2

You can use invoke-sqlcmd passing in an insert query to do this or you could use bulk insert.

Here's an example from my blog http://sirsql.net/blog/2011/5/5/checking-database-space-with-powershell.html

Shamless self promotion - read my blog http://sirsql.net

Stefan Sørensen SSC Rookie Points: 43 More actions · Answer 3

DECLARE @ps VARCHAR(1000)

SET @ps = N'powershell.exe -c "$Item = @(''DeviceId'', ''MediaType'', ''Size'', ''FreeSpace'');

Get-WmiObject -query ''Select * from Win32_logicaldisk'' | Format-Table $item -auto"'

CREATE TABLE #DiskSpace(

[Drive] [char](2) NOT NULL,

[MediaType] [smallint] NOT NULL,

[Size] [bigint] NULL,

[FreeSpace] [bigint] NULL

)

iNSERT INTO #DiskSpace (Drive, MediaType, Size, FreeSpace)

EXEC xp_cmdshell @ps

You have to have execute rights on xp_cmdshell