SQL Server text column affects results returned to classic ASP

Question

Using classic asp, I am trying to query a SQL Server database like so:

strSQL = "select column_1, column_2, column_3, column_4  from someview " &_
         "where RecordNo=" & i
set rs=conn.Execute(strSQL)

if not rs.eof then
    A = rs("column_1")
    B = rs("column_2")
    C = rs("column_3")
    D = rs("column_4")
end if

Column_3 is an NText type, the other columns are varchar or int (sometimes there may be more than 4 columns returned) but the query only returns 1 record because of the where clause.

On the ASP page the results vary - sometimes A,B,D are populated, sometimes not - but all columns in the view contain data (when I query the SQL Server I see the expected results - all columns do contain data). If I remove column_3 which is NText from the strSQL everything works fine.

I've seen this behaviour on a couple other pages in the past. If I modify the ASP to get column_3 separately:

 strSQL = "select column_3 from someview where RecordNo=" & i

The NText data is returned correctly.

Is there a maximum record length to a SQL Server recordset returned to classic ASP? Apart from splitting out the NTEXT into a separate query, is there anything else I can do?

EDIT: It just occured to me to try changing the connection string - inspired by this comment on a similar problem - the connection is via SQL Server ODBC Driver (Driver={SQL Server};).

Answer 1

casting ntext to varchar will do the job.

Answer 2

You're mixing unicode data (the ntext column) with non-unicode (varchar). That may be the reason, since the ASP page has to decide which to use.

Try and use either one or the other (casting non-unicode data to unicode may be the better option).

Answer 3

To avoid using the recordset, try this:

For 1 record returned:

arr = rs.Getrows

if IsArray(arr) then
 A = arr(0)
 B = arr(1)
 C = arr(2)
 D = arr(3)
end if

For more records:

aryList = rec.GetRows

iCount = Ubound(aryList,2)

For i = 0 to iCount
  A = aryList(0,i)
  B = aryList(1,i)
  C = aryList(2,i)
  D = aryList(3,i)
' Do something with A,B,C,D
Next

Answer 4

I have had this problem. Microsoft acknowledge it somewhere on their website.

If you put the NText column last in the SELECT list, you will be able to access it ok. However, your code cannot access any other columns after it has read the NText value. Once you move to the next row of the recordset you're OK again.

Best solution is to change your connection string though, and use something more modern. That solves the problem in the best way!