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.

SQL Script to Find Text in all Database Tables and Views…Revised! Again!

SQL Server logo

The SQL script below, which modifies one I wrote last year, has been modified to work with SQL Server 2008 and newer versions. On these versions, when querying SQL Server to output the schema of a database table, length values of MAX are returned as -1. Since this script builds table variables that mimic the schema of the actual table, this needed to be accounted for so no errors would occur as a result of the MAX length being used.

If MAX values are not used, the output of this script should be no different than on the previous version.

-- Set database to search
USE dbname
GO
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
------------------------------------------------------------------------------------------------------------------------------------
-- Set test value here and set @SearchViews to 1 if views are to be included - 0 if not; no other code below changes
-- Set @PrintAllSQLStmts to 1 if you want all of the statements printed as messages
DECLARE @TestValue VARCHAR(8000)
DECLARE @SearchViews BIT
DECLARE @PrintAllSQLStmts BIT

SET @PrintAllSQLStmts = 0
SET @SearchViews = 0
SET @TestValue = 'text to search'
------------------------------------------------------------------------------------------------------------------------------------

/* If the version of SQL Server is 2005 or newer, these can be set to MAX rather than 8000 */
DECLARE @SQL VARCHAR(8000)
DECLARE @varSQL VARCHAR(8000)
DECLARE @WhereClause VARCHAR(8000)
/******************************************************************************************/

DECLARE @TableName VARCHAR(128)
DECLARE @varTableName VARCHAR(128)
DECLARE @TableSchema VARCHAR(128)
DECLARE @ColumnName VARCHAR(128)
DECLARE @MaxLength VARCHAR(4)
DECLARE @NMaxLength VARCHAR(4)

IF @@VERSION LIKE 'Microsoft SQL Server  2000%'
	BEGIN
		SET @MaxLength = '8000'
		SET @NMaxLength = '4000'
	END
ELSE
	BEGIN
		SET @MaxLength = 'MAX'
		SET @NMaxLength = 'MAX'
	END

DECLARE @SchemaObjects TABLE (
	TABLE_NAME VARCHAR(128),
	TABLE_SCHEMA VARCHAR(128)
)

DECLARE @TypeTable TABLE (
	UserType VARCHAR(128) PRIMARY KEY,
	BaseType VARCHAR(128)
)

DECLARE @SizableTypes TABLE (
	TypeName VARCHAR(128) PRIMARY KEY
)

INSERT INTO @SizableTypes VALUES ('VARBINARY')
INSERT INTO @SizableTypes VALUES ('VARCHAR')
INSERT INTO @SizableTypes VALUES ('NVARCHAR')
INSERT INTO @SizableTypes VALUES ('CHAR')
INSERT INTO @SizableTypes VALUES ('NCHAR')

INSERT INTO @TypeTable
SELECT ut.name, bt.name FROM SysTypes ut, SysTypes bt
 WHERE ut.xusertype > 256 AND bt.xusertype < 256
 AND ut.xtype = bt.xtype

INSERT INTO @SchemaObjects
SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE'

IF @SearchViews = 1
BEGIN
	INSERT INTO @SchemaObjects
	SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS
END

DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_NAME, TABLE_SCHEMA FROM @SchemaObjects

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @WhereClause = ''

	DECLARE ColumnCursor CURSOR FAST_FORWARD FOR
	SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @TableSchema AND ((DATA_TYPE IN ('CHAR','NCHAR','VARCHAR','NVARCHAR')) AND (CHARACTER_MAXIMUM_LENGTH IS NOT NULL) AND (CHARACTER_MAXIMUM_LENGTH >= LEN(@TestValue)) OR DATA_TYPE IN ('TEXT','NTEXT'))
	OPEN ColumnCursor
	FETCH NEXT FROM ColumnCursor INTO @ColumnName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF LEN(@WhereClause) > 0 SET @WhereClause = @WhereClause + ' OR '
		SET @WhereClause = @WhereClause + '(CONVERT(VARCHAR,[' + @ColumnName + ']) LIKE ''%' + @TestValue + '%'')'
		FETCH NEXT FROM ColumnCursor INTO @ColumnName
	END
	CLOSE ColumnCursor
	DEALLOCATE ColumnCursor
	IF LEN(@WhereClause) > 0
	BEGIN
		SET @varTableName = REPLACE(@TableName,' ','_')
		SET @varSQL = 'DECLARE @' + @varTableName + ' TABLE (' + CHAR(10)
		SELECT @varSQL = @varSQL + ' [' + sc.name + '] ' +
		CASE WHEN st.name = 'TIMESTAMP' THEN 'DATETIME'
			 WHEN tt.UserType IS NOT NULL THEN UPPER(tt.BaseType)
		ELSE UPPER(st.name) END +
		CASE WHEN tt.BaseType IN (SELECT TypeName FROM @SizableTypes) OR st.name IN (SELECT TypeName FROM @SizableTypes) THEN '(' +
			CASE WHEN (LEFT(tt.BaseType,3) = 'VAR' OR LEFT(st.name,3) = 'VAR') AND (sc.[length] >= 8000 OR sc.[length] = -1) THEN @MaxLength
				 WHEN (LEFT(tt.BaseType,3) = 'NVA' OR LEFT(st.name,3) = 'NVA') AND (sc.[length] >= 4000 OR sc.[length] = -1) THEN @NMaxLength
			ELSE CAST(sc.[length] AS VARCHAR) END + ') '
		ELSE ' ' END +
		CASE WHEN sc.isnullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ',' + CHAR(10)
		FROM SysObjects so
		JOIN SysColumns sc ON sc.id = so.id
		JOIN SysTypes st ON st.xusertype = sc.xusertype
		LEFT OUTER JOIN @TypeTable tt ON st.name = tt.UserType
		WHERE so.name = @TableName
		ORDER BY sc.colid		

		SET @SQL =  CHAR(10) + 'SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM [' + @TableSchema + '].[' + @TableName + '] WHERE ' + @WhereClause + ';'
		SELECT @varSQL = SUBSTRING(@varSQL,1,LEN(@varSQL) - 2) + CHAR(10) + ');' + CHAR(10)

		SET @varSQL = @varSQL + 'INSERT INTO @' + @varTableName + CHAR(10) + 'SELECT * FROM [' + @TableSchema + '].[' + @TableName + ']' + CHAR(10) + 'WHERE ' + @WhereClause + ';' + CHAR(10)
		SET @varSQL = @varSQL + 'IF EXISTS(SELECT * FROM @' + @varTableName + ')' + CHAR(10)
		SET @varSQL = @varSQL + 'BEGIN' + CHAR(10)
		SET @varSQL = @varSQL + '   SET NOCOUNT OFF;' + CHAR(10)		

		IF @PrintAllSQLStmts = 0
			SET @varSQL = @varSQL + '   PRINT ''' + CHAR(10) + CHAR(10) + CHAR(10) + '/* New Query */' + CHAR(10) + REPLACE(@SQL,'''','''''') + ''';'  + CHAR(10)
		ELSE
			PRINT CHAR(10) + CHAR(10) + CHAR(10) + '/* New Query */' + @SQL + CHAR(10)		

		SET @varSQL = @varSQL + '   SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM @' + @varTableName + ';' + CHAR(10)		

		SET @varSQL = @varSQL + '   SET NOCOUNT ON;' + CHAR(10)
		SET @varSQL = @varSQL + 'END' + CHAR(10)
		SET @varSQL = @varSQL + CHAR(10) + CHAR(10) + CHAR(10)
		--PRINT @varSQL
		EXEC (@varSQL)
		IF @@ERROR <> 0
		BEGIN
			PRINT '/* Statement causing error */' + CHAR(10) + CHAR(10) + @varSQL + CHAR(10) + '/* End Statement */' + CHAR(10)
			PRINT 'Length of SQL statement: ' + CONVERT(VARCHAR,LEN(@varSQL)) + CHAR(10)
		END
	END
	FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
END
CLOSE TableCursor
DEALLOCATE TableCursor

Searching for Text in the Create Statements of Views and Stored Procedures in SQL Server

SQL Server logo

This is closely related to my last post, where we were searching for text in the data itself, but now we’re just looking to see if the text exists in the statements that create views or stored procedures. This has been tested only on SQL Server 2008 R2. If you do not have permission on the database to see the definitions of views or stored procedures, then you will get zero rows returned. Otherwise, the names of the views and stored procs containing the text in @TestValue will be listed here, along with their respective CREATE statements. This script should run much faster than the one in the above-referenced post since no cursors are being used here.

-- Set database to search
USE dbname
GO
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
------------------------------------------------------------------------------------------------------------------------------------
-- Set test value here
-- no other code below changes 

DECLARE @TestValue VARCHAR(MAX) 

-- If characters in @TestValue need to be escaped, use backslash
SET @TestValue = 'text to search for'
------------------------------------------------------------------------------------------------------------------------------------

SELECT DISTINCT
    o.name AS [Object_Name],
    o.type_desc,
	m.[definition]
FROM sys.sql_modules m
    INNER JOIN
    sys.objects o
        ON m.object_id = o.object_id
WHERE m.definition LIKE '%' + @TestValue + '%' ESCAPE '\';