June 19, 2013 at 7:21 am
Gary Varga (6/19/2013)
I guess it once more shows that all of us have something to offer 🙂
Spot on, Gary.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2013 at 10:07 am
Schleep - thanks for your example. I have a few places where it can be utilized but I have a question about it. While I am not very good with .Net, I think the cmd needs to be executed (executenonquery?) and I don't see that in the code before closing the connection. Am I missing something or just don't know enough?
Jeff - I'd just like to add my thanks for your participation on the forums. I don't get on often but I always find your posts excellent!
Oh - and welcome to the world of Powershell. I think you'll really like it!
MG
"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.
June 21, 2013 at 3:53 pm
Phil Parkin (6/20/2013)
Hi JeffThere's a free IDE for PowerShell here, in case you are coding using some bytepad editor 🙂
Thanks, Phil. Much appreciated. I'll give it a whirl.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2013 at 3:58 pm
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
Change is inevitable... Change for the better is not.
June 22, 2013 at 9:41 am
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()
###############################################################################
June 26, 2013 at 4:40 pm
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.
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.
##################################################################################
# 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
June 26, 2013 at 7:25 pm
jonbes (6/22/2013)
JeffBelow 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
Change is inevitable... Change for the better is not.
June 26, 2013 at 7:29 pm
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
Change is inevitable... Change for the better is not.
June 26, 2013 at 7:32 pm
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
June 26, 2013 at 7:36 pm
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
Change is inevitable... Change for the better is not.
November 22, 2013 at 6:26 am
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()
July 31, 2014 at 11:20 pm
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.
August 11, 2014 at 4:44 am
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
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply