OLEDB connection string to AG read-only replica Application Intent read only

Question

using System;
using System.Data;
using System.Data.OleDb;

class Program
{
static void Main()
{
string str = "Provider=SQLOLEDB;Data Source=myaglist;Initial Catalog=mytestdb;"
+ "Integrated Security=SSPI;application intent=readonly";
datadelete(str);
}

    private static void datadelete(string connectionString)
    {
        string queryString =
            "select * from testinging";

        using (OleDbConnection  connection =
                   new OleDbConnection (connectionString))
        {
            OleDbCommand command =
                new OleDbCommand (queryString, connection);
            connection.Open();
            command.ExecuteNonQuery();

        }
    }

}

I have the above .net project having the above code, but it seems no putting me to the secondary replica ..

How everver using sqlclient System.Data.SqlClient, the same code work .. of coz using applicationintent (without space)..

why ? Any idea ?

Answer 1

You're using the wrong OleDb Provider. You're using SQLOLEDB. That's too old to support Read-Only Routing. You need the new MSOLEDBSQL aka "Microsoft OLE DB Driver for SQL Server".

This driver has support for Application Intent documented here: Specifying Application Intent

Answer 2

Why

Because OleDB can target any RDBMS and so supports only the common connection string properties; it's not aware what ApplicationIntent means.
SqlClient is specific for SQL Server and that provider is aware of it and supports it.
You simply can test it using OleDbConnectionStingBuilder and the same for SQL.

Answer 3

Hi @sakuraime ,

Legacy SQL OLE DB and ODBC providers do not support these connection strings. Be sure that the legacy SQL OLE DB provider or the legacy ODBC driver is not specified when connecting. If it is, the application intent property will be ignored and the connection will be directed to the primary.

Please refer to the MS blog Connect to SQL Server Using Application Intent Read-Only to get more details information.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.