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 ««123»»

Write PowerShell Output to SQL Server Table Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 11:10 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1464285
Posted Tuesday, June 18, 2013 8:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:36 AM
Points: 402, Visits: 859
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()




Post #1464690
Posted Tuesday, June 18, 2013 12:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 9:57 AM
Points: 4, Visits: 749
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()
############################################################################################

Post #1464828
Posted Tuesday, June 18, 2013 9:16 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1464940
Posted Wednesday, June 19, 2013 3:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:20 AM
Points: 4,862, Visits: 2,243
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!!!
Post #1465011
Posted Wednesday, June 19, 2013 5:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:36 AM
Points: 402, Visits: 859
@Gary: +1


Post #1465080
Posted Wednesday, June 19, 2013 7:21 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1465154
Posted Thursday, June 20, 2013 5:25 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:02 AM
Points: 4,828, Visits: 11,182
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1465597
Posted Friday, June 21, 2013 10:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:27 AM
Points: 1,757, Visits: 2,118
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.

Post #1466283
Posted Friday, June 21, 2013 3:53 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1466408
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse