Problems using :Connect in SQLCMD

Question

Problems using :Connect in SQLCMD

robinsonir

Old Hand

Points: 346
More actions
July 13, 2010 at 10:23 am

#142032

I am using :connect with a scripting variable to identify the SQL instance. So in the SQL script the code looks like
:Connect $(SQLInstanceName)
For the most part this works just fine but every now and again I run into an instance where this will not work and I get the following error
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
It seems that it is something "environmental" in these cases where it does not work but I'm having no luck nailing it down. I've checked all the usual suspects (TCP/IP and Named Pipes protocols enabled, matching port numbers between client and server, SQL Server Browser service running etc.). My initial thought was that the environment variable SQLInstanceName was not being set but I did a test today when I ran into the issue again using the instance name explicitly and still it would not connect. The workaround I'm using at the moment is to comment out the :Connect in the SQL code and use /S%SQLInstanceName% on the SQLCMD command line and the connection is established OK.
Is anyone aware of any known problems with SQLCMD and :Connect? Any ideas are appreciated.
Thanks
Ian

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 1

Go to "Services" in Control Panel and make sure that the DTC service is running. Not only should it be running but it should probably also be set to start automatically.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

robinsonir Old Hand Points: 346 More actions · Answer 2

Jeff,

DTC is running on both the database server and the machine where SQLCMD is being executed so it doesn't look like that is the problem.

Thanks

Ian

seth delconte SSCertifiable Points: 6388 More actions · Answer 3

Is it possible you are running out of TCP ports? I've seen instances where adjusting the TCPTimeWaitDelay and MaxUserPort registry settings helped to extend the available TCP ports.

_________________________________
seth delconte
http://sqlkeys.com

robinsonir Old Hand Points: 346 More actions · Answer 4

Seth,

I doubt that is the case. When this occurs the SQLCMD with :Connect will fail every time while SQLCMD with a -S switch will work every time. If available TCP ports were the cause I would expect to see unpredictable results in terms of success or failure for either case.

Thanks

Ian