Hi,
I came across one interesting thing that I got a drastic performance improvement in my classic ASP application, that is using SQL Server 2005 as DBMS, just by changing the ODBC driver.

Earlier my query string was something like:
StrConnect="Driver={SQL Server}; Server=ServerName; Database=DBName; Uid=UserID; Pwd=Password;"

I just changed the query string to :
StrConnect="Driver={SQL Native Client}; Server=ServerName; Database=DBName; Uid=UserID; Pwd=Password;"

And the performance got improved drastically.

However, I have some problems with {SQL Native Client} driver. It's not returning VARCHAR(MAX ) datatype fields.

I know I can use : StrConnect="Provider = SQLOLEDB;Data Source = ServerName;Initial Catalog = DBName;User ID = userID;Password = Password ;DataTypeCompatibility=80"

But with this OLE DB provider the major problem is, it is not returning correct value for Recordset.RecordCount property. It always return -1. And that is not acceptable, as I am using Recordset.Recordcount at so many places in my ASP pages. I can Set my Recordset.CursorLocation = 3 (Client - side) to make it work. But I do not want client-side cursor.

I just want to use {SQL Native Client} driver.
StrConnect="Driver={SQL Native Client}; Server=ServerName; Database=DBName; Uid=UserID; Pwd=Password;"
And that should work properly with VARCHAR(MAX ) datatype.

Is this drawback has been fixed in {SQL Native client 10.0} ??

Appreciate your help.