Stored procedure to get the status of a linked server

Question

I'm trying to create a stored procedure to get the status of a linked server to prevent errors when trying to execute anything on it. I saw some examples online, but I'm not having any success with those.

Here is my code:

ALTER PROCEDURE [dbo].[checkLinkedServer] 
    @servername ntext
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @retval int = 0;
    BEGIN TRY
        EXEC @retval = sys.sp_testlinkedserver @servername;
        SELECT 1;
    END TRY
    BEGIN CATCH
        SELECT 0;
    END CATCH;      
END

I'm always getting a return value of 0 - no matter if the linked server exists or not.

Any ideas what I'm doing wrong here?

Answer 1

Your @servername parameter needs to be of the type sysname not ntext as the error message says

If you can't change the parameter going into the procedure then consider creating a new variable inside the proc and doing a convert:

ALTER PROCEDURE [dbo].[checkLinkedServer] 
    @servername ntext
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @retval int = 0,
            @sysservername sysname;
    BEGIN TRY
        SELECT  @sysservername = CONVERT(sysname, @servername);
        EXEC @retval = sys.sp_testlinkedserver @sysservername;
        SELECT 1;
    END TRY
    BEGIN CATCH
        SELECT 0;
    END CATCH;      
END