Solved

SQL 2005 Select Distinct error

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now