Search code examples
sql-serversql-server-2008sqlcmdosql

What are the default locations that OSQL and SQLCMD search to connect to a server when -S option is not specified?

I am trying to identify how osql and sqlcmd resolve server name and user to connect to that server when we do not specify server using -S option and use -E option. Below is the output for various queries I ran.

  1. List all available servers on the network
    osql -L -- Returns all the servers on the network except the one that it connects to by default
    sqlcmd -L -- Returns the same list as above
  2. Connect to the default server without using -S option
    osql -E -d dbname -Q "select current_user" -- does not return the current user. Instead returns a user configured in the SQL server which I can see is present in windows registry but not sure how any why osql picks it.
    sqlcmd -E -d dbname -Q "select current_user" -- gives and error Named pipes provider: Could not open connection to SQL Server
  3. Connect using server name and -S option
    osql -E -S servername -d dbname -Q "select current_user" -- same output as in point 2 above
    sqlcmd -E -S servername -d dbname -Q "select current_user" -- gives the login id used to login to the system from where I am executing this query\
  4. Execute command remotely
    osql -E -d dbname -Q "select current_user" -- gives the same output as point 2
    sqlcmd -E -s servername -d dbname -Q "select current_user" -- throws an error Login failed for computername where computername is the actual windows computer name

I have been at it for days now trying to find how is osql working. I need to figure this out to move to sqlcmd.

Edit 1: I can confirm that there is no environment variable OSQLSERVER

Solution

  • After days of effort, I found that our organization had built an executable and named it osql. That was the reason that a new installation was not working but the old one was.