Solved

SQL 2005 Select Distinct error

Posted on 2007-11-14
5
3,818 Views
Last Modified: 2010-04-21
I'm running SQL 2005 Management Studio on an XP Pro workstation and am running the following SQL Statement to prevent the insert of duplicate data from a Table called Software to a table called Packages.

Here's the SQL Query that I'm running:
ALTER PROCEDURE [dbo].[SpPackagesTable_Delete_ReseedIdentity_Insert ]

AS

DELETE
FROM CRPRDNMSQABE.platform_validation_tool.dbo.packages

DBCC CHECKIDENT ('platform_validation_tool.dbo.packages', RESEED, 0);

INSERT INTO platform_validation_tool.dbo.packages (Name, Version, Build)
SELECT Distinct Name, Version, Build
FROM crprdnmsqabe.platform_validation_tool.dbo.software

Everything worked fine when our Software and Packages tables datatypes were set to varchar. One of our Engineers decided to change the datatypes to "text" on all the tables including these two. When I run this query, here's the error I get:

Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.

Does the Distinct keyword have an issue running queries on tables with datatype TEXT and/or is there another command I can use to workaround this issue?

Thank you,
Wallace
0
Comment
Question by:wally_davis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
ID: 20281924
if this is sql server 2005, replace those Text datatype with VARCHAR(MAX) because, in sql 2005, 'text' datatypes are deprecated.

or else , you can covert those 'text' values to varchar(8000)  inside that DISTINCT part
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 20282435
>>One of our Engineers decided to change the datatypes to "text" on all the tables including these two. <<
What was he/she thinking?  You cannot have a DISTINCT on a text datatype.  As Aneesh points out, they should be using varchar(MAX)
0
 

Author Comment

by:wally_davis
ID: 20282886
Before accepting a solution, can any of you tell me why using the datatype of "text" is not ideal and the apparent drawbacks (such as this one) that one might have when using this datatype and two, what situations would you want to use text?

Thank you for you replies,
Wallace
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20285838
>> what situations would you want to use text?<<
Let me make it easy for you, with SQL Server 2005:  Never.
0
 

Author Closing Comment

by:wally_davis
ID: 31409334
Everything was working great using the Varchar datatype and when one of the engineers decided to convert everything to text/with all the query errors I was receiving and pointing to the "text" datatype, I knew something was up. Thank you for saving me countless troubleshooting hours. It's good to know we have experts who have the experience to prove to some, that they don't know everything.....
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question