Solved

Select Distinct ColumnName From Table produces error I believe due to spaces, tabs or carriage returns

Posted on 2008-10-23
5
176 Views
Last Modified: 2013-12-17
I'm running a query against one of our tables.
Select Distinct ColumnName From Table. (SELECT DISTINCT FULLERROR
FROM [DMS].[dbo].[SD_LOGDATA])
When I run this command it produces the error "The text data type cannot be selected as DISTINCT because it is not comparable."
There are these funny looking squares in the FULLERROR Column and I believe these may be spaces, tabs or carriage returns.
Does anyone know what command I should run or if there's a way to change something up on the table to fix this?
Regards,
Wally
0
Comment
Question by:wally_davis
  • 2
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 250 total points
ID: 22790394
It's not due to the carriage returns.  It's due to the data type -- TEXT.

Are you allowed to change data types?  If so, change to Varchar(Max) -- new for SQL 2005.
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 250 total points
ID: 22790449
OR... since changing the data type of a column can have massive ramifications, CAST the column in your select.

SELECT DISTINCT cast(FULLERROR as nvarchar(max))
FROM [DMS].[dbo].[SD_LOGDATA]
0
 

Author Comment

by:wally_davis
ID: 22790556
I used varchar in place of nvarchar(max)). So, yes we can change the datatype and we eventually will. both are great solutions and will award you each 250 points.
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 250 total points
ID: 22790609
the problem with varchar is that varchar, without specifying a length, will default to 30.

So use varchar(max) instead of nvarchar(max) if you have a text column isntead of ntext.  But you should use max probably.  Why else would it be a TEXT column if it didn't house long data?
0
 

Author Closing Comment

by:wally_davis
ID: 31509412
Good point Brandon. Well, someone else on my team assigned Text datatype to this column that will hold a large amount of data when they should have assigned datatype varchar.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Not showing page correctly 3 29
SSL https .net web site force redirect 3 31
Javascript and Jquery not firing 9 37
Extract string portion 2 0
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

929 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

10 Experts available now in Live!

Get 1:1 Help Now