By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,299 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,299 IT Pros & Developers. It's quick & easy.

cannot convert/cast ntext

Expert 100+
P: 1,240
Can you tell me why this
Expand|Select|Wrap|Line Numbers
  1. CAST(ExtensionData AS nvarchar(10)) AS Expr1
  2.  
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
May 20 '15 #1

✓ answered by Rabbit

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

Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,430
I believe it has to be converted or casted first to nvarchar(max)
May 21 '15 #2

Expert 100+
P: 1,240
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT     CustomerID, dbo.MASCustomerID(ExtensionData) AS MASCustomerNo, CAST(ExtensionData AS nvarchar(10)) AS Expr1
  2. FROM         dbo.Customer
  3. WHERE     (Deleted = 0) AND (NOT (ExtensionData IS NULL))
May 21 '15 #3

Rabbit
Expert Mod 10K+
P: 12,430
You're using 10, I think it has to be max, you can substring it after.
May 21 '15 #4

Expert 100+
P: 1,240
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
May 21 '15 #5

Rabbit
Expert Mod 10K+
P: 12,430
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.
May 21 '15 #6

Expert 100+
P: 1,240
Very educational. Thank you.
May 24 '15 #7

Post your reply

Sign in to post your reply or Sign up for a free account.