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

sakuraime 2,316 Reputation points
2020-10-07T10:54:06.653+00:00
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 ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,238 questions
{count} votes

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 38,786 Reputation points
    2020-10-07T11:24:32.663+00:00

    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.

    30628-image.png

    1 person found this answer helpful.

  2. CathyJi-MSFT 21,106 Reputation points Microsoft Vendor
    2020-10-08T09:25:53.863+00:00

    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.