SELECT UNION - nText Field? 2

[1DMF]

Hi is there any way i can perform as distinct union that includes an nText field?

My SP is moaning and I need the address field which is nText.

How do i resolve this?

Thanks,
1DMF



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

It's OK, i the en I decided to bite the bullet and changed the field to nvarchar(350) and all is well with my distinct union.

I guess this highlights the fact that you should never use nText/Text, unless you really, really need that type of space requirments!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[SQLScholar]

1DMF,

Actually - i would say you should never use (N)Text now period!

(N)Text is going to be drepricated soon.

http://msdn.microsoft.com/en-us/library/ms187993.aspx

For the same storage requirements as text you should use (N)varchar(max).

Obviously if you are able to trim down to 350 charaters then all good - but if you need to play it safer (or someone else is reading this and doing the same) the like for like is nvarchar(max).

So likelyhood is 2008 r2 is the last time you will see text as datatype.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

you should never use nText/Text, unless you really, really need that type of space requirments!

I would add to this.

you should never use nText/Text, unless you really, really need that type of space requirments [!]and[/!] you are stuck using an older database engine like SQL7 or SQL2000.

With SQL2005 or newer, you should be using nvarchar(max)/varchar(max). You can store the same amount of data in a "max" column as you can a text column, but you don't need to deal with all the wiggyness of a text column.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Sorry SQLScholar. I didn't see your post when I replied.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Thanks guys!

I have both SQL2000 & SQL 2005, but soon to be replaced with SQL2008.

Does this mean before any migration can take place I need to change the data types?

Can SQL2000 have an nvarchar(max) ?

How does that migrate to SQL 2008 r2?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

Does this mean before any migration can take place I need to change the data types?

[blue]No. SQL 2008 and SQL2008 R2 can both use Text/nText columns[/blue]

Can SQL2000 have an nvarchar(max) ?

[blue]No. nvarchar(max) was first introduced in SQL2005[/blue]

How does that migrate to SQL 2008 r2?

[blue]Like I said earlier, you can still have text/ntext in SQL 2008 R2[/blue]

All that being said, if you need to support SQL2000 and SQL2005 with the same code base, then I wouldn't change anything right now. After you stop supporting SQL2000, I would encourage you to change the data type to nvarchar(max). When doing the conversion, you need to be careful of any existing code you may already have. Text columns have a lot of limitations, and they have their own functions like TextPtr, UpdateText etc... before changing the column's data type, you'll need to check your code to see if you are using any of these functions, and plan on changing that code at the same time you change the data type.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Much obliged George!

Once we migrate I will change the data types.

I don't think there is any code using any 'Text' special function, it's more a legacy data type that was brought over when first migtrating from MS Access to SQL 2000 and those pesky 'Memo' fields!

Regards,
1DMF



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta