Invoke-SqlCmd4 - A Superior SQL Commandlet

Recently, I wrote about one of the issues with the Invoke-SqlCmd commandlet where it sets the ApplicationName when you use the parameter that should set the host.

Fellow MVP Ben Miller sent me a copy of the Invoke-SqlCmd3 that they were using. It was much better.

I then realized that there were many other options missing from these commandlets (such as options for Database Mirroring and Availablity Groups) and so I set about improving it.

Ben has posted it to the PowerShell Code Repository. You’ll find Invoke-SqlCmd4 here:

https://sqldownunder.blob.core.windows.net/public/Invoke-Sqlcmd4.txt

This version of the commandlet will allow setting many other parameters (and it sets the ApplicationName and HostName correctly). The parameters are:

  1. [Parameter(Position = 0, Mandatory=$true)] [string]$ServerInstance,
  2. [Parameter(Position = 1, Mandatory = $false)] [string]$DatabaseName,
  3. [Parameter(Position = 2, Mandatory = $false)] [string]$Query,
  4. [Parameter(Position = 3, Mandatory = $false)] [string]$UserName,
  5. [Parameter(Position = 4, Mandatory = $false)] [string]$Password,
  6. [Parameter(Position = 5, Mandatory = $false)] [Int32]$QueryTimeout = 600,
  7. [Parameter(Position = 6, Mandatory = $false)] [Int32]$ConnectionTimeout = 30,
  8. [Parameter(Position = 7, Mandatory = $false)] [string]$ApplicationName = “PowerShell SQLCMD”,
  9. [Parameter(Position = 8, Mandatory = $false)] [string]$HostName,
  10. [Parameter(Position = 9, Mandatory = $false)] [ValidateSet(“ReadOnly”, “ReadWrite”)] [string] $ApplicationIntent,
  11. [Parameter(Position = 10, Mandatory = $false)] [ValidateScript({test-path $_})] [string]$InputFile,
  12. [Parameter(Position = 11, Mandatory = $false)] [ValidateSet(“DataSet”, “DataTable”, “DataRow”)] [string]$OutputAs = “DataRow”,
  13. [Parameter(Position = 12, Mandatory = $false)] [string]$FailoverPartnerServerInstance,
  14. [Parameter(Position = 13, Mandatory = $false)] [bool]$IsMultiSubnetFailover = $false

Hope that helps someone.

2015-04-03