Deep in the Code

Since June, I have begun working in a new position that involves less in the way of application development, and more data analytics and database development. The primary tool I’ll be using is Microsoft Power BI, along with SQL Server, Amazon Redshift, and CRM Analytics (formerly called Tableau) for use with Salesforce.

Power BI is an amazing tool, but like any new or unfamiliar tool, there is a learning curve to be overcome. Though there is plenty of free training to take, there’s nothing that will teach you as much as getting your hands on the tool – and then researching the problems you encounter along the way.

Recently, I had to update a number of reports with a fairly simple update – add a new column to the dataset, and then to the reports. I was able to do this and could view the updated reports with no problem.

One of my customers looked at the report and said that the new column was not there. I assumed that the report had simply been cached in the browser, and needed to be refreshed. Not so. After digging around a bit, I found that I was looking directly at the report I’d updated, and the user was looking at the report through a published app. As it turns out, updates to the report and dataset do not show up in the app until the app is updated.

Once the app has been updated, any changes made will appear, though there may be a delay if the changes were major (such as adding a large quantity of data to a dataset).

As we are preparing to migrate some of our older SQL Server databases that are nearing their end of support to SQL Server 2019, one of the necessary tasks is determining what Linked Servers need to be established on the new servers to maintain the existing functionality for all of our users’ applications.

Testing the functionality of an individual Linked Server is simple enough – the built-in stored procedure sp_testlinkedserver will return a zero (0) if the link is good, and returns an exception if the link is not.

There is a stored procedure for listing all Linked Servers, but it doesn’t return the health of the link: sp_linkedservers.

How can these be combined to return only the Linked Servers that are currently up?

The solution I developed uses sp_linkedservers to populate a table variable, which is then used in a cursor. For each row in the table, using the sp_testlinkedserver stored proc, an active column is set to 1 if the server is up, and 0 if it is not.

DECLARE @LinkedServerName NVARCHAR(128), @Active BIT, @Message NVARCHAR(MAX)=''

DECLARE @Servers TABLE
(SRV_NAME NVARCHAR(128)
,SRV_PROVIDERNAME	NVARCHAR(128)
,SRV_PRODUCT	NVARCHAR(128)
,SRV_DATASOURCE	NVARCHAR(4000)
,SRV_PROVIDERSTRING	NVARCHAR(4000)
,SRV_LOCATION	NVARCHAR(4000)
,SRV_CAT	NVARCHAR(128)
,Active BIT DEFAULT 1)

INSERT INTO @Servers (SRV_NAME,SRV_PROVIDERNAME,SRV_PRODUCT,SRV_DATASOURCE,SRV_PROVIDERSTRING,SRV_LOCATION,SRV_CAT)
EXEC sp_linkedservers 

DECLARE servercursor CURSOR FOR 
	SELECT SRV_NAME, Active 
	FROM @Servers
	ORDER BY SRV_NAME

OPEN servercursor

FETCH NEXT FROM servercursor INTO @LinkedServerName, @Active
WHILE @@FETCH_STATUS=0
BEGIN
	DECLARE @isUp BIT = 0
		
	BEGIN TRY
		DECLARE @test INT
		EXEC @test = sp_testlinkedserver @LinkedServerName;
		IF @test = 0
			BEGIN
				SET @isUp = 1
			END
		ELSE
			BEGIN
				SET @isUp = 0
			END
	END TRY
	BEGIN CATCH			
		SET @isUp = 0;
	END CATCH
		
	IF @Active<>@isUp
	BEGIN
		UPDATE @Servers 
		SET Active=@isUp
		WHERE SRV_NAME=@LinkedServerName

		SET @Message = @Message + 'Active bit on server ' + @LinkedServerName + ' was set to ' + CONVERT(VARCHAR,@isUp) + '.' + CHAR(10) + CHAR(13)
	END		
	FETCH NEXT FROM servercursor INTO @LinkedServerName, @Active
END

CLOSE servercursor
DEALLOCATE servercursor

SELECT SRV_NAME
FROM @Servers
WHERE Active=1
ORDER BY SRV_NAME

PRINT @Message

Running this code will return an alphabetically ordered list of active linked servers.