Query Error when using ORDER BY if the column and * is in the SELECT list

Suppose, you have a query where you get one column explicitly and all other columns using * then you get an Ambiguous column name error.

The following query results in an error:

SELECT Title,* FROM HumanResources.Employee ORDER BY Title

Msg 209, Level 16, State 1, Line 1

Ambiguous column name 'Title'.

The following does not give an error:

SELECT Title,* FROM HumanResources.Employee

So whats’s the solution?

Adding an alias for the table and using alias with the column in ORDER BY or providing a fully qualified path to the column in ORDER BY will fix this issue.

One of the following query will fix this issue.

SELECT Title,* FROM HumanResources.Employee e ORDER BY e.Title

SELECT Title,* FROM HumanResources.Employee ORDER BY HumanResources.Employee.Title

If the Title column was not in SELECT list, the issue would not arise. The following query works perfectly:

SELECT * FROM HumanResources.Employee ORDER BY Title

Also, if you didn’t have * in your select, but just specific list of columns, then also you do not need to have alias in order by

SELECT EmployeeID,Title FROM HumanResources.Employee ORDER BY Title

So, it’s only when you have a combination of explicit column name and a * in the SELECT you get this issue.

Weird issue or the way syntax is expected by SQL. However, you have to live by some quirks which creates jobs for DB programmers.

Sql Server Connection Problem

Running a c# 2.0 windows application, connecting to sql server 2005 standard edition, gave me the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The error occured when the connection was being opened.

On further drilling down in the debugger, it gave additional information as follows:

ServerVersion = 'DatabaseManager.Connection.ServerVersion' threw an exception of type 'System.InvalidOperationException'

Now, here the problem started. I was misled by the ServerVersion related information and thought that it had something to do with the server version and my efforts were targeted to that error.

After several hours of searching through all sort of sites and changing stuff, finally found the problem:

The connection string, though looked fine, had a problem:

string conn = "Data Source=theserver\release;Initial Catalog=AdventureWorks;User id=sa;password=xyz;";

Since, I had created a string variable and was assigning the connection string, the instance not being the default instance, had to use a convention server\instance for the data source.

Now the \ was being taken as the escape character and trying to do all sorts of things.
The fix:
change the single \ to \\, escape the \.

string conn = "Data Source=theserver\\release;Initial Catalog=AdventureWorks;User id=sa;password=xyz;";

Hope this saves your time.