How you are all dealing with ntext, text and image data types ?

Question

How you are all dealing with ntext, text and image data types ?

VG-619426

Hall of Fame

Points: 3914
More actions
September 22, 2008 at 4:48 pm

#194037

Hi,
I upgraded my sql server 2005 database from compatibilty level 80 to 90 on dev environment. We had a problem, as one of the stored proc was using distinct select, but select list did not involve any ntext data type column at all. One of the select query inside it had ntext data type column. After I changed ntext to nvarchar(max) that resolved.
Does everyone converted their data types as these are not supported in future versions?
How do I find all references inside stored proc for ntext, like one used inside temp tables ?
Thank you,
VG

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

Although NVarChar(Max) is preferable in my book over NText, NText is still available in SQL Server 2005 (according to BOL) and you shouldn't have to make the change you speak of.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bcronce SSCrazy Points: 2597 More actions · Answer 2

You can't compare text types to eachother, which means you can't run a distinct statement that outputs or groups on a text column. I don't think sql stores text types in the page, so you can't do all your regular SQL operations on it. I could be wrong as for the reason, but I'm pretty sure text types can't be comapred.

Could be wrong

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

bcronce (9/23/2008)
You can't compare text types to eachother, which means you can't run a distinct statement that outputs or groups on a text column. I don't think sql stores text types in the page, so you can't do all your regular SQL operations on it. I could be wrong as for the reason, but I'm pretty sure text types can't be comapred.
Could be wrong

Yep... I knew that. But, if you have a working stored procedure that works on a table that has a text type in it, I'm pretty sure no changes are required during a migration to 2k5. Of course, the change should be desired, but I don't believe it's required.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VG-619426 Hall of Fame Points: 3914 More actions · Answer 4

It is happening after upgrading to compatibility 90.

I found it to be a UNION issue...Each brach of union cast to result type of union and then cast to the target column type if INSERT.

In our union one of the column data type ntext and second one has nvarchar.

Insert into table

(col1(int), col2 (nvarcahr(max)))

Select

col1, col2 --- ntext

from tablw 2

union

select

col1, col2 -- nvarchar

from table 3.

Either we have to use cast to convert to nvarcahr here..We are suggested to convert all the ntext, text, image data types in all our databases. It is lot of work, i know..I am not sure it is worth it ?????

VG