error while using distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

error while using distinct

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2008-07-10 : 09:08:46

Hi friends,
I m getting error while using the keyword distinct.I used the keyword distinct because i m getting the duplicate records to avoid this.The error message is "The text, ntext, or image data type cannot be selected as DISTINCT."

My query is
select dumyServices.serviceIndex, rpm_Provisioning.type,rpm_Provisioning.comments,rpm_Provisioning.[Approx Distance],rpm_Provisioning.nearby from rpm_Provisioning inner join dumyServices on (rpm_Provisioning.type=dumyServices.serviceName And dumyServices.TableName='Provisioning' And rpm_Provisioning.MarinaNo=833) order by dumyServices.serviceIndex

The values are
serviceIndex type comments Approx Distance nearby
1 Complete Provisioning Service NULL 0
1 Complete Provisioning Service NULL 0

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-07-10 : 09:13:41

Looks like you might be missing part of your join statement.
Can you post the table structures.

Or remove the Text, Ntext or Image column from the result set.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

vidhya
Posting Yak Master

108 Posts

Posted - 2008-07-10 : 09:18:44

i have s_link as image datatype in rpm_Provisioning table. i cant change any datatype for the tables. any other solution.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-07-10 : 09:27:41

s_link is not in your select,m there is another column in the result set that is text,ntext or Image.
But as I said post your table structure.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

vidhya
Posting Yak Master

108 Posts

Posted - 2008-07-10 : 09:36:05

my table structure for rpm_Provisioning

seqid int
comments ntext
mno int
s_gen int
s_link image
type nvarchar
nearby int
approx distance nvarchar

the table for dumyServices

s_gen int
serivename nvarchar
slink image

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-10 : 09:52:23

If your rpm_Provisioning.comments value is well below 8000 characters, you can use following statement.
-- sql server 2000
CONVERT(varchar(8000), rpm_Provisioning.comments)

vidhya
Posting Yak Master

108 Posts

Posted - 2008-07-10 : 10:09:42

thanks i got it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 14:20:44

or even convert it to varchar(max) if you're using sql 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources