Invoke-SqlCmd Just Got Better!

If you’ve read even just a few of my articles on Simple-Talk you know that I’m a big fan of PowerShell + SQL Server.  Over the years I have written a lot about how to collect data from your SQL Servers and store it somewhere, maybe in a database, maybe in a word document.

Well today collecting data from your SQL Servers just got a little bit easier.


Invoke-Sqlcmd now supports a -OutputAs parameter and it allow you to specify either “DataRows” (default), “DataTables”, and “DataSet”.  We have had this functionality for years with Invoke-SqlCmd2 and it’s now available natively from SQL Server.  When you run a query and output it into a DataTable you will still need some other mechanism to be able to store the data.  At this point you have lots of options, you could store the data in Excel, HTML, or put it into a table in another SQL Server database.


For right now you will still need to use a script like Write-DataTable if you want to put your data back into a table in SQL Server.   While this means you might still need an additional script to accomplish a common task, the great news is that Matteo from the SQL Tools team has already commented on this Trello card saying that looking into adding that functionality.  I encourage you to go Up-Vote & comment on that item on Trello or on Connect.

Join the Conversations!

As I mentioned above, folks in the PowerShell + SQL Server community are collaborating on the Trello board about new cmdlets they want; and everyone is welcome to comment & Up-Vote.  If you want to be able to add a card, just ask and you will be added!  In addition, a Slack channel has been setup for everyone in SQL Community to join.  You can go to this link here and auto-invite yourself.  Everyone is welcome to join; I can’t wait to talk to you on the boards!

But wait, there’s more.

  • To read the full announcement from Microsoft check out this post on the Data Platform Insider blog.
  • To read more about the new SQL Agent cmdlets read this post from Chrissy LeMaire.
  • To read more about the new Get-SqlErrorLog cmdlet read this post from Aaron Nelson.

Microsoft also said “Additionally, we added the ConnectionString parameter which allows the script author complete control over the connection context. This unlocks new capabilities such as connecting to SQL Azure using Azure Active Directory authentication.

Big thanks to Aaron, Chrissy and Rob . This blog post it is just a channel to spread the words, the post itself is from all of us. The PASS PowerShell VC Team.