Click here to monitor SSC
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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44968 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
schleep
schleep
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 1286
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
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 855
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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44968 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8252 Visits: 6124
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
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 1286
@Gary: +1



Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44968 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8308 Visits: 19449
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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2158 Visits: 2689
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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44968 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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