473,320 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

cannot convert/cast ntext

1,271 Expert 1GB
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)

6 4681
Rabbit
12,516 Expert Mod 8TB
I believe it has to be converted or casted first to nvarchar(max)
May 21 '15 #2
jimatqsi
1,271 Expert 1GB
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
12,516 Expert Mod 8TB
You're using 10, I think it has to be max, you can substring it after.
May 21 '15 #4
jimatqsi
1,271 Expert 1GB
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
12,516 Expert Mod 8TB
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
jimatqsi
1,271 Expert 1GB
Very educational. Thank you.
May 24 '15 #7

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

Similar topics

5
by: Brad Moore | last post by:
Hey all, I'm getting the following compiler error from my code. I was wondering if anyone could help me understand the concept behind it (I actually did try and compile this degenerate...
2
by: Abhijit Bhadra | last post by:
Hi , I was trying to build my project in VC with latest Service Packs but got this error . C:\Program Files\Microsoft Visual Studio\VC98\ATL\INCLUDE\atlconv.h(125) : error C2440: 'return' :...
4
by: Daisy | last post by:
Having a weird problem... Just moving some code into new classes & methods. Originally, I created a new instance of "Game" (my form), and it created a new DX Device using: _device = new...
3
by: kaizen | last post by:
Hi, i wrote the code in C and compiled in VC++ compiler. at that time it has thrown the below mentioned error. error C2664: 'strcpy' : cannot convert parameter 2 from 'char' to 'const char *'...
1
by: fheinstein | last post by:
Hello, I am a newbies in .NET and i am followin a tutorial to create my first MFC program but when a compile a have this problem: See the file attach! Please help Compiling......
1
by: asenthil | last post by:
Hai this is senthil... I had tried to write a string which fetched from a database. into a file... when i tried to compile the solution the following error occurs like this error C2664:...
7
by: groups | last post by:
This is my first foray into writing a generic method and maybe I've bitten off more than I can chew. My intent is to have a generic method that accepts a value name and that value will be...
2
by: Akino877 | last post by:
Hello, I have a small program which uses FindFirstFile() to get a file name. Once I get the file name, I would like to use sscanf to extract certain part of the file name. My program is as...
1
by: balamk81 | last post by:
I've a question about ntext and text. How do you use them in a stored procedure? When I try to use an ntext data type in a proc i get this error - 'The assignment operator operation cannot take a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.