Solved

SQL 2005 Select Distinct error

Posted on 2007-11-14
5
3,811 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

Independent Software Vendors: 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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

726 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