SELECT DISTINCT not working in .NET application, but works in SQL Mgmt Studio

Question

I have a web app where I am calling a SELECT DISTINCT query on two joined tables. If I try to run the query from the web page, I get the following error: "The text, ntext, or image data type cannot be selected as DISTINCT". When I run it from within SQL Management Studio, the query runs fine - no error. Even more interestingly, there are no text, ntext, or image data types in either of the tables.

It should be noted that I'm stepping through the code, and right before the query is executed, I'm copying the query from the "watch" window into Mgmt Studio, and it runs, when I step through and let .NET run it, the error is thrown. I'm using .NET 2, and the System.Data.SqlClient namespace.

Here is my query:

SELECT DISTINCT ResponseFormParent.* 
FROM ResponseFormParent 
INNER JOIN ResponseForm 
  ON ResponseFormParent.ResponseFormParentId = ResponseForm.ResponseFormParentId 
WHERE ResponseForm.RegistrationDeadline >= '5/1/2009'

ResponseFormParent has 3 ints, 1 datetime, and 1 nvarchar(50). ResponseForm has 4 ints, 1 datetime, 1 bit, and 1 nvarchar(255).

What is causing the problem? The error doesn't make sense.

---

UPDATE: code to call query

public DataSet ExecuteQuery(string sql)
{
 DataSet ds = null;
 try
 {
  using (SqlConnection dbconn = new SqlConnection(connectionString))
  using (SqlCommand dbcmd = new SqlCommand(sql, dbconn))
  {
   SqlDataAdapter dbadapter = new SqlDataAdapter(dbcmd);
   ds = new DataSet();
   dbadapter.Fill(ds);
  }
 }
 catch (Exception ex)
 {
  Utility.LogError(sql, ex);
 }
 return ds;
}

Answer 1

You might be querying two different databases. Have you checked your Connection in your app to make sure you're actually querying the same database that you're querying against in SQL Mgmt Studio? It's also possible that you're pointing to different servers. You might want to check that in your Connection as well.

If either is the case, then it's most likely a database schema mismatch between the two databases you're querying against.

Answer 2

Are you sure the web app is connecting to the same database? (i.e. dev vs. production)?

Maybe someone changed that nvarchar(255) to a text column in one database but not the other...

Answer 3

Instead of stepping through the code and copying the output into Management Studio, run Profiler against the database and capture the exact query sent to the server. Then copy that (including the sp_executesql if it's there) and run it in Management Studio.

This will not only give you the exact query, but it will also confirm that it's running against the correct database.

Answer 4

Just tested a DISTINCT query on a table that contains an ntext column. It works as long as you avoid selecting the ntext column. When you include the ntext column, you get the error you name.

So are you sure your code is doing:

 SELECT DISTINCT ResponseFormParent.*

And not

 SELECT DISTINCT *

Answer 5

If try to select one column at a time the problem still persists ?

Answer 6

What happens if you wrap your selection code into a stored procedure & call that from your C# code?

Answer 7

Have you checked if the same credentials are being used in both connections?

Answer 8

You're sure it hasn't got anything to do with using a DataAdapter? Have you tried your code using a reader ==> dbcmd.ExecuteReader() ?

Answer 9

Try specifying the columns instead of using select * (which should not be used on prod anyway). Could be the Data Adapter is just assuming you might have that type of column (just guessing here) when you use select *.