sp_MSset_oledb_prop (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSset_oledb_prop(nvarchar @provider_name
, nvarchar @property_name
, bit @property_value)

MetaData:

   
create procedure sys.sp_MSset_oledb_prop  
    @provider_name as sysname = NULL,  
    @property_name as sysname = NULL,  
    @property_value as bit = NULL  
AS  
set nocount on  
  
IF (not is_srvrolemember(N'setupadmin') = 1)  
begin  
    raiserror(15003,-1,-1, N'setupadmin')  
    return (1)  
end  
  
create table #oledbprop (provider_name sysname null, allow_in_process bit, disallow_adhoc_access bit, dynamic_parameters bit, index_as_access_path bit,  
                level_zero_only bit, nested_queries bit, non_transacted_updates bit, sql_server_like bit)   
  
create table #param_list(property_name sysname, property_value int)  
  
create table #providers (name nvarchar(100), guid nvarchar(100) NULL, description nvarchar(100) NULL)  
if @provider_name is null   
begin  
    insert into #providers exec sys.sp_enum_oledb_providers  
end  
else  
begin  
    insert into #providers (name) VALUES ( @provider_name )  
end  
  
declare @regpath nvarchar(512)  
set @regpath = N'SOFTWARE\Microsoft\MSSQLServer\Providers\' + @provider_name  
  
  
insert #param_list(property_name) VALUES ('AllowInProcess')  
insert #param_list(property_name) VALUES ('DisallowAdHocAccess')  
insert #param_list(property_name) VALUES ('DynamicParameters')  
insert #param_list(property_name) VALUES ('IndexAsAccessPath')  
insert #param_list(property_name) VALUES ('LevelZeroOnly')  
insert #param_list(property_name) VALUES ('NestedQueries')  
insert #param_list(property_name) VALUES ('NonTransactedUpdates')  
insert #param_list(property_name) VALUES ('SqlServerLIKE')  
  
if (@property_name is null)  
begin  
    declare @value int  
    declare @sql nvarchar(300)  
    declare @param nvarchar(300)  
    set @sql = 'exec sys.xp_instance_regread N''HKEY_LOCAL_MACHINE'', @regpath, @property_name, @value OUTPUT, @no_output = N''no_output'' ' +  
        'update #param_list set property_value = IsNull(@value, 0) where property_name = @property_name'  
    set @param = '@regpath nvarchar(512), @property_name sysname, @value int'  
  
    declare c_prov cursor local fast_forward  
        for ( select name from #providers )  
    open c_prov  
    fetch next from c_prov into @provider_name  
    while @@fetch_status = 0  
    begin  
        set @regpath = N'SOFTWARE\Microsoft\MSSQLServer\Providers\' + @provider_name  
  
        declare c cursor local fast_forward  
            for ( select property_name from #param_list )  
        open c  
        fetch next from c into @property_name  
        while @@fetch_status = 0  
        begin  
            exec sp_executesql @sql, @param, @regpath, @property_name, @value  
            fetch next from c into @property_name  
        end  
    close c  
    deallocate c  
      
    insert #oledbprop (provider_name, allow_in_process , disallow_adhoc_access , dynamic_parameters , index_as_access_path , level_zero_only , nested_queries , non_transacted_updates , sql_server_like)  
        select @provider_name, AllowInProcess, DisallowAdHocAccess, DynamicParameters, IndexAsAccessPath, LevelZeroOnly, NestedQueries, NonTransactedUpdates, SqlServerLIKE   
        from #param_list pivot ( max(property_value) for property_name in ( [AllowInProcess], [DisallowAdHocAccess], [DynamicParameters], [IndexAsAccessPath], [LevelZeroOnly], [NestedQueries], [NonTransactedUpdates], [SqlServerLIKE] ) ) as p  
  
    fetch next from c_prov into @provider_name  
    end  
    close c_prov  
    deallocate c_prov  
  
    select * from #oledbprop  
end  
else  
begin  
    --  check if this is a known property  
    if @property_name not in ( select property_name from #param_list )  
    begin  
    --  A message need to be added to system messages and name should also be given ('Unknown property specified: %s.')  
        --  localize message without changing message number  --   
           declare @errtxt nvarchar(1024)  
        select @errtxt=text from sys.messages where message_id=29004  
           raiserror (@errtxt, 16, 1, @property_name)  
           return  
    end  
  
    if 1 = @property_value  
    begin  
        declare @val int  
        set @val = @property_value  
        exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', @regpath, @property_name, REG_DWORD, @val  
    end  
    else  
    begin  
        exec xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', @regpath, @property_name  
    end  
end  
  
drop table #oledbprop  
drop table #providers  
drop table #param_list