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 (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)

Group: General Forum Members
Points: 512897 Visits: 44330
Thank you Gary and calvo... this is a great start for me. It's been a thousand years since I've gone anywhere near procedural code outside of SQL Server. Compound that with being a real newbie to PowerShell and hopefully you'll understand why wasn't even sure what I was looking for in Google. Now that I have a couple of examples that I actually understand, I might be able to make some serious headway until I get a book on the subject.

Thanks again to both of you for help me out.

--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
schleep
schleep
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6717 Visits: 1491
Jeff,

A while back I experimented with TVPs and Powershell.

Basically, you create a table on a SQL server, and create a stored proc with a TVP to handle the inserts.

In Powershell, you create a datatable object and populate it. Then create a sqlcommand object of type stored proc, add a parm that will be a TVP, then set the value of the parm = datatable, and call the sproc.

It's not exactly elegant, and you still have to populate the PoSh datatable RBAR.
But you can pass many rows to SQL Server in a single call.

I adapted the following from code found on the net. I wish I could remember where for credit.


#Datatable object
$Datatable = New-Object system.Data.DataTable
$null = $Datatable.Columns.Add("Server", "System.String")
$null = $Datatable.Columns.Add("DBName", "System.String")
$null = $Datatable.Columns.Add("TableName", "System.String")
etc ....


$Data = <some result set you're going to insert into your $Datatable>
if ($Data)
{
foreach ($Row in $Data)
{
$Info = $Row.ItemArray
if ($Info)
{
$null = $Datatable.Rows.Add($Info)
}
}
}

#Connection and Query Info
$serverName = "<MyServer>"
$databaseName = "<MyDB>"
$query = "<MyInsertSproc>"

#Connect
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$conn = new-object System.Data.SqlClient.SqlConnection $connString
$conn.Open()

#Create Sqlcommand type and params
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmd.CommandText = $query
$null = $cmd.Parameters.Add("@TVP", [System.Data.SqlDbType]::Structured)
$cmd.Parameters["@TVP"].Value = $Datatable

#Create and fill dataset
$ds = New-Object system.Data.DataSet
$da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
$null = $da.fill($ds)
$conn.Close()



jonbes
jonbes
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 873
Jeff

Below is full routine for your specific needs. RBAR I'm afraid, but unless you have an impressive number of drives I don't see that as an issue here.

With thanks for all of your articles that have helped and instructed me.


###############################################################################

$drives = Get-WmiObject Win32_LogicalDisk -computer 'SomeComputerName' | Select SystemName DeviceID, VolumeName, Size, FreeSpace

# 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 queries
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]::Text

# Write Data
foreach ($drive in $drives)
{
# Test for Null Values - otherwise Dynamic SQL goes wrong
if ($drive.Size -eq $null) { $drive.Size = 0 }
if ($drive.FreeSpace -eq $null) { $drive.FreeSpace = 0 }

# Compose Query for this $drive - watch the single quotes for string values!!
$query = "INSERT INTO dbo.DiskSpace (SystemName, DeviceID, VolumeName, Size, FreeSpace)
VALUES ('" + $drive.SystemName + "', '" + $drive.DeviceID + "', '" + $drive.VolumeName + "', " + $drive.Size + ", " + $drive.FreeSpace + ")"

# 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()

###############################################################################

And if you want to read the data back:

############################################################################################

# Check Data Stored via Reader

# 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 queries
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]::Text

# Create Query
$query = "SELECT SystemName, DeviceID, VolumeName, Size, FreeSpace FROM dbo.DiskSpace"

# Get records
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()

$values = @("","","",0,0) # empty array object

# Read Rows
while ($reader.read())
{
$reader.GetValues($values) | Out-Null # suppress the field count
$values -join "`t" # join the row values with Tabs and display
}
$reader.Close()

# Tidy Up
$conn.Close()
############################################################################################
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)

Group: General Forum Members
Points: 512897 Visits: 44330
This is absolutely incredible.

Gary, thanks for kicking off the thread with the bone of the code and some great examples to get me thinking in loops again. It's been a very long time.

Calvo, Schleep, and Jonbes… thank all 3 of you for the wonderful examples and the embedded documentation. Using your examples and explanations, I'll be able to quickly get my arms around PowerShell and its nuances.

There's just nothing like some practical examples and a bit of documentation. I really appreciate what all 4 of you have done for me. Thank all of you very much.

--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
Gary Varga
Gary Varga
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46484 Visits: 6562
Hi Jeff,

You are most welcome. I am sure I speak for all of us when I say it was a privilege to help you especially considering the huge input you provide to this community.

I guess it once more shows that all of us have something to offer :-)

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
schleep
schleep
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6717 Visits: 1491
@Gary: +1



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)

Group: General Forum Members
Points: 512897 Visits: 44330
Gary Varga (6/19/2013)
I guess it once more shows that all of us have something to offer :-)


Spot on, Gary.

--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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)SSC Guru (129K reputation)

Group: General Forum Members
Points: 129580 Visits: 22604
Hi Jeff

There's a free IDE for PowerShell here, in case you are coding using some bytepad editor :-)


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
MG-148046
MG-148046
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6338 Visits: 2957
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)

Group: General Forum Members
Points: 512897 Visits: 44330
Phil Parkin (6/20/2013)
Hi Jeff

There'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

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
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