Azure, Classic ASP and NVarchar(MAX)

For some reason. I dont know why, moving a site with classic asp from a virtual machine (Win 2k8r2, IIS 7, .net 4 integrated) to azure websites makes sql server and NVarchar(max) stop working.

It doesn’t throw an error, it just gives no data in the field. I even tried Switching the Driver to “Driver={SQL Server Native Client 11.0}”

So I finally figured out that it was the (max) that was the difference. So I worked out a script (C# console app) to run to convert the NVarchar(max) that didn’t need to be so.

[sourcecode lang=”csharp”]
SqlConnection myConnection = new SqlConnection(“connectionstring”);
myConnection.Open();
SqlDataReader myReader = null;
var todo = “select * from INFORMATION_SCHEMA.COLUMNS inner join INFORMATION_SCHEMA.TABLES on INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME where DATA_TYPE like ‘%varchar’ and CHARACTER_MAXIMUM_LENGTH = -1 AND TABLE_TYPE = ‘BASE TABLE’ order by INFORMATION_SCHEMA.TABLES.Table_Name”;
var myCommand = new SqlCommand(todo,myConnection);
myReader = myCommand.ExecuteReader();
var infos = new List();
while (myReader.Read())
{
infos.Add(new info()
{
CHARACTER_MAXIMUM_LENGTH = myReader[“CHARACTER_MAXIMUM_LENGTH”].ToString(),
table_name = myReader[“TABLE_NAME”].ToString(),
COLUMN_NAME = myReader[“COLUMN_NAME”].ToString()
});

}
myReader.Close();
foreach (var info in infos.OrderBy(f=> f.table_name))
{
Console.Write(“{0}.{1} “, info.table_name, info.COLUMN_NAME);
var colmaxQuery = String.Format(“SELECT MAX(LEN([{0}])) as maxlen from [{1}]”,
info.COLUMN_NAME,
info.table_name);
var myCommand2 = new SqlCommand(colmaxQuery, myConnection);
var myReader2 = myCommand2.ExecuteReader();
while (myReader2.Read())
{
info.read_maximum_length = int.Parse(myReader2[“maxlen”].ToString());
Console.Write(” –> Max Len = {0}\n”, info.read_maximum_length );
}
myReader2.Close();
}
foreach (var info in infos.Where(f=> f.read_maximum_length < 2000))
{
var command = String.Format("ALTER TABLE [{0}] alter column [{1}] nvarchar({2})",
info.table_name,
info.COLUMN_NAME,
Math.Max((info.read_maximum_length * 2) ?? 0, 201));
Console.WriteLine(command);
var myCommand3 = new SqlCommand(command, myConnection);
try
{
myCommand3.ExecuteNonQuery();
}
catch (Exception)
{

Console.WriteLine("Exception");
}
}
[/sourcecode]

This code pulls the nvarchar(max) from INFORMATION_SCHEMA.COLUMNS then looks at the longest actual column usage of each of the columns. If that usage is less than 2000, the script alters the column to be nvarchar(2 * max length) which allows for growth. nvarchar(4000) is the maximum valid value so that's why it looks for 2000.

That solved all of my issues (mostly) but there are some other strategies that could work for you.

  1. If you arent using unicode varchar can be (8000)
  2. switch to ntext
  3. Use Provider=SQLNCLI11 (may involve provider install)
  4. Split into multiple columns

The difference between Native Client and NCLI are subtle and way over my head but here they are Microsoft SQL Server Native Client and Microsoft SQL Server 2008 Native Client

If someone tells me what I am doing wrong I would totally appreciate it, but this works for me.