The short answer is: Those returned by master.dbo.xp_enum_oledb_providers.
But let’s dig deeper and try to give you the detailed description you deserve.
The triggering reason why I’m writing about this is because a colleague described the following situation in one of our internal lists:
I designed a OLE DB Provider, and want to register it in the sql server, then I can use the linked server to access the data source.
But I can’t find the Provider after register.
What I did was:
- Build the DLL for the ole db provider
- Register the DLL by call “regsvr32 ..\xx.dll”
- Screenshot after the provider was registered.
- Restart the Sql Server
- Check the provider of the Sql Server
I can’t find the ole db provider registered by me.
Someone can tell me what is the problem, is there something missing when register the provider.
So, I investigated what SSMS did to collect the information it was showing under the Providers node and this is what I’ve found:
SSMS GUI’s invokes master.dbo.xp_enum_oledb_providers on the instance of SQL Server it is connected to, and with the rowset returned by that extended stored procedure is with what it builds the list of items hanging from the Server Objects -> Linked Servers -> Providers node.
Now, what that extended stored procedure does is use the OLEDB root enumerator object, from the rowset returned by ISourcesRowset::GetSourcesRowset it fetches the first 50 rows (this value of 50 is hardcoded in SQL Server’s xp_enum_oledb_providers code), and excludes from that list those SQL Server deliberately wants to exclude because are not supported (being those providers whose names match any in the following list "DTSFlatFile;MS Remote;MSDataShape;MSPersist;Microsoft.Jet.OLEDB.3.51;SampProv;CustTran").
Having explained the way it works, the things that could be preventing an OLEDB provider to show up in that list are these:
- your OLEDB provider is built for a bitness (processor architecture) that doesn’t match that of your instance of SQL Server (for example, your instance of SQL is x64 and your registered provider is built for x86),
- you have more than 50 providers registered in your system, and given the way the root enumerator traverses the Registry, your provider is beyond the 50th position, or
- the name of your provider matches one of those in the list above.
My experience is that odds are higher for option 1 to be the one causing your provider to not show up.