Saturday, December 12, 2009

SQLCMD Error: Login timeout expired

So I was automating the creation of a database and I was getting the following error:

C:\sqlcmd -s "(local)SQLEXPRESS" -d TestDb -i MySql.sql

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.

This was really confusing because all of the blogs that talked about this error were telling me to do things that I had already done.  I guess it is common to get this error, but the usual culprits were not at play with me.  I was stumped  I could not figure out why I had configured my DB Instance to accept remote connections and it was functioning properly, but I was still getting this error.

Well I finally found a post on this page that had the answer.  Let me say that it is all my fault, but I contend that if the SQLCMD program had better error messages, I would have figured out the problem quite quickly.  As it turns out, the -s parameter is to set the colseperator.  What I should have been using the -S parameter which is to set the server. 

C:\sqlcmd -S "(local)SQLEXPRESS" -d TestDb -i MySql.sql