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

  )

• Roust_m

  SSCoach

  Points: 17410

  More actions

  January 5, 2012 at 12:08 am

  #1428802

  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

  January 13, 2012 at 4:00 pm

  #1433588

  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

  June 27, 2013 at 12:41 am

  #1627810

  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