2

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.

3

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]
  • Actually, yes - SQLCMD mode in the Studio does work! (what is even more interesting is that it changes the connection to the specified server, correctly runs the rest of the script against that server but the connection of the query tab remains same as before running the script). However, that same script which runs in SQLCMD mode, does not run well in SQLCMD itself! I do not want to use -S parameter because I want to pack as much as possible inside the script itself (otherwise there wouldn't be any question) – Yuri Makassiouk May 3 at 12:25
  • I just tested :CONNECT tcp:[servername] via sqlcmd.exe and it works fine. I'm not clear where the problem is. – Max Vernon May 3 at 13:35
  • When you start sqlcmd.exe it will always connect to a server. If you don't specify the -S parameter, it'll attempt to connect to tcp:localhost:,1433 by defauilt. – Max Vernon May 3 at 13:36
  • Do you mean that sqlcmd would need to first make a connection to what the -S parameter specifies (or default) before it processes any :connect directives inside the script? – Yuri Makassiouk May 4 at 14:33
  • Yes, that's precisely what I'm saying. – Max Vernon May 4 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.