I wrote this procedure (q2). When I run q1, I get the error :
Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because every code path results in an error; see previous errors for some of these.
Msg 2812, Level 16, State 62, Procedure sp_describe_first_result_set, Line 1
Could not find stored procedure 'master.dbo.usp_checkLinkedServer'.
q1:
DECLARE @StatementToRun VARCHAR(MAX)
SELECT @StatementToRun = StatementToRun FROM OPENQUERY("linkedserver", 'EXEC master.dbo.usp_checkLinkedServer')
SELECT @StatementToRun
EXEC (@StatementToRun)
q2:
create PROCEDURE usp_checkLinkedServer (
@sysservername varchar(1000) = 'server'
)
AS
set nocount on
DECLARE @LinkedServerDBCheck NVARCHAR(2000)
Declare @count int;
--Print 1
BEGIN TRY
IF EXISTS(SELECT * FROM sys.servers WHERE name = @sysservername)
BEGIN
--SELECT @sys= CONVERT(sysname, @sysservername);
-- EXEC @retval = msdb.sys.sp_testlinkedserver @servername = @sysservername;
--SELECT 1;
SET @LinkedServerDBCheck = 'SELECT 1 FROM '+@sysservername+'.master.sys.databases WHERE name = ''master'''
--select @LinkedServerDBCheck
EXEC sys.sp_executesql @LinkedServerDBCheck
SELECT @Count = @@ROWCOUNT
IF (@Count > 0)
BEGIN
print 'LINKED SERVER ''' + ISNULL(@sysservername,'') + ''' Works.'
END
ELSE
PRINT 'LINKED SERVER '''+ ISNULL(@sysservername,'') + ''' DOES NOT Work!'
END
END TRY
BEGIN CATCH
SELECT 0;
END Catch
Thanks.