Strange problem with nvarchar(max) fields and Classic ASP

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?

---

Very old thread - admitted. But I had this issue and it was driving me INSANE.

I fixed this by changing my connection string like this:

objConn.Open "Driver={SQL Server}; Server=(local); Database=<what-your-database-is-called>; Uid=sa;Pwd=sa;"

to:

objConn.Open "Provider=SQLNCLI; Server=(local); Database=<what-your-database-is-called> ; Uid=sa;Pwd=sa;"

So, essentially, removing Driver={SQL Server} and adding in "Provider=SQLNCLI;", FIXED THIS.

I can now see the nvarchar(max) column displayed correctly in my Classic ASP page.

Hope this helps someone.

---

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.

---

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.

---

I did cast(columName to varchar) and it worked for me.

---

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.

---

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

---

This issue remains or resurfaces with using DSN's on Server 2012 and 2016.

Using DSN send the data thru an immediate layer that appears to handle varbinary(max) fine but not varchar(max) or nvarchar(max).

It is a bit worst, because over time if you have actual results over 9K (in my case, one that was 400K), there appears to be corruption of memory/code and the server errors with a vague external component error