Error "An OLE DB Provider was not specified in the ConnectionString...' when converting a DSN-less connection from ODBC to OLEDB

[Suskil]

Issue

The following error occurs when changing an Input Data tool's DSN-less connection string, to use OLEDB instead of ODBC.

 

Connection Error: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'

1. Paste current working DSN-less connection string into Input Data Tool, e.g:  odbc:DRIVER={SQL Server Native Client 11};DATABASE=dbname;SERVER=servername;Trusted_Connection=yes
2. Replace odbc with odb
3. Click on the ellipsis in the table or query field, results in the following error

Environment Details

• Alteryx Designer
  • Any.
• SQL Server
  • any.
• SQL Server Native Client 11

 

Cause

 

OLEDB requires a Provider to be specified. In this particular case 'SQLNCLI11', which is the OLEDB provider associated with SQL Server Native Client 11

Resolution

Replace

odbc:DRIVER={SQL Server Native Client 11};DATABASE=dbname;SERVER=servername;Trusted_Connection=yes

with

odb:PROVIDER=SQLNCLI11;DATABASE=dbname;SERVER=servername;Trusted_Connection=yes

For information on DSN-Less formats for multiple database platforms, see additional resources below.

 

Additional Resources

• The Connection Strings Reference