sqlcmd -L (server list is empty)

  • This starting happening all of a sudden with a SQL 2005 ENT x64 server (SP2) with 2 instances being rebooted. Prior to the reboot, doing a "sqlcmd -L" would yield results, showing a list of SQL instances on the network. Now, after the reboot, doing a "sqlcmd -L" yields an empty list. The output from the sqlcmd is being used in a script to enumerate databases, and unfortunately is not returning any results.

    All SQL instances on this machine are running and using a domain account.

    Anyone have any thoughts on this? At first I thought it might be the browser service, but not too sure. Google searches on this topic have come back with no suggestions.

    Thanks.

  • Hi,

    the list of servers returned by sqlcmd -L isn't guaranteed to be the same every time it's run (refer to BOL...).

    Are you running this command on the same server where the SQL instances are running? Can you connect to SQL from a different machine, ie the Windows firewall isn't running and blocking requests to the SQL Server listening ports?

  • It is possible to hide a SQL Server intance from beeing detected by sqlcmd -L.

  • If the SQL Browser isn't running, or the UDP 1434 is blocked, then I believe -L doesn't work.

  • Thanks everyone.

    Weird thing, it started working again today, without any changes to the server.

    @Danie Loots

    Yes, I understand it is inconsistent from time-to-time, and have experienced that first hand 🙂 Windows Firewall is disabled and I am running this from a different machine than the servers I want to enumerate. Running from my local laptop with firewall enabled produced more results oddly 🙂

    @steve-2 Jones

    Thanks Steve for the tip. That was the first thing I checked on the server that the SQL Browser service was running, I even restarted it.

    I spent a good time "googling" for results and couldn't find anyone with a similar situation. I knew I could count on this forum for some immediate response.

  • Ask your network folks if they changed any routing or firewall rules.

    Also, if your network is segmented it may not show all servers. Again of you have multiple V-LANs the same applies.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

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

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