Testing Connectivity to Multiple SQL Server Linked Servers

SQL Server 2022

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.

Fixing a SQL Server Job Status Without Restarting Services

SQL Server logo

Yesterday morning I received notice that a SQL Server job for an application I support that normally ran fairly regularly was not working. This job was not kicked off by a manual schedule, but was called from elsewhere.

I was able to run the job manually with no problem at all. Nevertheless, the job did not start running “automatically” as expected. After some digging, I found out that the job was kicked off in a database trigger on INSERT.

The section of code that kicked off the job looked like this:

SET @JOB_NAME = N'My job name'; 
 
IF NOT EXISTS(     
		select 1 
		from msdb.dbo.sysjobs_view job  
		inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id 
		where  
			activity.run_Requested_date is not null  
		and activity.stop_execution_date is null  
		and job.name = @JOB_NAME 
		) 
	BEGIN      
		PRINT 'Starting job ''' + @JOB_NAME + ''''; 
		EXEC msdb.dbo.sp_start_job @JOB_NAME; 
	END 
ELSE 
	BEGIN 
		PRINT 'Job ''' + @JOB_NAME + ''' is already started '; 
	END 

So this code should only return a row (and prevent job execution if the job is already running. The job was not running, and yet a row came back saying that the job had started two days before and never finished.

It just so happened that the SQL Server cluster had to be rebooted at just that time. The job appeared to be running, but was not.

The first solution: Restart the SQL Server Agent. This had no effect on the sysjobactivity table.

The second solution: Restart the entire SQL Server instance. This was not done, due to critical operations at work.

What else could be done?

Manually updating the table to reflect that the job was no longer running!

DECLARE @job_name VARCHAR(2000) = 'Your job name'; 

DECLARE @job_id uniqueidentifier =
    (SELECT job_id
    FROM msdb.dbo.sysjobs
    WHERE NAME LIKE @job_name); 

UPDATE msdb.dbo.sysjobactivity
SET stop_execution_date = GETDATE()
WHERE job_id = @job_id
AND start_execution_date =
    (SELECT MAX(start_execution_date)
    FROM msdb.dbo.sysjobactivity
    WHERE job_id = @job_id
AND run_Requested_date IS NOT NULL  
AND stop_execution_date IS NULL);

EXEC sp_stop_job @job_id = @job_id;

Running this block of code enters a time (now) for job execution end, and also manually sends a job stop command in case the job had started in the meantime.

After this, the trigger worked as expected!

SQL Server Script to Create INSERT Statements

Microsoft SQL Server logo

Though you can generate INSERT statements using SQL Server Management Services if the Generate Scripts functionality is enabled, this script may help in cases when it is not.

USE DatabaseName
GO
SET NOCOUNT ON

DECLARE @TableName VARCHAR(255)
DECLARE @IndividualInserts BIT

SET @TableName = 'TableName'
SET @IndividualInserts = 0

DECLARE @TableStructure TABLE (ColumnName VARCHAR(255), DataType VARCHAR(50), [MaxLength] INT, [precision] INT, [scale] INT, is_nullable BIT, PrimaryKey BIT, ColumnID INT)
DECLARE @ColumnName VARCHAR(255)
DECLARE @DataType VARCHAR(50)
DECLARE @MaxLength INT
DECLARE @precision INT
DECLARE @scale INT
DECLARE @is_nullable BIT
DECLARE @PrimaryKey BIT
DECLARE @ColumnID INT
DECLARE @SelectQuery VARCHAR(MAX) = ''
DECLARE @ResultQuery VARCHAR(MAX) = ''
DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @CrLf VARCHAR(10) = CHAR(13) + CHAR(10)

INSERT INTO @TableStructure
SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key',
	c.column_id
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID(@TableName) 
ORDER BY c.column_id

--SELECT * FROM @TableStructure

DECLARE TableCursor CURSOR FOR
SELECT * FROM @TableStructure 
ORDER BY ColumnID


OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @ColumnName, @DataType, @MaxLength, @precision, @scale, @is_nullable, @PrimaryKey, @ColumnID
WHILE @@FETCH_STATUS = 0
BEGIN
	--SELECT @ColumnName, @DataType, @MaxLength, @precision, @scale, @is_nullable, @PrimaryKey, @ColumnID
	SET @ColumnName = '[' + @ColumnName + ']'
	IF LEN(@SelectQuery)=0 SET @SelectQuery='SELECT' + @CrLf + '''INSERT INTO ' + @TableName + ' (' 
	ELSE SET @SelectQuery = @SelectQuery + ',' --@CrLf + ',' 
	SET @SelectQuery = @SelectQuery + @ColumnName -- + '   -- ' + @DataType

	IF LEN(@ResultQuery)=0 
	BEGIN
		IF @IndividualInserts=0 SET @ResultQuery = 'UNION ALL SELECT CASE WHEN ROW_NUMBER() OVER(ORDER BY ' + @ColumnName + ') &amp;amp;amp;amp;lt;&amp;amp;amp;amp;gt; 1 THEN '','' ELSE '''' END +' + @CrLf + '''('
		ELSE SET @ResultQuery= @CrLf + '(' 
	END
	ELSE SET @ResultQuery = @ResultQuery + ','
	SELECT @ResultQuery = 
	CASE
		WHEN @DataType LIKE '%CHAR' 
		THEN @ResultQuery + '''+ COALESCE('''''''' + ' + @ColumnName + ' + '''''''',''NULL'') +'''
		WHEN @DataType LIKE '%DATETIME' 
		THEN @ResultQuery + '''+ COALESCE(''CONVERT(DATETIME,'''''' + CONVERT(VARCHAR,' + @ColumnName + ',21) + '''''',21)'',''NULL'') +'''		
		WHEN @DataType LIKE 'NUMERIC%' 
		THEN @ResultQuery + '''+ COALESCE(CONVERT(VARCHAR,' + @ColumnName + '),''NULL'') +'''
		WHEN @DataType = 'TEXT' 
		THEN @ResultQuery + '''+ COALESCE('''''''' + CONVERT(VARCHAR,' + @ColumnName + ') + '''''''',''NULL'') +'''
		WHEN @DataType LIKE '%INT' 
		THEN @ResultQuery + '''+ COALESCE(CONVERT(VARCHAR,' + @ColumnName + '),''NULL'') +'''
		WHEN @DataType = 'FLOAT' 
		THEN @ResultQuery + '''+ COALESCE(CONVERT(VARCHAR,' + @ColumnName + '),''NULL'') +'''
		ELSE @ResultQuery + '''+ COALESCE(' + @ColumnName + ',''NULL'') +'''
	END
	--SET @ResultQuery = @ResultQuery + '   -- ' + @DataType	
	
	FETCH NEXT FROM TableCursor INTO @ColumnName, @DataType, @MaxLength, @precision, @scale, @is_nullable, @PrimaryKey, @ColumnID
END
CLOSE TableCursor
DEALLOCATE TableCursor

SET @SelectQuery = @SelectQuery + ') VALUES' 
IF @IndividualInserts=0 SET @SelectQuery = @SelectQuery + ''''
SET @ResultQuery = @ResultQuery + ')'' FROM ' + @TableName + ';'

SET @SQL = @SelectQuery + @ResultQuery

--SELECT @SelectQuery
--SELECT @ResultQuery

--SELECT @SQL

--PRINT @SelectQuery 
--PRINT @ResultQuery
EXEC sp_executesql @SQL

This script may not work in every single case, as there are data types not accounted for here.