SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Write PowerShell Output to SQL Server Table


Write PowerShell Output to SQL Server Table

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216968 Visits: 41991
MG-148046 (6/21/2013)
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.


I love those quotes. Well done.

Thanks for the feedback. Putting my toes back into procedural code waters is a bit chilling for me. I've not really gone near procedural code since I gave it up back in 2002.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jonbes
jonbes
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 862
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()

###############################################################################
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40122 Visits: 14413
calvo shows the approach I use, namely:

1. Get a PowerShell Object.
2. Convert PowerShell object to a .NET DataTable
3. Load DataTable into a SQL Server table using SqlBulkCopy.

SqlBulkCopy is a .NET object that accesses the same Bulk Load APIs both bcp.exe and T-SQL's BULK INSERT access. So no RBAR, and yes, using it will be much faster than issuing individual selects for the same reasons why bcp is faster than reading a file line by line and issuing a series of inserts. This works great for small to medium sized data sets because SqlBulkCopy loads data from memory, and in the general case this means the entire data set is held in memory so the limitation is the hardware. For larger data sets writing to a file and then using bcp.exe will be a better option.



You mentioned:

I was hoping to avoid the out-datable (God bless the "Scripting Guy"!) function because I may have several columns in the table that won't be populated by PowerShell.

No worries. If you want to pre-load a bunch of derived columns in the PowerShell object destined for your table you have a few options:

Option 1. Add columns as part of the initial select:

$pso = Get-WmiObject Win32_LogicalDisk -computer 'Orlando' | Select SystemName,DeviceID,VolumeName,Size,FreeSpace,@{Name="ExtraColumn";Expression={[int]0}}

What I added is called a 'calculated property'. I added a typed-constant as a sample but you can use the other columns in the pipeline in the Expression as well. For example, you could calculate the percent free from the Size and FreeSpace values to store in your table.

Option 2. After the PowerShell object is created you can add columns using Add-Member:

$pso | Add-Member -MemberType NoteProperty -Name ExtraColumn -Value "0" #add a column as a string
$pso # show data
$pso | Get-Member # show columns

$pso | Add-Member -MemberType NoteProperty -Name ExtraColumn2 -Value 0 #added another column as an int
$pso # show data
$pso | Get-Member # show columns

Option 3. Do nothing.

Well, usually nothing depending on your table structure. If the columns in your database table that are not in your DataTable exist ordinally after your DataTable columns and you want them to assume the column-default from the database then you do not need to do anything. Write-DataTable does not explicitly configure SqlBulkCopy so the columns in your DataTable will be mapped to the columns in your DataTable ordinally, and trailing columns in your database table will be left to take on the column-default. Now, if the first column in your database table is an IDENTITY column that can really trip you up when using this method due to the ordinal mapping. If you control the staging table then no worries, but if not, know that you can explicitly map the columns in your DataTable to columns in your database table using SqlBulkCopy, similar to how you can do the same with a bcp format file, but it requires manual configuration of the object so it's easiest to try and avoid that scenario.



So, here is a script for you, truncated with pointers to a couple web pages with functions on them for you to copy and paste into your script:

##################################################################################
# functions

#Copy and paste the Out-DataTable definition from http://poshcode.org/2119

#Copy and paste the Write-DataTable definition from http://poshcode.org/2276

##################################################################################
# script

# get your WMI data
$pso = Get-WmiObject Win32_LogicalDisk -computer 'Computer1','Computer2' | Select SystemName,DeviceID,VolumeName,Size,FreeSpace

# convert the PowerShell object to a .NET DataTable
$dt = ($pso | Out-DataTable)

# write the DataTable to a SQL Server table
Write-DataTable -ServerInstance Server\Instance -Database DBA -TableName dbo.DiskInfo -Data $dt


Edit...

Forgot to add the table definition:

CREATE TABLE dbo.DiskInfo
(
SystemName SYSNAME NULL,
DeviceID SYSNAME NULL,
VolumeName SYSNAME NULL,
Size BIGINT,
FreeSpace BIGINT,
ExtraColumn1 INT,
ExtraColumn2 INT
);



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216968 Visits: 41991
jonbes (6/22/2013)
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.


Very cool. I really appreciate the time you spent putting that together. This is a really big help especially with the embedded comments. Thank you.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216968 Visits: 41991
@Orlando,

Man, thanks for that... especially on the hint that you can use ordinal positioning for this. A properly formed staging table or even the old trick of using an insertable view instead of a "format file" or special coding would absolutely do the trick.

Thanks for the great post, ol' friend.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40122 Visits: 14413
Happy to. Forgot about the view fronting the table for bulk loads. That has saved my bacon more than once.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216968 Visits: 41991
I have to say it again... "Thank you" to all that have helped on this thread. It's been truly educational and provides a great springboard to get me started. Not only will it help me, but it's a wonderful thread for others that have the same question.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
nicholas_conlan
nicholas_conlan
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 11
Hi,

Not knowing Powershell very well and indeed sql it's taken me some time to tinker with and adapt your script . I need help expanding on it. It works for retrieving more than 1 value from the same class and inserts the values. What I need to do is to be able to retrieve values from other classes and insert the result in to the appropriate column. Any help / advice would be so appreciated.

$servernames = Get-WmiObject -computername Anycompname -class win32_ComputerSystem | Select Name, Manufacturer

# Open Connection
$conn = New-Object System.Data.SqlClient.SqlConnection
$connectionString = "Server=;Database=;user=;pwd=;"
$conn.ConnectionString = $connectionString
$conn.Open()

# Create the Command object to execute the queries
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]::Text

# Write Data
foreach ($servername in $servernames)
{
# Compose Query for this $servername - watch the single quotes for string values!!
$query = "INSERT INTO dbo.U_Server (ServerName, OSVersion) VALUES ('" + $servername.Name + "', '" + $servername.Manufacturer + "')"

# Uncomment next line to display query for checking
$query

# Setup Command
$cmd.CommandText = $query

# Execute Command to insert data for this $drive
$result = $cmd.ExecuteNonQuery()
}

# Tidy Up
$conn.Close()
ajiteshmalhotra
ajiteshmalhotra
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 133
Thanks for such a wonderful script.

I am new in PowerShell.so it will take time to understand. Could you please tell me
Do you have any script for window service and Sql job.
ajiteshmalhotra
ajiteshmalhotra
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 133
Hi,

I have created the power shell script for database information.Please find the below query:-

ForEach ($instance in Get-Content "C:\temp\test\sqlserverlist.txt")
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$dbs=$s.Databases
$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
ForEach ($dbs in $dbss)
{

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=AITSSQL22; Initial Catalog=Workdb; Integrated Security=SSPI")

$conn.Open()

$cmd = $conn.CreateCommand()

$cmd.CommandText = $insert_stmt

$name=$dbs.name

$Collation=$dbs.Collation

$CompatibilityLevel=$dbs.CompatibilityLevel

$AutoShrink=$dbs.AutoShrink

$RecoveryModel=$dbs.RecoveryModel

$Size=$dbs.Size

$SpaceAvailable=$dbs.SpaceAvailable

$insert_stmt = "INSERT INTO dbo.temp_ajmalh2(Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable)

VALUES('$Name', '$Collation', '$CompatibilityLevel', '$AutoShrink', '$RecoveryModel', '$Size', '$SpaceAvailable')"

$cmd.ExecuteNonQuery()

$conn.Close()
}
}

But data is not going on database table. Could you please help me out for this.
I am new in powershell.

Thanks
Ajitesh Malhotra
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search