Archives
-
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);