INF: xp_enum_oledb_providers Enumerates the OLE DB Providers

This article was previously published under Q216575
This article has been archived. It is offered "as is" and will no longer be updated.
xp_enum_oledb_providers is a new extended stored procedure that enumerates all OLE DB providers installed on a SQL Server.

The following is the syntax for xp_enum_oledb_providers:
EXEC xp_enum_oledb_providers
xp_enum_oledb_providers returns three columns as follows:
  • ProviderName: Default value of CLSID key.
  • Parsename: CLSID (GUID).
  • Description: Friendly name of the provider.
Using this procedure, you can write another script or procedure to check if a specific provider is installed on a computer.

-- sp_valid_oledb_provider-- A test to see if you pass in a legal provider name.use mastergoif exists (select * from sysobjects where type = 'P' and name = 'sp_valid_oledb_provider')<BR/>drop proc sp_valid_oledb_provider<BR/>gocreate proc sp_valid_oledb_provider @p nvarchar(255) asset nocount oncreate table #t([Provider Name] nvarchar(255) not null,[Parse Name] nvarchar(255) not null,[Provider Description] nvarchar(255) not null)insert into #t exec xp_enum_oledb_providersif exists (select * from #t where [Provider Name] = @p)begin   -- print 'OK'   return 1endelsebegin   raiserror('Invalid OLE-DB provider "%s"',-1, -1, @p)   return 0<BR/>endgoexec sp_valid_oledb_provider N'SQLOLEDB'exec sp_valid_oledb_provider N'MSDASQL'exec sp_valid_oledb_provider N'Microsoft.Jet.OLEDB.4.0'exec sp_valid_oledb_provider N'Illegal Provider'

This procedure can be used to find if a specific OLE DB provider is installed before setting up a SQL Server 7.0 distributed query with sp_addlinkedserver.


For more details on setting up and using distributed queries using OLE DB providers, refer to the "sp_addlinkedserver", "OpenQuery", and "OpenRowset in" topics in SQL Server 7.0 Books Online.

For additional information, see the Support WebCast at the following location:
kbDSupport oledb proc xproc st sproc sp xp

Article ID: 216575 - Last Review: 01/16/2015 19:27:05 - Revision: 3.3

Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbinfo KB216575