SELECT DISTINCT不在.NET应用程序中工作，但在SQL Mgmt Studio中工作

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.

我有一个Web应用程序,我在两个连接表上调用SELECT DISTINCT查询。如果我尝试从网页运行查询,我会收到以下错误:“text,ntext或image数据类型不能被选为DISTINCT”。当我从SQL Management Studio中运行它时,查询运行正常 - 没有错误。更有趣的是,任何一个表中都没有text,ntext或image数据类型。

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.

应该注意的是,我正在逐步执行代码,并且在执行查询之前,我正在将查询从“监视”窗口复制到Mgmt Studio中,当我单步执行并让.NET运行时,它会运行它,错误被抛出。我正在使用.NET 2和System.Data.SqlClient命名空间。

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).

ResponseFormParent有3个整数,1个日期时间和1个nvarchar(50)。 ResponseForm有4个整数,1个日期时间,1个位和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;
}

9 个解决方案

#1

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.

刚刚在包含ntext列的表上测试了DISTINCT查询。只要您避免选择ntext列,它就会起作用。当您包含ntext列时,您会收到您输入的错误。

So are you sure your code is doing:

所以你确定你的代码正在做:

 SELECT DISTINCT ResponseFormParent.*

And not

 SELECT DISTINCT *

#2

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.

您可能正在查询两个不同的数据库。您是否在应用程序中检查了Connection,以确保您实际查询的是在SQL Mgmt Studio中查询的数据库?您也可能指向不同的服务器。您可能还想在Connection中检查它。

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

如果是这种情况,那么很可能是您要查询的两个数据库之间的数据库模式不匹配。

#3

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

如果将选择代码包装到存储过程中并从C#代码中调用,会发生什么?

#4

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

您确定Web应用程序是否连接到同一个数据库? (即开发与生产)?

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

也许有人将nvarchar(255)更改为一个数据库中的文本列而不是另一个数据库中的文本列...

#5

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.

不是单步调试代码并将输出复制到Management Studio,而是对数据库运行Profiler并捕获发送到服务器的确切查询。然后复制它(包括sp_executesql,如果它在那里)并在Management Studio中运行它。

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

这不仅会为您提供确切的查询,还会确认它是针对正确的数据库运行的。

#6

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

您是否检查过两个连接中是否使用了相同的凭据?

#7

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

如果尝试一次选择一列,问题仍然存在?

#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() ?

您确定它与使用DataAdapter没有任何关系吗?您是否使用过reader ==> dbcmd.ExecuteReader()尝试过代码?

#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 *.

尝试指定列而不是使用select *(它不应该在prod上使用)。可能是数据适配器只是假设您使用select *时可能有这种类型的列(只是在这里猜测)。

#1