views:

23

answers:

1

I am having 2 instances of SQL Server 2008R2 and instance of SQL Server Express 2008.
SQLCMD, etc., works [1].

But "SQLCMD -L" and "OSQL -L" fail [2].
Why?
What am I missing in SQLCMD use?
How to use SQLCMD to get servers?

[1] Test results of SQLCMD

>sqlcmd
1> use AdventureWorks2008R2;
2> SELECT TOP(2) BusinessEntityID, FirstName, LastName From Person.Person;
3> GO
Changed database context to 'AdventureWorks2008R2'.
BusinessEntityID FirstName                                          LastName

---------------- -------------------------------------------------- --------------------------------------------------
         285      Syed                                               Abbas
         293      Catherine                                          Abel
(2 rows affected)
1>

[2]

SQLCMD -L

Servers:

OSQL -L

Servers: -- NONE --

Update:
After starting SQLBrowser I have:

OSQL -L

Servers:
(local)
PBLACK
PBLACK\SQL2008R2
PBLACK\SQLEXPRESS

SQLCMD -L

Servers:
PBLACK
PBLACK\SQL2008R2
PBLACK\SQLEXPRESS

Why does OSQL enumerate (local) but SQLCMD not?
SQLEXPRESS (is broken, I cannot start it) and SQL2008R2 instance are not running (stopped) - why are they enumerated?
Is it possible to expose only running SQL Server instances?

+1  A: 

The instance discovery protocol relies on the SQL Server Browser Service. By default this service is disabled. You need to explicitly enable and start this service if you need to be able to discover the SQL Server instances installed.

Remus Rusanu
Plz see my update. What is the sense to expose stopped instances?
vgv8
1) `(local)` and `PBLACK` are the same. 2) The SQL Server Browser Service enumerates all installed instances, no matter whether they are started or stopped. Consider scenarios like SSMS management tools, than can enumerate instances and then expose them in the UI, from where they can be *started*.
Remus Rusanu