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?
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