Sql-server – SQLCMD :connect directive – how can I specify connection protocol

sql serversqlcmd

In a script I have the directive

:connect DbServer

and it seems that if I run this script in SQLCMD (like this:

sqlcmd -i script.sql

then sqlcmd attempts to connect using Named Pipes (which doesn't work). I get error messages saying that Named Pipes provider could not connect.
However, if I try to connect to the same server by specifying its name in the -S parameter, it works fine.

Is there a way to let the :connect directive know that I am trying to connect by TCP/IP?

I tried using :connect tcp:DbServer to no avail. Also tried to go by IP address, same results. On this machine I don't have the SQL Server Configuration Manager to specify connectino aliases or manipulate protocols. Maybe that's the next thing to do, but I'd like to know if somebody knows a way to contain the connection specification inside the script.

Best Answer

sqlcmd.exe -S tcp:myserver or sqlcmd.exe -S tcp:myserver\instance should do the trick. If you must specify the port, do so like this: sqlcmd.exe -S tcp:myserver,1433

By the way, documentation for SQLCMD is available directly from Microsoft, where it states the -S parameter syntax as:

-S [protocol:]server[instance_name][,port]

SQLCMD Mode in SQL Server Management Studio accepts precisely the same syntax.

So:

:CONNECT [protocol:]servername[instance_name][,port]
Related Question