This is one of those completely obtuse issues that one hits from time to time – nvarchar(max) fields behave oddly on Microsoft Azure SQL – yet it all works fine on SQL Server 2008 on my local machine.
I battled this for an hour before finally simultaneously figuring out that trying to return data from an nvarchar(max) column via Classic ASP on Azure yields a blank value (or weird binary garbage); and falling over someone else who’s had exactly the same problem recently.
It’s fine to have a table with nvarchar(max) in Azure. I can push data into it, I can see the data via SSMS, but returning via ASP – nada.
I’m returning the data via a UDF, so I altered the table definition returned from the function so the column is nvarchar(4000). My function grabs the data from the (max) column and sticks it into the output column without trouble, and the function returns values fine, don’t even need a CAST.
It’s frustrating because I’m anticipating these columns to hold large blocks of text, and now I’m size restricted.
I haven’t had time to explore whether messing with the connection strings might do the trick.