Query LinkedServer and Provider Information

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 
Advertisements

Recovering unsaved or modified scripts

Let’s face it we have all spent countless hours developing and/or polishing up our sql scripts deep into the wee hours of the night to the point of near exhaustion. We rise early the next morning (or a few hours later in most cases), pour a cup of coffee and head over to our laptop to find that our system rebooted. Then reality sinks in and you suddenly realize that you didn’t save anything before calling it a night. Now you’re thinking “[enter swear phrase of choice here]”!

Don’t panic…

Typically when you re-open SQL Server Management Studio you’re prompted with a nice little screen that politely asks if you would like to recover the selected files or queries. Which is extremely helpful, but what happens when you don’t get that prompt, what then?

The answer is easy enough and may require you to change your folder options to show hidden files and folders. In the event you find yourself in a similar situation simply navigate to (assuming you are running Windows 7) C:\Users\”[your username goes here]”\AppData\Local\Temp\ and look for files similarly named like the ones illustrated below.

Pass Summit 2011… A First Timers Experience

Words really cannot describe the experience. The SQL Server community is an amazing community to be a part of. The camaraderie among peers is undoubtedly extraordinary. From what I recall there were over 500 first timers this year which just proves the PASS community is growing at a great rate. I have only been involved with PASS for a few years now and have become the V.P. for the Arizona Pass Chapter, a SQLSaturday Phoenix Organizer and now a volunteer/presenter scheduler for the Performance Pass Virtual Chapter. So if you have any questions about Pass feel free to drop me a line, I would love to chat with you about it.

Back to the summit…

The Pass Summit is technically a three-day fun-filled event from Wed – Fri but there were pre-conference seminars (precons) that occurred on Monday and Tuesday which I did not attend. From what I hear the (precons) were amazing. Hopefully next year I can be fortunate enough to attend those as well. I only attended the Wed – Fri sessions. My main focus was on performance but there were many other tracks to choose from. The ever so popular business intelligence to administration to development to professional development. Definitely something for everyone.

The training is just one part of the conference but the relationships you build from networking is priceless. I finally met so many people whom I have literally known for several years for the first time. I know that sounds odd, but the power of social networking just brings people from all walks of life together. The best part is that you feel like you’ve been friends forever even though this is the first time meeting one another. That’s the energy of the SQL Community and I am proud to be a part of it. I learned a lot and have so many thoughts and scenarios running through my mind that I need to organize them into actionable items and prepare to blog about them. There’s so much more I can say about the benefits of attending the Pass Summit, but take it from me (a first timer) that it’s well worth it and you’ll never forget it. Hope to see and/or talk with you soon at a local, national or international event!

Just in case you wanted to see some the photos taken at this years and past Pass Summit events… Pass Summit 2011 and Pass Summit Past Events.