Microsoft KB Archive/216575

= INF: xp_enum_oledb_providers Enumerates the OLE DB Providers =

Article ID: 216575

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q216575



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 master go if exists (select * from sysobjects where type = 'P' and name = 'sp_valid_oledb_provider') drop proc sp_valid_oledb_provider go create proc sp_valid_oledb_provider @p nvarchar(255) as set nocount on create 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_providers if exists (select * from #t where [Provider Name] = @p) begin -- print 'OK' return 1 end else begin raiserror('Invalid OLE-DB provider "%s"',-1, -1, @p) return 0 end go exec 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.