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.
SUMMARY
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
MORE INFORMATION
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.

REFERENCES

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
Properties

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
Feedback