Add a parameter to Invoke-SqlCmd to accept a connection string - by Mike Fal

Status : 


Sign in
to vote
ID 2551673 Comments
Status Active Workarounds
Type Suggestion Repros 1
Opened 4/4/2016 1:23:29 PM
Access Restriction Public


Invoke-SqlCmd is currently limited in its use by the fact that you can only define how it connects to SQL Servers by using the pre-definied parameters and switches for the cmdlet. This was modeled after SQLCMD and not all the switches were supported. This means some behavior isn't supported. The most specific example of this is connecting to Availability Groups that have multiple subnets, because you can't specify MultiSubnetFailover=true (-M for SQLCMD).
Sign in to post a comment.
Posted by Microsoft on 10/26/2016 at 3:24 PM
This was fixed a while back. If you install the SqlServer PowerShell module which comes with the latest SSMS version, you'll see the fix.

Posted by Microsoft on 6/17/2016 at 11:41 AM
Hi Mike,
Thanks for filing this connect bug. It is indeed a good suggestion. I've added a new -ConnectionString parameter that allows you to pass your own custom connection string. Hopefully, this will be available in a near future (I'm goingto update this connect bug when it happens).

Here's an example of what you can will be able to do:

PS C:\> Invoke-Sqlcmd -Query "SELECT COUNT(*) AS Count FROM MyTable" -ConnectionString "Data Source=MYSERVER;Initial Catalog=MyDatabase;Integrated Security=True;ApplicationIntent=ReadOnly"