Hello:

Now that I have SQL Express, I am going back over the tutorials
that I did before.

I had trouble with sqlcmd. If I give the plain command
sqlcmd
I get
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A
network-related or in
stance-specific error has occurred while establishing a connection to
SQL Server
.. Server is not found or not accessible. Check if instance name is
correct and i
f SQL Server is configured to allow remote connections. For more
information see
SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout
expired.

If I specify my server, it works. Shouldn't the plain command
work since it is supposed to start the default instance of SQL Express
on my computer?

I accepted the default instance name when installing SQL Express.

SSMS works: it starts up and I can log in.

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
No.

Admittedly, this is a little confusing. A default instance is an instance
that does not have a name. It is accessed as SERVER only. A named instance
is accessed as SERVER\NAME.

If you install any other edition than Express, then by default you get a
default instance.

But when you install Express Edition, by default you get a named instance
with the name SQLEXPRESS. Why, you may wonder? Because you may install
some other edition later. And more importantly, you may bundle Express
with an application you sell, and in that case you should hijack the
default instance, in case the customer wants to install a bigger instance of
SQL Srever later.


In SSMS Server\instance is filled for you.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Mon, 25 Oct 2010 23:33:40 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

ITYM "unnecessarily".

I think one could be forgiven for thinking that Microsoft does
not want its product used.

[snip]

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
No, the current behaviour is correct in my opinion. SQL Express should
not be installed as a default instance, at least no by default, and SQLCMD
should not try to guess what you want. What if you later install a
default instance, and SQLCMD without any arguments suddenly started to
connect to a difference instance?

If ./SQLEXPRESS to much to type for you, I think SQLCMD can get the default
server from a environment manual. Books Online should have the details.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Tue, 26 Oct 2010 23:44:31 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

I do not. "default" means the one selected when nothing is
specified. I think that most people would think that when there is
only one, that it should be the default.

Microsoft has chosen to call a nameless instance a default
instance. Was there really any need for this obfuscation?

People are familiar with defaults changing.

ITYM "variable" here.

No such option, but one could do
set inst=Loop\SQLExpress
sqlcmd -S %inst%
or
set inst=-S Loop\SQLExpress
sqlcmd -%inst%

I just created a batch file consisting of this line:
sqlcmd -S Loop\SQLExpress %*

It is these little gotchas and the sheer number of them that
makes SQL Server difficult to learn.

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Everything has a history. In the beginning there was no named instances, but
you only could only have one instance of SQL Server on a machine. That was
in SQL 2000. SQL Express was introduced in SQL 2005.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Tue, 26 Oct 2010 15:13:57 -0700, Gene Wirchenko wrote:

Don't forget that SQL Server is a server product by nature. The client
tools don't necessarily expect the server to be on the same computer as
where they are running. In such a setup, it's hard to come up with any
sensible default, so I think not having a default to connect to is the
right choice.

Probably the fact that if you change no options during installation of
SQL Server, you get a nameless instance. It's not the default to connect
to, but the default to install.

Wrong. Excerpt from Books Online:
"If you do not specify a server_name [ \instance_name ] when you start
sqlcmd, SQL Server checks for and uses the SQLCMDSERVER environment
variable."

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Wed, 27 Oct 2010 12:27:04 +0200, Hugo Kornelis
<hugo (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:

Funny that the Microsoft tutorial does not agree.

Well, not so funny. I have not found it very useful. I have
found out about a book that looks hopeful, previewed a chapter, and
have just ordered it.

Nameless instance is not the default.

Not wrong. I looked at the command options, and there was no
such option.

Your detail is YA detail that is not obvious to someone starting
and is just another gotcha.

Sincerely,

Gene Wirchenko

On Wed, 27 Oct 2010 12:53:46 -0700, Gene Wirchenko wrote:

It's not the default for Express. But it has always been the default for
other SQL Server editions. As Erland already mentioned, Express was
introduced a lot later than the other SQL Server editions. The name
"default instance" was already in use when Express was released.

Now I don't understand. I thought this was about environment variables,
but now you say that this is not a command option. Just for the record,
when running sqlcmd.exe ...

* You can use the -S command line option to specify server name or
server + instance name to connect to. This is documented in Books
Online. When you run sqlcmd with the -? option, -S in included in the
overview, but [\instance] is missing (tested on SQL Server 2005; I have
no idea if this is fixed in later edition, but I'll run a test later)

* Or you can set the SQLCMDSERVER environment variable before running
sqlcmd.exe. This environment variable will only be used if the -S option
is not specified. This is documented in Books Online.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Thu, 28 Oct 2010 15:41:59 +0200, Hugo Kornelis
<hugo (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:

[snip]

-S is a command option.

This is not a command option. It is not documented in the
command help. I had no reason to suppose it existed, so why would I
go looking? Just think of how nice it would have been if this was
part of the sqlcmd /? output:

"If the -S parameter is not specified, sqlcmd will use the value
of the SQLCMDSERVER environment variable (if defined) for the server
string."

The secret to eternal life could be hidden in the Books Online
for all I know. "hidden" is the operative word.

Sincerely,

Gene Wirchenko