Connect to remote named instance

May 27, 2011 at 11:32 AM

Need to connect to a remote instance, via TCP. This is a named instance in a cluster. I've tried every combination that I can imagine with no success:

-serverinstance servername\instancename - (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

-serverinstance servername\instancename,portnumber - Invoke-Sqlcmd : Value cannot be null.

-serverinstance IPadress:portnumber - (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

-serverinstance tcp:IPadress:portnumber - (provider: TCP Provider, error: 0 - No such host is known.)

...

Can you help?

Coordinator
May 28, 2011 at 6:09 PM

Invoke-sqlcmd is cmdlet which ships with the Microsoft SQL Server PowerShell host, sqlps. This is CodePlex project is a community-driven project called sqlpsx. I would suggest posting your question in the Microsoft SMO forum:

http://social.technet.microsoft.com/Forums/en-US/sqlsmoanddmo/threads

That said it doesn't appear invoke-sqlcmd supports specifying a port number--only server instance. You could create your own version as I have done here which does support specifying:

"servername,portnumber"

http://poshcode.org/2279

Invoke-Sqlcmd2 -ServerInstance "Win7boot,50498" -Database master -Query "select @@servername"

 

Coordinator
Jun 13, 2011 at 2:13 PM

I was using invoke-sqlcmd today and figured out how to make it work. Just like SSMS you specify tcp:servername,portnumber:

invoke-sqlcmd -ServerInstance tcp:myserver,5100 -Database master -Query "select @@servername"