Cannot specify a column width on data type ntext - SQL Server 2014 Enterprise (64 bit)

Question

Cannot specify a column width on data type ntext - SQL Server 2014 Enterprise (64 bit)

mkaresh85

SSC Enthusiast

Points: 182
More actions
May 2, 2016 at 7:38 am

#326068

Hi SQL experts,
I am working on our product bug which reports problem in automatic creation of few dynamic tables with following error: [SQLServer]Column\, parameter\, or variable #002322: Cannot specify a column width on data type ntext.
Seems this used to work fine with SQL server 2012 version.
I understand 'ntext' is still supported and not sure why this would fail with SQL server 2014.
Kindly don't recommend to remove the "width" value - like people have done in other posts out there.
Thanks.

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

ntext doesn't have a length definition. It has a predefined maximum size.

You should stop using ntext and start using nvarchar(max). It has the same capabilities and it's easier to manage. The only reason would be if you're using any functions related to handle text data types.

For a shorter length, use varchar and nvarchar with an adecuate length.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 2

mkaresh85 (5/2/2016)
Hi SQL experts,
I am working on our product bug which reports problem in automatic creation of few dynamic tables with following error: [SQLServer]Column\, parameter\, or variable #002322: Cannot specify a column width on data type ntext.
Seems this used to work fine with SQL server 2012 version.
I understand 'ntext' is still supported and not sure why this would fail with SQL server 2014.
Kindly don't recommend to remove the "width" value - like people have done in other posts out there.
Thanks.

Looks like there is a bug in the table creation code where it attempts to specify the width of the ntext column which is a syntax error.

😎

NTEXT is supported in SQL Server 2014

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Firstly you should be using nvarchar(max) since ntext has been deprecated for ~11 years, but the problem does sound like the width. ntext isn't defined with a width, it's just ntext, so yes you probably should be removing the width like others have told you.

It's nvarchar that has a size specified. nvarchar(500), nvarchar(max), but just plain ntext.

Fails

CREATE TABLE #test(

a NTEXT(2000)

);

Succeeds

CREATE TABLE #test(

a NTEXT

);

As for why it worked on 2012 (if it did, I don't have a 2012 instance to test on), probably a bug in the parser that was fixed in the newer version.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mkaresh85 SSC Enthusiast Points: 182 More actions · Answer 4

Thanks for all the replies.

I too came across recommendation to use 'nvarchar' instead of 'ntext'.

Not sure how this would have worked with SQL server 2012! I will have to ask for SQL server 2012 instance to validate.