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
No comments:
Post a Comment