For MSSQL.
This helped me determine if linked servers were alive. Using an Open Query connection and a TRY CATCH to put the results of the error to something useful.
IF OBJECT_ID('TEMPDB..#TEST_CONNECTION') IS NOT NULL DROP TABLE #TEST_CONNECTION
IF OBJECT_ID('TEMPDB..#RESULTSERROR') IS NOT NULL DROP TABLE #RESULTSERROR
IF OBJECT_ID('TEMPDB..#RESULTSGOOD') IS NOT NULL DROP TABLE #RESULTSGOOD
DECLARE @LINKEDSERVER AS VARCHAR(25) SET @LINKEDSERVER = 'SERVER NAME GOES HERE'
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @OPENQUERY AS VARCHAR(MAX)
--IF OBJECT_ID ('dbo.usp_GetErrorInfo', 'P' ) IS NOT NULL DROP PROCEDURE usp_GetErrorInfo;
--GO
---- Create procedure to retrieve error information.
--CREATE PROCEDURE dbo.usp_GetErrorInfo
--AS
--SELECT
-- ERROR_NUMBER() AS ErrorNumber
-- ,ERROR_SEVERITY() AS ErrorSeverity
-- ,ERROR_STATE() AS ErrorState
-- ,ERROR_PROCEDURE() AS ErrorProcedure
-- ,ERROR_LINE() AS ErrorLine
-- ,ERROR_MESSAGE() AS Message;
--GO
BEGIN TRY
SET @SQL='
SELECT 1
'''
--SELECT @SQL
SET @OPENQUERY = 'SELECT * INTO ##TEST_CONNECTION FROM OPENQUERY(['+ @LINKEDSERVER +'],''' + @SQL + ')'
--SELECT @OPENQUERY
EXEC(@OPENQUERY)
SELECT * INTO #TEST_CONNECTION FROM ##TEST_CONNECTION
DROP TABLE ##TEST_CONNECTION
--SELECT * FROM #TEST_CONNECTION
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
IF OBJECT_ID('dbo.usp_GetErrorInfo') IS NOT NULL -- IT WILL ALWAYS HAVE SOMTHING...
BEGIN
CREATE TABLE #RESULTSERROR (
[ErrorNumber] INT
,[ErrorSeverity] INT
,[ErrorState] INT
,[ErrorProcedure] INT
,[ErrorLine] INT
,[Message] NVARCHAR(MAX)
)
INSERT INTO #RESULTSERROR
EXECUTE dbo.usp_GetErrorInfo
END
END CATCH
BEGIN
IF (Select ERRORNUMBER FROM #RESULTSERROR WHERE ERRORNUMBER = '1038') IS NOT NULL --'1038' FOR ME SHOWED A CONNECTION ATLEAST.
SELECT
'0' AS [ErrorNumber]
,'0'AS [ErrorSeverity]
,'0'AS [ErrorState]
,'0'AS [ErrorProcedure]
,'0'AS [ErrorLine]
, CONCAT('CONNECTION IS UP ON ', @LINKEDSERVER) AS [Message]
ELSE
SELECT * FROM #RESULTSERROR
END
learn.microsoft.com
