Share via

SQL Server 2019 localdb with ADO and SQLOLEDB Driver

aMicrosoftUser-2990 1 Reputation point
2021-12-17T10:22:19.943+00:00

Hi,
I tried to get SQL Server 2019 localDB to run, but failed.
I Installed SQL Server 2019 localDB from Setup.
I am using ADO to connect.

First thing what bothered me is that the setup does not not install the necessary driver.
So I downloaded and installed SQLOLEDB Driver for SQL Server.
I tried a connection string like this:
"Provider=SQLOLEDB;Data Source=(localdb)\MSSQLLocalDB;Integrated Security=true"
or
"Provider=SQLOLEDB;Server=(localdb)\MSSQLLocalDB;Integrated Security=true"
I get this error:
ADO Error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (0x80040e21) (HRESULT: 3617)

Then I installed deprecated SQL Native Client.
And it worked like a charm with this connection string:
"Provider=SQLNCLI11;Data Source=(localDB)\MSSQLLocalDB;Integrated Security=SSPI;"

Question is:
is there a way to get SQL Server localDB running via ADO with SQLOLEDB (since SQLNCLI is deprecated)?
It should ... since I can connect to an SQL Express Instance with it.

Thanks and best regards

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 43,246 Reputation points
    2021-12-17T11:00:48.197+00:00

    Then I installed deprecated SQL Native Client.

    You use SQLOLEDB, that's also an old and deprecated data provider, which don't support new features like LocalDB.
    Use ODBC or MSOLEDBSQL instead, see Driver history for Microsoft SQL Server

    0 comments
  2. Dan Guzman 9,231 Reputation points
    2021-12-17T12:24:54.87+00:00

    So I downloaded and installed SQLOLEDB Driver for SQL Server.
    I tried a connection string like this:
    "Provider=SQLOLEDB;Data Source=(localdb)\MSSQLLocalDB;Integrated Security=true"

    In addition to installing the new OLE DB driver, you need to change your connection string to specify the new provider MSOLEDBSQL: 

    "Provider=MSOLEDBSQL;Data Source=(localdb)\MSSQLLocalDB;Integrated Security=true"
    0 comments
  3. YufeiShao-msft 7,091 Reputation points
    2021-12-20T06:41:43.473+00:00

    Hi @aMicrosoftUser-2990 ,

    Starting in SQL Server 2005, the SQL Server Native Client includes an OLE DB provider interface (SQLNCLI) and is the OLE DB provider that shipped with SQL Server 2005 through SQL Server 2017. It was announced as deprecated in 2011 and it isn't recommended to use this driver for new development. In 2017, OLE DB data access technology was later undeprecated and a new planned release was announced for 2018. The new OLE DB provider is called the "Microsoft OLE DB Driver for SQL Server" (MSOLEDBSQL) and is currently maintained and supported.

    Refer to this doc:

    For example, for a trusted connection using SQL Native Client (SNAC11), plan to convert from:

    TABLE 1
    Provider=SQLNCLI11; Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=yes;

    to:

    TABLE 2
    Provider=MSOLEDBSQL; Server=myServerName\theInstanceName; Database=myDataBase;Trusted_Connection=yes;

    For a trusted connection using the Microsoft OLE DB Provider for SQL Server, plan to convert from:

    TABLE 3
    Provider=sqloledb; Data Source=myServerName\theInstanceName;Initial Catalog=myDataBase; Integrated Security=SSPI;

    to:

    TABLE 4
    Provider=MSOLEDBSQL; Server=myServerName\theInstanceName; Database=myDataBase;Trusted_Connection=yes;

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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.

    0 comments