Strange problem with nvarchar(max) fields and Classic ASP

Question

I'm working on a Classic ASP (VBScript) site that's running on a Windows 2008 server with IIS7 and hitting a SQL Server 2008 database and I'm seeing some strange behavior that seems to be specific to fields that are of type nvarchar(max).

I have this simple bit of code that loops over some query results and tries to print out the title field twice

rs.open "SELECT * FROM thing", dbConnection

do while not rs.eof
    response.write "(" & rs("title") & ")" & "(" & rs("title") & ")" & "<br />"
    rs.movenext
loop

The first time I use rs("title"), I get the value back. Any time after the first time in that loop, rs("title") returns a blank value.

Again, this only seems to happen with the nvarchar(max) fields.

Can anyone shed any light on this behavior and how to get around it?

Answer 1

Not sure that this is the cause, but one thing worth noting is that the varchar(max) type was not added to Sql Server until well after the last version of Classic ASP was released. So it's very possible that the old ado provider just doesn't know how to deal with those fields.

Answer 2

aside from the 'pull it into a variable before display'... I vaguely remember coworkers needing some hack with asp/sql/varchar(max) queries, something like it had to be the last column (or not the last column) in the query. Really sorry for the vagueness, it's been a few years since I've had to deal with asp.

Answer 3

If you take a look at this link, http://msdn2.microsoft.com/en-us/library/ms130978.aspx it says to enable SQL Server Native Client to get the latest features such as varchar(max) which was was introduced in SQL2005, so I think maybe using this would work for you as well even though you're using SQL 2008.

Answer 4

I just had a similar problem (only with SQL Server 2005, not 2008):

If Not IsNull(rs("Title")) Then
    Response.Write "The title: " & rs("Title") 
End If

The Response.Write was executed, but the title itself was not displayed.

It took me quite some time until I figured out that the combination of ASP Classic and nvarchar(max) was causing the problem.
Then I found this and did what was described there...I changed my code to this:

SomeVariable = rs("Title")    
If Not IsNull(SomeVariable) Then
    Response.Write "The title: " & SomeVariable 
End If