Union unable to use nText fieldtype

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

Union unable to use nText fieldtype

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-02-02 : 10:35:37

I use a union below which works for all columns except stext. The error I get is:
Msg 421, Level 16, State 1, Line 1
The ntext data type cannot be selected as DISTINCT because it is not comparable.

I'm selecting sText from the strtoolinfo table in both sides of the Union so it should work and does work with other columns. So I'm stuck. After googling the problem I'm not getting any nearer to the solution. I'm using sql2005 but the data may reside on both sql2000 and sql2005. Anyone here have any ideas?

select strtoolinfo.stext
from strlnktooltoolinfo, strtoolinfo
where strlnktooltoolinfo.ltoolinfoid = strtoolinfo.ltoolinfoid and strlnkToolToolInfo.sToolGUID = '{79be4f69-6081-4029-8fe7-802048030b5b}'
union
select stext
from strtoolinfo
where ltoolinfoid not in (select ltoolinfoid from strlnktooltoolinfo where stoolguid = '{79be4f69-6081-4029-8fe7-802048030b5b}')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 10:48:59

you cant use union with ntext datatype. if you're using sql 2005, try after casting it to nvarchar(max)

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-02-02 : 10:50:25

quote:
Originally posted by insanepaul

I use a union below which works for all columns except stext. The error I get is:
Msg 421, Level 16, State 1, Line 1
The ntext data type cannot be selected as DISTINCT because it is not comparable.

I'm selecting sText from the strtoolinfo table in both sides of the Union so it should work and does work with other columns. So I'm stuck. After googling the problem I'm not getting any nearer to the solution. I'm using sql2005 but the data may reside on both sql2000 and sql2005. Anyone here have any ideas?

select strtoolinfo.stext
from strlnktooltoolinfo, strtoolinfo
where strlnktooltoolinfo.ltoolinfoid = strtoolinfo.ltoolinfoid and strlnkToolToolInfo.sToolGUID = '{79be4f69-6081-4029-8fe7-802048030b5b}'
union
select stext
from strtoolinfo
where ltoolinfoid not in (select ltoolinfoid from strlnktooltoolinfo where stoolguid = '{79be4f69-6081-4029-8fe7-802048030b5b}')

OK, I found out I should use nvarchar(max) on sql2005. I've changed ntext to this and it works....BUT...what about databases that still use the old sql2000. will it work for them?

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-02-02 : 10:51:06

quote:
Originally posted by visakh16

you cant use union with ntext datatype. if you're using sql 2005, try after casting it to nvarchar(max)

ok...so rather than change the database i can cast it ...thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 10:52:45

nope. you could try with union all instead. it works as it doesnt take DISTINCT internally

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-02-02 : 11:02:07

quote:
Originally posted by visakh16

nope. you could try with union all instead. it works as it doesnt take DISTINCT internally

Thanks for your help however your last statement isn't totally clear to me - are you saying i cannot cast to nvarchar(max) on sql2000?.

I haven't heard or 'Union all' but i tried it (dont have sql2000 to test it on) and it works on sql2005.

So conclusion I think is to use 'Union all' to cover both sql2000 and sql2005 but if its just sql2005 then either cast to nvarchar(max) or change the fieldtype to that.

I think thats correct - many thanks for your help I was spending too long on this.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-02 : 11:33:35

VARCHAR(MAX) and NVARCHAR(MAX) don't exists on SQL sever 2000 so you can't use them.

UNION (without the ALL) gives you data just like SELECT DISTINCT does

Therefore

SELECT 'x' UNION SELECT 'x' gives:

1) x

SELECT 'x' UNION ALL SELECT 'x' gives:

1) x
2) x
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 11:49:03

quote:
Originally posted by insanepaul

quote:
Originally posted by visakh16

nope. you could try with union all instead. it works as it doesnt take DISTINCT internally

Thanks for your help however your last statement isn't totally clear to me - are you saying i cannot cast to nvarchar(max) on sql2000?.

I haven't heard or 'Union all' but i tried it (dont have sql2000 to test it on) and it works on sql2005.

So conclusion I think is to use 'Union all' to cover both sql2000 and sql2005 but if its just sql2005 then either cast to nvarchar(max) or change the fieldtype to that.

I think thats correct - many thanks for your help I was spending too long on this.

yup you need to use UNION ALL if its sql 2000
No problem...you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources