Solved

SQL 2005 Select Distinct error

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need help with a query 4 44
Order by but want it in specific order 2 23
Analysis of table use 7 30
CREATE DATABASE ENCRYPTION KEY 1 44
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 SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 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

22 Experts available now in Live!

Get 1:1 Help Now