SQL Server text column affects results returned to classic ASP

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 on开发者_运维问答ly 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};).

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!

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

casting ntext to varchar will do the job.

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).

One extra tip for those who are working with older code:

When a recordset's column value is blank using ADO/ASP and you have a single line of data, you can bypass this problem by using a parameterised Command statement and returning the string value into a variable:

Some hand-typed code hopefully explains what I mean:

' DB connection
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.CursorLocation = adUseClient
objCon.Open pubDbConnString

' statement construction
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = objCon

cmd.CommandText = "SELECT ?=T.Column1, ?=T.Column2 From Table T WHERE ID=?"
cmd.CommandType = adCmdText
' add parameters
cmd.Parameters.Append cmd.CreateParameter("@column1Data", adVarChar, adParamOutput, 8000)
cmd.Parameters.Append cmd.CreateParameter("@column2Data", adTinyInt, adParamOutput)
cmd.Parameters.Append cmd.CreateParameter("@id", adBigInt, adParamInput)
cmd.Parameters("@id").value = 1

set objRS = cmd.Execute

@column1Data will contain the large string. objRS will actually not have any records in it, so be mindful of this.

In theory, this should also work with named parameters with the same results, but I have not tested this.