Why am I getting Data provider or other service returned an E_FAIL status? SQL Native Client

Question

I'm switching our ASP code to use SQL Native Client so that we can connect to a mirrored database with a failover partner, since you can only supply the failover partner parameters in SQL Native Client. When I run a proc that returns an nvarchar(max) column with Driver={SQL Server} everything works fine. When I run procs that return small colums with Driver={SQL Server Native Client 10.0} that works fine. It's only when I try to run a proc that returns an nvarchar(max) column while using Driver={SQL Server Native Client 10.0}; that I get the error. The error happens as soon as we hit

rs.Open cmdTemplate

So I'm not even referring to the column. Setting the conn string like this:

if bUseSQLNative then
        connString = "Driver={SQL Server Native Client 10.0}; Network=DBMSSOCN; server=" & rs("SERVER_NAME") & "," & rs("PORT_NUM") & ";database=" & rs("DATABASE_NAME")
        connString = connString & ";uid=" & rs("USER_NAME") & ";pwd=" & UnProtectValueEx(ConnSaltForDBPwd(), rs("CONNECTION_NAME"), rs("PASSWORD"))
    else
        connString = "Driver={SQL Server}; Network=DBMSSOCN; server=" & rs("SERVER_NAME") & "," & rs("PORT_NUM") & ";database=" & rs("DATABASE_NAME")
        connString = connString & ";uid=" & rs("USER_NAME") & ";password=" & UnProtectValueEx(ConnSaltForDBPwd(), rs("CONNECTION_NAME"), rs("PASSWORD"))
    end if 
    connString = connString & ";"

And opening like this:

set rs = server.CreateObject("ADODB.RecordSet")
rs.CursorLocation = 3
rs.CursorType = 3
rs.CacheSize = 50

on error resume next
    rs.Open cmdTemplate

The error is: Microsoft Cursor Engine (0x800A0001) Data provider or other service returned an E_FAIL status.

http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/ac06f814-1814-4861-b2cd-8d5e507a7ee3/ - possibly related? But that was input params. This is just selecting a column that is nvarchar(max). — Brian White, May 01 '12 at 21:24
That is definitely related. If I add cast(SEND_TEXT as ntext) [SEND_TEXT] into the stored proc then it works without error. So it's something with ADO data types, and ntext and nvarchar(max) looking different? — Brian White, May 01 '12 at 21:30
I found others reporting this problem as well. It's not unique to ASP classic. It appears to be either a provider or driver limitation that also affects Visual Foxpro. http://www.bsslimited.com/articles/classic-asp-and-sql-server-2008/ and http://connect.microsoft.com/SQLServer/feedback/details/467300/problem-with-varchar-max-via-sql-native-client — HK1, May 01 '12 at 23:41

score 2 · Answer 1 · answered Jan 11 '13 at 23:37

2

In my case, the data to be saved (string) was larger than the specified nvarchr(). Increasing the field size solved the problem

answered Jan 11 '13 at 23:37

user1971483

21
2

score 1 · Accepted Answer · answered May 02 '12 at 16:19

I found it. I had to use

connString = "Provider=SQLNCLI10; DataTypeCompatibility=80;...

The DataTypeCompatibility makes the nvarchar max etc map back to formats ado can handle. And for some reason that parameter doesn't take effect with Driver={SQL Server Native Client 10.0};, only with Provider=SQLNCLI10

score 0 · Answer 3 · edited Nov 27 '13 at 10:24

Use the reference MSADO 6.1 Library and then construct a data environment to connect to the database and establish the recordset to be used like this:

 mydata = is a data environment with the connection to the database
 getItemRec = is the query or command inside the mydata
 myRecSet = is a Recordset Variable.

Do the code like this:

 myData.Commands("getItemRec").CommandText = "Select * from myTable"
 myData.getItemRec

 Set myRecSet = myData.rsgetItemRec

 With myRecSet       
      If .RecordCount <> 0 Then .MoveNext
      Do While .EOF = False
            ....
            ....
          .movenext
      Loop
 end With

Hope this will Help.

Why am I getting Data provider or other service returned an E_FAIL status? SQL Native Client

3 Answers3