Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Write PowerShell Output to SQL Server Table Expand / Collapse
Author
Message
Posted Friday, June 21, 2013 3:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1466411
Posted Saturday, June 22, 2013 9:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:09 AM
Points: 4, Visits: 787
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()

###############################################################################
Post #1466469
Posted Wednesday, June 26, 2013 4:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1467902
Posted Wednesday, June 26, 2013 7:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1467918
Posted Wednesday, June 26, 2013 7:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
@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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1467919
Posted Wednesday, June 26, 2013 7:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1467920
Posted Wednesday, June 26, 2013 7:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1467921
Posted Friday, November 22, 2013 6:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 16, 2013 4:26 AM
Points: 3, 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()
Post #1516754
Posted Thursday, July 31, 2014 11:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:34 AM
Points: 3, Visits: 101
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.
Post #1598573
Posted Monday, August 11, 2014 4:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:34 AM
Points: 3, Visits: 101
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
Post #1601767
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse