cannot convert/cast ntext

[jimatqsi]

Can you tell me why this

CAST(ExtensionData AS nvarchar(10)) AS Expr1

results in an error "Data type mismatch - no conversion possible"? [ExtensionData] is an ntext column.

This error pops up SQL Server Management Studio when I am editing a view; if I ignore it and run the view anyway, it pops up again.

It also happens if I use CONVERT instead of CAST. I've scoured the net and I keep seeing this construct is legal. The error occurs whether I use char, varchar, nvarchar, nchar

SQL Server (2008 R2)

Thanks for any help.

Jim

[Rabbit]

I believe it has to be converted or casted first to nvarchar(max)

[jimatqsi]

Isn't that what I'm doing? Maybe I don't understand what you mean. Here's the whole Select statement I've been trying:

SELECT     CustomerID, dbo.MASCustomerID(ExtensionData) AS MASCustomerNo, CAST(ExtensionData AS nvarchar(10)) AS Expr1
FROM         dbo.Customer
WHERE     (Deleted = 0) AND (NOT (ExtensionData IS NULL))

[Rabbit]

You're using 10, I think it has to be max, you can substring it after.

[jimatqsi]

Good grief, that worked! Thank you. I looked at many examples and it never sunk in that max was literally 'max' ... I will go back and reread what I have seen and try to get a better understanding of that.

Thank you!

Jim

[Rabbit]

Yeah, even though VARCHAR(8000) and VARCHAR(MAX) look similar, they are different. The maximum data size for "any" data type is 8000 bytes. Except that a VARCHAR(MAX) can hold 2 gigs of data. With a VARCHAR(MAX), anything under 8000 bytes is stored like a VARCHAR(8000), if the length exceeds that, it is stored elsewhere and a pointer is put in its place.

[jimatqsi]

Very educational. Thank you.