nvarchar(max) parameters need the size set to -1

SQL Server 2005 supports a new data type nvarchar(max). This is one of the new max datatypes that are to replace ntext, text, and image in a future version of SQL Server (according to SQL Server Books Online) but you should start using them now.

I recently used the nvarchar(max) data type for the first time in a stored procedure and I had some difficulty setting the parameter size in my C# code. I tried leaving off the size or setting it to the size of the string that was being passed to the stored procedure but none of these worked. Eventually I figured out that you have to set the size to -1 to get it to work.

Here is an example of how to create the SqlClient.SqlParameter:

System.Data.SqlClient.SqlParameter param;
param = new System.Data.SqlClient.SqlParameter();
param.ParameterName = "@Message";
param.SqlDbType = System.Data.SqlDbType.NVarChar;
param.Size = -1;
cmd.Parameters.Add(param);

Published 15 August 2008 05:00 PM by Jeff Widmer

Comments

# Damon said on 11 March, 2009 11:42 AM

Thank you

Saved me a lot of fiddling around

# Raghav said on 25 March, 2009 05:34 AM

Thanks its working

# rahul said on 04 June, 2009 06:35 AM

thanks you .... Saved lot of time...

# lucas cullen said on 01 July, 2009 10:09 PM

Good post, just what i was after.

# The Game said on 03 July, 2009 05:35 AM

Nice man.

Keep it up.

Really helped me.

Thanks.

# Richard Williams said on 13 July, 2009 12:45 PM

hi,

using it from VB6 and until recently worked fine - something changed (maybe a driver - not sure) and now it no longer works - any ideas - I can use the -1 trick for params that are IN but for ones that are input/output I get a badly formed params message

# Manish said on 17 August, 2009 01:05 AM

Thank U!

It Helped!

# Ton Nguyen said on 01 September, 2009 02:35 AM

Just found your post, and it worked! Thank you so much!

# tz said on 19 January, 2010 04:41 AM

less chatty:

var param = cmd.Parameters.Add(new SqlParameter("@Message", System.Data.SqlDbType.NText, -1));

# Eddy said on 28 January, 2010 10:15 AM

Thanks, useful tip...

# buitrunghieu said on 16 February, 2010 05:45 PM

Thanks a lot. This helps me so much.

# classicboyir said on 04 March, 2011 01:25 AM

Short and useful. Thanks

# Manoj said on 24 June, 2011 03:50 PM

Thanks bro this saved my time

# Sabu K J said on 30 September, 2011 07:11 AM

A useful information. thanks.

# Vibhu said on 01 November, 2011 10:03 AM

Thanks for the help

# Vijay said on 16 November, 2012 01:34 PM

Great Job! Helped Me!

# sudarshan said on 18 August, 2013 10:48 AM

Superb! Thanks lot you have save.d me in critical situaton

Leave a Comment

(required) 
(required) 
(optional)
(required) 

Search

Go

This Blog

News

Syndication