May 29, 2012

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

No comments:

Post a Comment

Total Pageviews