Classic ASP SQLNCLI connection string returns empty recordsets that worked fine with SQLOLEDB

Question

Trying to switch to SQLNCLI so I can use varchar(max) fields in my ASP application. No records are returned from a query that worked fine under SQLOLEDB.

The connection string opens as follows:

ConnStr="Provider=SQLNCLI10;Server=129.118.139.78,8888;Server=UWCTest;Uid=user;Pwd=pass;DataTypeCompatibility=80;MARS Connection=true;"
oConn.Open connStr

and the code to get the recordset is as follows:

sSQL="usp_tutors_active_select"

Set dbCommand = Server.CreateObject("ADODB.Command")    
Set dbCommand.ActiveConnection = oConn  
dbCommand.CommandType = adCmdStoredProc 
dbCommand.Commandtext=sSQL  


set oRST=dbCommand.Execute

Response.Write orst("firstname")
do while not datacommand1.EOF
    Response.Write "<option value=" & orst("firstname") & ">" & orst("firstname")

    orst.MoveNext
loop

Answer 1

Try adding SET NO COUNT ON to the top of usp_tutors_active_select. I seem to remember having this issue before.

Answer 2

Turns out I needed to install the SQLCNI provider on the server; the actual error that was masked by the apparently empty recordset was actually a "provider not found."