sqlcmd

On Mon, 25 Oct 2010 23:33:40 +0200, Erland Sommarskog
wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>> 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?
>
>No.
>
>Admittedly, this is a little confusing. A default instance is an instance
^^^^^^^^
ITYM "unnecessarily".

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

[snip]

Sincerely,

Gene Wirchenko
 >> Stay informed about: sqlcmd 

Gene Wirchenko (genew@ocis.net) writes:
>>Admittedly, this is a little confusing. A default instance is an instance
> ^^^^^^^^
> ITYM "unnecessarily".
>
> I think one could be forgiven for thinking that Microsoft does
> not want its product used.

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 DeleteThis @sommarskog.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
 >> Stay informed about: sqlcmd 

On Tue, 26 Oct 2010 23:44:31 +0200, Erland Sommarskog
wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>>Admittedly, this is a little confusing. A default instance is an instance
>> ^^^^^^^^
>> ITYM "unnecessarily".
>>
>> I think one could be forgiven for thinking that Microsoft does
>> not want its product used.
>
>No, the current behaviour is correct in my opinion. SQL Express should

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?

>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?

People are familiar with defaults changing.

>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.
^^^^^^
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
 >> Stay informed about: sqlcmd 

Gene Wirchenko (genew@ocis.net) writes:
> Microsoft has chosen to call a nameless instance a default
> instance. Was there really any need for this obfuscation?

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.TakeThisOut@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: sqlcmd 

On Tue, 26 Oct 2010 15:13:57 -0700, Gene Wirchenko 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.

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.

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

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.

>>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.
> ^^^^^^
> ITYM "variable" here.
>
> No such option,

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
 >> Stay informed about: sqlcmd 

On Wed, 27 Oct 2010 12:27:04 +0200, Hugo Kornelis
wrote:

>On Tue, 26 Oct 2010 15:13:57 -0700, Gene Wirchenko 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.
>
>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.

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.

>> Microsoft has chosen to call a nameless instance a default
>>instance. Was there really any need for this obfuscation?
>
>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.

Nameless instance is not the default.

>>>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.
>> ^^^^^^
>> ITYM "variable" here.
>>
>> No such option,
>
>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."

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
 >> Stay informed about: sqlcmd 

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

>>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.
>
> Nameless instance is not the default.

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.

>>>>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.
>>> ^^^^^^
>>> ITYM "variable" here.
>>>
>>> No such option,
>>
>>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."
>
> 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.

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
 >> Stay informed about: sqlcmd 

On Thu, 28 Oct 2010 15:41:59 +0200, Hugo Kornelis
wrote:

[snip]

>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)

-S is a command option.

>* 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.

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
 >> Stay informed about: sqlcmd 

Gene Wirchenko (genew@ocis.net) writes:
> The secret to eternal life could be hidden in the Books Online
> for all I know. "hidden" is the operative word.

Yes, if you refuse to read the documentation when pointed to it, there is a
lot that will be hidden to you.

True, the topic for SQLCMD is long, and I did not tell you when on the
page it is. Guess why? I don't know the topic by heart, and since *you*
are the one who need the information and not me, I didn't see why I should
spend the time to read the topic for you. (But since I know that
predecessors to SQLCMD have supported such environment variables, I had
reason to expect that there is one.)

--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.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
 >> Stay informed about: sqlcmd 

On Thu, 28 Oct 2010 23:33:12 +0200, Erland Sommarskog
wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>> The secret to eternal life could be hidden in the Books Online
>> for all I know. "hidden" is the operative word.
>
>Yes, if you refuse to read the documentation when pointed to it, there is a
>lot that will be hidden to you.

HA!

When I got Adobe Acrobat, there was approximately 7,000 pages of
documentation. I needed about 1/2 page of it for my integration.

Are you seriously suggesting that someone read every page before
getting going?

There is something to be said for organisation and
user-friendliness.

>True, the topic for SQLCMD is long, and I did not tell you when on the
>page it is. Guess why? I don't know the topic by heart, and since *you*

I do not even know where to find it. It is generally faster for
me to Google for something once I know about it than to try to find
something in the documentation.

CLUE: That might indicate to you that the documentation is not
beginner-friendly.

>are the one who need the information and not me, I didn't see why I should
>spend the time to read the topic for you. (But since I know that
>predecessors to SQLCMD have supported such environment variables, I had
>reason to expect that there is one.)

I had not previously used it, so I had no reason etc.

Note that I am speaking from the point of view of someone trying
to get started with this stuff. Bad documentation makes this much
more difficult than it needs to be.

Sincerely,

Gene Wirchenko
 >> Stay informed about: sqlcmd 

Gene Wirchenko (genew@ocis.net) writes:
> Are you seriously suggesting that someone read every page before
> getting going?

No, only the topic for SQLCMD.

> I do not even know where to find it. It is generally faster for
> me to Google for something once I know about it than to try to find
> something in the documentation.

And you do don't want to find it? You don't want to read documentation?
I'm sorry, but I have no sympathy for that attitude.

Books Online has an index. It has a search function. It has a table of
contents. With the slightest level of ambition you could easily find the
topic for SQLCMD.


--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.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
 >> Stay informed about: sqlcmd 

On Fri, 29 Oct 2010 09:19:02 +0200, Erland Sommarskog
wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>> Are you seriously suggesting that someone read every page before
>> getting going?
>
>No, only the topic for SQLCMD.
>
>> I do not even know where to find it. It is generally faster for
>> me to Google for something once I know about it than to try to find
>> something in the documentation.
>
>And you do don't want to find it? You don't want to read documentation?
>I'm sorry, but I have no sympathy for that attitude.

I have little tolerance for people who misstate other people
positions. There is a lot of documentation. I do not want to have to
read it all to get started. I want the relevant portion to get
oriented.

>Books Online has an index. It has a search function. It has a table of
>contents. With the slightest level of ambition you could easily find the
>topic for SQLCMD.

Did you cover the bit where I had no known reason for looking it
up? The tutorial implied that the bare command would just work.

Sincerely,

Gene Wirchenko
 >> Stay informed about: sqlcmd 

Gene Wirchenko (genew@ocis.net) writes:
> I have little tolerance for people who misstate other people
> positions. There is a lot of documentation. I do not want to have to
> read it all to get started. I want the relevant portion to get
> oriented.

And I pointed you to do it. What more do want me to do? Fly over, and click
so you get the page in front of you? What is so difficult to use an index?
Really, Books Online is your biggest asset to get working with SQL Server,
so there is all reason to get acquainted with it. (And it's not that Books
Online is very SQL Server-specific, but it uses the same platform and
structure as MSDN Library. Well, at least up to VS 2008. What ships with
VS 2010 is a disaster.)

> Did you cover the bit where I had no known reason for looking it
> up? The tutorial implied that the bare command would just work.

Are you referring to this?
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_5techref/html/00d57437-7a29-4da1-b639-ee990db055fb.htm

The first thing I see is the second note: "By default, SQL Server Express installs as the named instance *sqlexpress*."

So already there is a warning that you are not connecting to a default
instance.

Yes, that is a problem. Sometimes you need to read documentation more than
once to get the gist. That happens to me too.


--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.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
 >> Stay informed about: sqlcmd