INF: xp_enum_oledb_providers Enumerates the OLE DB Providers

ID: Q216575

The information in this article applies to:

SUMMARY

xp_enum_oledb_providers is a new extended stored procedure that enumerates all OLE DB providers installed on SQL Server computer.

Here is the syntax: 


Exec xp_enum_oledb_providers


MORE INFORMATION

xp_enum_oledb_providers returns three columns as follows:

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')<BR/>
drop proc sp_valid_oledb_provider<BR/>
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<BR/>
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 OLEDB 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 please refer to the following:

SQL 7.0 Books Online, topics: "sp_addlinkedserver", "OpenQuery", "OpenRowset in"

Additional query words: kbDSupport 


Keywords          : kbole kbSQLServ700 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: April 21, 1999