Solved

SQL 2005 Select Distinct error

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.

822 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