ayesamson

First off I modified the (master.dbo.sp_MSset_oledb_prop) stored procedure just a bit and incorporated my own query to get the results I needed. Which you can find from lines 78 to 104 of the query at the bottom of this post. I cannot take all the credit, I received a lot of help from the sql community. Thanks again all, I appreciate it. Also note I am using SQL Server 2008 R2 Developer Edition.

You’ll notice I changed most of the temp tables to table variables with the exception of the #paramlist. I suspect the table variable loses it declaration when using it within a cursor (not 100% positive on that). The oddity was that the results were still returned but I received the “Msg 1087, Level 15, State 2, Line 1 Must declare the table variable @paramlist” error repeatedly. So I changed it back to a temp table and now all is well.

The Thread

The point of this was to return linked server information as well as the provider properties, specifically Allow Inprocess. I blurred out some details to protect the innocent.

The Results

The Query

SET NOCOUNT ON
DECLARE @provider_name AS SYSNAME, @property_name AS SYSNAME, @property_value AS BIT

DECLARE @providers TABLE
(
	name NVARCHAR(100) PRIMARY KEY CLUSTERED
	,guid NVARCHAR(100) NULL
	,description NVARCHAR(100) NULL
) 

INSERT INTO @providers EXEC sys.sp_enum_oledb_providers  

CREATE TABLE #paramlist
(
	property_name SYSNAME PRIMARY KEY CLUSTERED
	,property_value INT
)  

INSERT INTO #paramlist (property_name) VALUES ('AllowInProcess')  
--INSERT INTO #paramlist (property_name)  VALUES ('DisallowAdHocAccess')  
--INSERT INTO #paramlist (property_name)  VALUES ('DynamicParameters')  
--INSERT INTO #paramlist (property_name)  VALUES ('IndexAsAccessPath')  
--INSERT INTO #paramlist (property_name)  VALUES ('LevelZeroOnly')  
--INSERT INTO #paramlist (property_name)  VALUES ('NestedQueries')  
--INSERT INTO #paramlist (property_name)  VALUES ('NonTransactedUpdates')  
--INSERT INTO #paramlist (property_name)  VALUES ('SqlServerLIKE')  

DECLARE @oledbprop TABLE
(
	provider_name SYSNAME PRIMARY KEY CLUSTERED
	,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
)

DECLARE @regpath NVARCHAR(512)  
SET @regpath = N'SOFTWARE\Microsoft\MSSQLServer\Providers\' + @provider_name  
  
DECLARE @value int, @sql NVARCHAR(300), @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 #paramlist 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 #paramlist )  
		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 #paramlist 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  

/* RETURN RESULTS */
	 SELECT 
		s.name 'linked server'
		/* ,s.data_source */
		,s.product
		,CASE
			WHEN s.catalog IS NULL THEN QUOTENAME(s.data_source)
			ELSE QUOTENAME(s.data_source) + '.' + QUOTENAME(s.catalog)
		END 'data source'
		,s.provider
		,p.[description]
		,CASE
			WHEN o.allow_in_process = 0 THEN 'No'
			ELSE 'Yes'
		END 'allow inprocess'
		,CASE
			WHEN s.is_rpc_out_enabled = 0 THEN 'No'
			ELSE 'Yes'
		END 'rpc out'
		,l.remote_name
		,s.modify_date
	FROM master.sys.servers s
		JOIN master.sys.linked_logins l ON s.server_id = l.server_id
		JOIN @oledbprop o ON s.provider = o.provider_name
		JOIN @providers p ON o.provider_name = p.name
	WHERE (s.name != @@SERVERNAME)	
		ORDER BY s.name

/* HOUSE CLEANING */
DROP TABLE #paramlist