Unable to pass the servername in the openquery Linkedserver

Question

Unable to pass the servername in the openquery Linkedserver

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
January 24, 2017 at 12:43 pm

#317831

Hi Experts
Unable to pass the servername in the @param1 variable. It just does not work. Can you suggest how to fix the issue? Error is mentioned below -
declare @v-2 varchar(200)
Declare @param1 varchar(200) = 'server'
set @v-2='
SELECT *
FROM OPENQUERY(' + @param1 +', ''EXEC master.dbo.procedure @sysservername =' + @param1 + ''')'
select @v-2
exec(@v)
Error :
I am actually passing the servername to another proc to validate.
Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 55
The metadata could not be determined because statement 'EXEC @retval = sys.sp_testlinkedserver @sysservername;' in procedure 'procedure' invokes an extended stored procedure.
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

SQL-DBA-01 - Tuesday, January 24, 2017 12:43 PM
Hi Experts
Unable to pass the servername in the @param1 variable. It just does not work. Can you suggest how to fix the issue? Error is mentioned below -
declare @v-2 varchar(200)
Declare @param1 varchar(200) = 'server'
set @v-2='
SELECT *
FROM OPENQUERY(' + @param1 +', ''EXEC master.dbo.procedure @sysservername =' + @param1 + ''')'
select @v-2
exec(@v)
Error :
I am actually passing the servername to another proc to validate.
Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 55
The metadata could not be determined because statement 'EXEC @retval = sys.sp_testlinkedserver @sysservername;' in procedure 'procedure' invokes an extended stored procedure.

<delete> Misread, hang on.
.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Try this:

set @v-2=' SELECT * FROM OPENQUERY([' + @param1 +'], ''EXEC master.dbo.procedure @sysservername =''' + @param1 + ''')'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

January 24, 2017 at 1:43 pm

#1924732

Edited my post above.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

Still the same error. I think one quote at the end is missing.

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

SQL-DBA-01 - Tuesday, January 24, 2017 1:51 PM
Still the same error. I think one quote at the end is missing.

I get an error on the procedure name "procedure" but then it doesn't exist in my master database.

declare @v-2 nvarchar(max), @param1 nvarchar(max) = N'127.0.0.1';

set @v-2='
SELECT *
FROM OPENQUERY([' + @param1 +'], ''EXEC master.dbo.procedure @sysservername = ''''' + @param1 + ''''''')'

print @v-2;

exec (@v);

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

How to write an alternate script against [sys.sp_testlinkedserver]

When I pass the above result set to this proc ..
it throws error:

Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXEC @retval = sys.sp_testlinkedserver @servername;' in procedure 'proc' invokes an extended stored procedure.

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

You need to try this:

exec('exec sp_testlinkedserver [127.0.0.2]') at [127.0.0.1]

Where I am testing this I have a linked server using the loopback ip address.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

Can I not store the results in a variable? say, @retval

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 9

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

SQL-DBA-01 - Tuesday, January 24, 2017 3:11 PM
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

You can't use openquery to run a stored procedure on a remote server. Look at what I last posted.