With the release of Microsoft SQL 2016 looming, I've been keeping an eye on the SQL PowerShell cmdlets. In particular, Invoke-Sqlcmd.
I was disappointed however, to find the cmdlets are still only included when installed with the SQL Management Objects. It seems that this cmdlet has been designed to fill the gap between batch files + sqlcmd.exe and more modern PowerShell.
One of my rules when writing PowerShell is that the script should run on any machine without requiring special things to be installed. Based on this rule, I have written a small function. The only dependency is the .NET framework, as long as you're running on Windows 7/Server 2008+, you should be good to go.
Invoke-SqlCommand
Usage
Using the cmdlet is quite simple and can be used with either Windows or SQL auth:
Input
$server = "<server>.database.windows.net"
$db = "<database>"
$sql = "SELECT TOP 5 * FROM [Index]"
Invoke-SqlCommand -Server $server -Database $db -Username $user -Password $pass -Query $sql | Format-Table
Output
I've been doing some work in the BI space lately. I've found it interesting to use unconventional data sources (like logs or output from other cmdlets etc). Then using PowerShell to clean/normalise the data and finally, plugging a reporting tool in over the top.
PowerShell is a great language for bridging operational scripts with a database for reporting (especially when your report is at the PoC stage). This code makes it super easy to plug your PowerShell script into an Azure SQL DB too.
Let me know what interesting uses you find for this cmdlet!
UPDATE 05/05/2016
Thanks to danekan on Reddit, I have updated the gist above to create an alias for the -ServerInstance param. This makes my code somewhat interchangeable with the Invoke-SqlCmd cmdlet without major changes.
Adding the following line to your existing scripts that use Invoke-SqlCmd to complete the transformation: New-Alias Invoke-SqlCmd Invoke-SqlCommand -Force. Just a quick disclaimer however: I have not intentionally made this to be at full feature parity with Invoke-SqlCmd, therefore your mile may vary :)
Jourdan