How to get a list of installed OLE DB providers?

Question

Microsoft Excel allows import of data from "Other Sources". One of the options is to use an OLE DB provider.

How to get a list of available OLE DB providers?

Simms

Nearly 7 years later and there's still no acceptable answer? – Louvain

Answer 1

If you have powershell available, just paste this into a powershell command prompt:

foreach ($provider in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator())
{
    $v = New-Object PSObject        
    for ($i = 0; $i -lt $provider.FieldCount; $i++) 
    {
        Add-Member -in $v NoteProperty $provider.GetName($i) $provider.GetValue($i)
    }
    $v
}

Credits and more advanced usage: http://dbadailystuff.com/list-all-ole-db-providers-in-powershell

Keene

Answer 2

I am answering my own question because this was harder to find that I expected. Google-fu could only answer part of my question; I needed to synthesize information from various blog entries and official documentation.

Below is VBScript you can copy/paste into a text file and run on Windows. You do not need local admin rights to run this version.

Depending on the size of your registry and speed on your CPU, it may take up to one minute to run. The result is a message box with text that can be copied to the clipboard with Ctrl+C.

Primary reference: https://sysmod.wordpress.com/2014/07/11/vbscript-to-list-installed-oledb-providers/

'List of installed OLEDB providers on local computer
Option Explicit
Const HKEY_CLASSES_ROOT     = &H80000000
Const HKEY_CURRENT_USER     = &H80000001
Const HKEY_LOCAL_MACHINE    = &H80000002
Const HKEY_USERS        = &H80000003
Const HKEY_CURRENT_CONFIG   = &H80000005

Dim OutText, strComputer, objRegistry
Dim num
Dim ProgIdDict

strComputer = "."
Set objRegistry = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
OutText = "Note: Strike Ctrl+C to copy full text to clipboard"
Num = 1
Set ProgIdDict = CreateObject("Scripting.Dictionary")

' I discovered these registrations can appear in three different places.
' Use ProgIdDict to prevent dupes in the output
Append objRegistry, HKEY_CLASSES_ROOT, "HKEY_CLASSES_ROOT", "CLSID", ProgIdDict, Num, OutText
Append objRegistry, HKEY_LOCAL_MACHINE, "HKEY_LOCAL_MACHINE", "SOFTWARE\Classes\CLSID", ProgIdDict, Num, OutText
Append objRegistry, HKEY_LOCAL_MACHINE, "HKEY_LOCAL_MACHINE", "SOFTWARE\Classes\Wow6432Node\CLSID", ProgIdDict, Num, OutText

Sub Append(ByVal objRegistry, ByVal HKEYConstant, ByVal HKEYConstantStr, ByVal KeyPrefixStr, ByVal ProgIdDict, ByRef Num, ByRef OutText)

    Dim Key, arrKeys
    Dim strKeyPath, strValue, uValue

    objRegistry.enumKey HKEYConstant, KeyPrefixStr, arrKeys

    for each key in arrKeys

        strKeyPath = KeyPrefixStr & "\" & key

        ' if key exists...
        ' I noticed something weird where non-MSOLAP entries use the first style,
        ' and MSOLAP entries use the second style.
        If 0 = objRegistry.GetDWordValue(HKEYConstant, strKeyPath, "OLEDB_SERVICES", uValue) _
        Or 0 = objRegistry.GetDWordValue(HKEYConstant, strKeyPath & "\OLEDB_SERVICES", "", uValue) _
        Then
            objRegistry.GetStringValue HKEYConstant,strKeyPath & "\ProgID","",strValue
            If Not ProgIdDict.Exists(strValue) _
            Then
                ProgIdDict.Add strValue, strValue
                OutText=OutText & vbcrlf & vbcrlf
                'get the (Default) value which is the name of the provider
                objRegistry.GetStringValue HKEYConstant,strKeyPath,"",strValue
                OutText=OutText & num & ") " & strValue & vbcrlf & "Key: \\" & HKEYConstantStr & "\" & KeyPrefixStr & "\" & key
                ' and the expanded description
                objRegistry.GetStringValue HKEYConstant,strKeyPath & "\OLE DB Provider","",strValue
                OutText=OutText & vbcrlf & "OLE DB Provider: " & strValue
                objRegistry.GetStringValue HKEYConstant,strKeyPath & "\ProgID","",strValue
                OutText=OutText & vbcrlf & "ProgID: " & strValue
                objRegistry.GetStringValue HKEYConstant,strKeyPath & "\VersionIndependentProgID","",strValue
                OutText=OutText & vbcrlf & "VersionIndependentProgID: " & strValue
                num = 1 + num
            End If
        end if
    next

End Sub

Wscript.Echo OutText

Simms

Answer 3

Another solution using PowerShell, this time leveraging .NET code (credit to jrich523.wordpress.com).

Plug this into a PowerShell console:

(New-Object system.data.oledb.oledbenumerator).GetElements()

Produces output like this:

SOURCES_NAME        : SQLOLEDB Enumerator
SOURCES_PARSENAME   : {DFA22B8E-E68D-11d0-97E4-00C04FC2AD98}
SOURCES_DESCRIPTION : Microsoft OLE DB Enumerator for SQL Server
SOURCES_TYPE        : 2
SOURCES_ISPARENT    : False
SOURCES_CLSID       : {DFA22B8E-E68D-11d0-97E4-00C04FC2AD98}

Injector

It'd be better style to write it as (New-Object System.Data.OleDb.OleDbEnumerator).GetElements() ... – Greenhouse

Also this won't list 32-bit providers from a 64-bit Powershell prompt. – Emogeneemollient

Answer 4

In Windows Explorer:

1. Create a text file anywhere. e.g. temp.txt.
2. Change the extension to "udl". E.g. temp.udl.
3. Double click temp.udl.
4. Go to the [Provider] Tab. Observe the list of "OLE

Sagitta

Answer 5

OLEDB provides a class that will enumerate all OLE DB providers for you.

Microsoft OLE DB Root Enumerator^🕗

• ProgID: "MSDAENUM"
• clsid: {c8b522d0-5cf3-11ce-ade5-00aa0044773d}
• CLSID_OLEDB_ENUMERATOR (from msdaguid.h in the sdk)

The steps

Create the enumerator

ISourcesRowset enum = (ISourcesRowset)CreateComObject(CLSID_OLEDB_ENUMERATOR);

Get the results as an OLEDB IRowset:

IRowset rowset = enum.GetSourcesRowset(null, IRowset, 0, null);

The IRowset interface, like the rest of OLEDB is...merciless. It's a nightmare of API, dreamed up in a time when Java was all the rage. Fortunately Microsoft created a friendly wrapper around OLEDB: called ActiveX Data Object (ADO). It even provides a handy function to wrap an OLEDB IRowset into an ADO Recordset (adapter pattern):

Recordset rs = (Recordset)CreateComObject(CLASS_Recordset);
(rs as ADORecordsetConstruction).Rowset = rowset;

Now you can iterate the results:

SOURCES_NAME                SOURCES_PARSENAME                       SOURCES_DESCRIPTION                                   SOURCES_TYPE  SOURCES_ISPARENT  SOURCES_CLSID
--------------------------  --------------------------------------  ----------------------------------------------------  ------------  ----------------  --------------------------------------
SQLOLEDB                    {0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}  Microsoft OLE DB Provider for SQL Server              1             False             {0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}
MSOLAP                      {10154F28-4979-4166-B114-3E7A7926C747}  Microsoft OLE DB Provider for Analysis Services 10.0  1             False             {10154F28-4979-4166-B114-3E7A7926C747}
MSOLAP                      {10154F28-4979-4166-B114-3E7A7926C747}  Microsoft OLE DB Provider for Analysis Services 10.0  3             False             {10154F28-4979-4166-B114-3E7A7926C747}
MSDataShape                 {3449A1C8-C56C-11D0-AD72-00C04FC29863}  MSDataShape                                           1             False             {3449A1C8-C56C-11D0-AD72-00C04FC29863}
SQLNCLI11                   {397C2819-8272-4532-AD3A-FB5E43BEAA39}  SQL Server Native Client 11.0                         1             False             {397C2819-8272-4532-AD3A-FB5E43BEAA39}
ADsDSOObject                {549365d0-ec26-11cf-8310-00aa00b505db}  OLE DB Provider for Microsoft Directory Services      1             False             {549365d0-ec26-11cf-8310-00aa00b505db}
MSOLEDBSQL                  {5A23DE84-1D7B-4A16-8DED-B29C09CB648D}  Microsoft OLE DB Driver for SQL Server                1             False             {5A23DE84-1D7B-4A16-8DED-B29C09CB648D}
MSOLEDBSQL Enumerator       {720818D5-1465-4812-839F-9F15C38A52CB}  Microsoft OLE DB Driver for SQL Server Enumerator     2             False             {720818D5-1465-4812-839F-9F15C38A52CB}
SQLNCLI11 Enumerator        {8F612DD2-7E28-424f-A2FD-C2EECC314AA2}  SQL Server Native Client 11.0 Enumerator              2             False             {8F612DD2-7E28-424f-A2FD-C2EECC314AA2}
Windows Search Data Source  {9E175B8B-F52A-11D8-B9A5-505054503030}  Microsoft OLE DB Provider for Search                  1             False             {9E175B8B-F52A-11D8-B9A5-505054503030}
MSDASQL                     {c8b522cb-5cf3-11ce-ade5-00aa0044773d}  Microsoft OLE DB Provider for ODBC Drivers            1             False             {c8b522cb-5cf3-11ce-ade5-00aa0044773d}
MSDASQL Enumerator          {c8b522cd-5cf3-11ce-ade5-00aa0044773d}  Microsoft OLE DB Enumerator for ODBC Drivers          2             False             {c8b522cd-5cf3-11ce-ade5-00aa0044773d}
MSOLAP                      {DBC724B0-DD86-4772-BB5A-FCC6CAB2FC1A}  Microsoft OLE DB Provider for Analysis Services 14.0  1             False             {DBC724B0-DD86-4772-BB5A-FCC6CAB2FC1A}
MSOLAP                      {DBC724B0-DD86-4772-BB5A-FCC6CAB2FC1A}  Microsoft OLE DB Provider for Analysis Services 14.0  3             False             {DBC724B0-DD86-4772-BB5A-FCC6CAB2FC1A}
Microsoft.Jet.OLEDB.4.0     {dee35070-506b-11cf-b1aa-00aa00b8de95}  Microsoft Jet 4.0 OLE DB Provider                     1             False             {dee35070-506b-11cf-b1aa-00aa00b8de95}
SQLOLEDB Enumerator         {DFA22B8E-E68D-11d0-97E4-00C04FC2AD98}  Microsoft OLE DB Enumerator for SQL Server            2             False             {DFA22B8E-E68D-11d0-97E4-00C04FC2AD98}
MSDAOSP                     {dfc8bdc0-e378-11d0-9b30-0080c7e9fe95}  Microsoft OLE DB Simple Provider                      1             False             {dfc8bdc0-e378-11d0-9b30-0080c7e9fe95}
MSDAORA                     {e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}  Microsoft OLE DB Provider for Oracle                  1             False             {e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}

Microsoft documents the columns in ISourcesRowset::GetSourcesRowset ^archive

• SOURCES_NAME (0): The invariant name of the data store or enumerator.

• SOURCES_PARSENAME (1): String to pass to IParseDisplayName to obtain a moniker for the data source object or enumerator.

• SOURCES_DESCRIPTION (2): Description of the OLE DB data source object or enumerator.

• SOURCES_TYPE (3): Specifies whether the row describes a data source object or an enumerator:

  • DBSOURCETYPE_DATASOURCE_TDP (1): Indicating a tabular data provider
  • DBSOURCETYPE_ENUMERATOR (2): Indicating an enumerator
  • DBSOURCETYPE_DATASOURCE_MDP (3): Indicating a multidimensional (OLAP) provider
  • DBSOURCETYPE_BINDER (4): Indicating a provider binder that supports direct URL binder If a single piece of code is capable of being used both as a data source object and as an enumerator, it is listed in the rowset twice, once in each role.

• SOURCES_ISPARENT (4): If the row describes an enumerator, SOURCES_ISPARENT is VARIANT_TRUE if the enumerator is the parent enumerator; that is, the enumerator whose enumeration contains the enumerator on which ISourcesRowset::GetSourcesRowset was just called. This allows the consumer to go backward through the enumeration. Whether an enumerator is able to enumerate its parent is provider-specific. Otherwise, SOURCES_ISPARENT is VARIANT_FALSE.

  If the row describes a data source object, SOURCES_ISPARENT is ignored by the consumer.

Esoteric

Answer 6

I had an issue where Steinar Herland's powershell script wasn't finding my oledb provider. I wrote a little C# program that did though.

    using System.Data.OleDb;    
    var oleEnum = new OleDbEnumerator();
    var elems = oleEnum.GetElements();
    if (elems != null && elems.Rows != null)
       foreach (System.Data.DataRow row in elems.Rows)
          if (!row.IsNull("SOURCES_NAME") && row["SOURCES_NAME"] is string)
             Console.WriteLine(row["SOURCES_NAME"]);

Hootman

Answer 7

If you have MS Excel installed, why go thru all the hassle of all these scripts/powershell? You can use Excel Import Data from Other Source and it will display all installed OLEDB Data Sources.

Scornful

Note that if you're trying to figure out if you have a 64-bit provider, but your version of Office is running in 32-bit, this won't help. (And vice versa -- finding a 32-bit provider with a 64-bit version of Office.) – Debouchment

Answer 8

I edited the code by @Steinar Herland

If you want only to display the ACCESS OLEDB Providers, just paste this into a powershell command prompt:

foreach ($provider in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator())
{
    if ($provider.GetValue(0) -like "Microsoft.ACE*")
    {
        $v = New-Object PSObject
        Add-Member -in $v NoteProperty "Provider Name" $provider.GetValue(0)
        Add-Member -in $v NoteProperty "Description" $provider.GetValue(2)
        $v
    }
}

Devy