Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSset_oledb_prop

  No additional text.


Syntax

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

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash