0

Is anyone aware of any subtle differences between the behavior of SQL Server 2005 when executing a command via OSQL vs. a programmatic query using SQlConnection?

I have a client with a database on a remote machine that I can use OSQL to interact with. However, when I attempt a similar query via building a sqlconnection/sqlcommand; I receive the error:

['A network-related or instance-specific error occurred while establishing a connection to the SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that the SQL Server is configured to allow remote connections (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specific).

Here is the OSQL command which works:

osql -S myHost\SQLEXPRESS -U sa -q "USE [TempDB] select [Endpoint] from [Service]"

This doesn't work, the error above is generated.

SqlConnectionStringBuilder cnBldr = new SqlConnectionStringBuilder();

cnBldr.UserID = "sa";
cnBldr.Password  = "PASSWORD";
cnBldr.DataSource = "myHost\\SQLEXPRESS";
cnBldr.InitialCatalog = "TempDB";
using (SqlConnection cn = new SqlConnection(cnBldr.ConnectionString))

using (SqlCommand cmd = new SqlCommand("select [Endpoint] from [Service]",cn))
{
   cn.Open();
   SqlDataReader rdr = cmd.ExecuteReader();
   rdr.Read();
}

In both cases, I am running as the same user (ie: I log in as a windows user and I either open a cmd window and execute the OSQL command or I launch a windows application which contains the code fragment above. The OSQL will work, the code fragment generates the error above. Both cases utilize the 'sa' account for accessing the database. The OSQL fragment will prompt the user to input the password.

Is there some difference between the two that could attribute to this behavior? I don't believe that this is due to a problem with the number of connections as I can freely switch between executing the osql command (which will work) and running the application (which generates the error message).

Thanks for any thoughts.

-john

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
John Kudrle
  • 1

2 Answers2

2

First don't use TempDB, the name is tempdb and your code will break on a case sensitive instance.

For the connectivity issue, follow the steps in SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified. OSQL uses ODBC and SQLCommand has a built-in managed SNI implementation, so there are differences between them, and they are subject to different machine global configuration settings. Follow the steps in the linked article to identify where does the connectivity breaks.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Hi,Yes I was running on the same machine in both bases. TempDB was merely an annotation to remove the name of the real database. Sorry for the confusion; I should have called it fooDB. – John Kudrle Jun 13 '11 at 19:28
  • Also, the same behavior is observed if I use the more sqlcmd (vs. osql) (ie: sqlcmd -S myHost\SQLExpress -U sa -q "USE [tempDB] select [Endpoint] from [Service]" will also produce the proper output. – John Kudrle Jun 13 '11 at 19:30
  • Does you app attempting to connect result in an error added to the server ERRORLOG? Check either the file, or use EventVwr.exe – Remus Rusanu Jun 13 '11 at 19:32
0

As it turns out there is no difference between the two mechanisms. The application was being launched slightly differently; and this difference (specifically a name resolution prior to the query being generated) was what led to the error message.

There are no inherent differences between osql/sqlcmd and a programmatic query from this point of view.

John Kudrle
  • 1