Getting OUTPUT from Nested Dynamic SQL with a Remote Server

Question

I am trying to execute some logic on a remote server using dynamic SQL, but when I set the dynamic SQL and try to call it nothing happens.. If I call the execution without making the execution dynamic it works without issue.

Not sure if what I am trying to do is allowed when passing OUTPUT values but figured I would see if anyone has any thoughts

DECLARE @sqlserver NVARCHAR(MAX) = '' 
DECLARE @DBName NVARCHAR(MAX) = '' 
DECLARE @parms NVARCHAR(MAX) = N'@output INT OUT', @output INT, @sql NVARCHAR(MAX) = ' 
;WITH DuplicateStatusCodes AS (  
    SELECT CodeTypeID FROM EDDSDBO.Code WHERE Name = ''Master'' OR Name = ''Unique'' OR Name = ''Duplicate''  
    )  
    SELECT ROW_NUMBER() OVER (ORDER BY CodeTypeID) RN, CodeTypeID INTO #temp 
    FROM DuplicateStatusCodes 
    GROUP BY CodeTypeID  
    HAVING COUNT(CodeTypeID) = 3 
    DECLARE    @count INT = (SELECT COUNT(1) FROM #temp) 
    DECLARE @ctr INT = 1; 
    IF @count > 0 
    BEGIN 
        WHILE @ctr <= @count BEGIN 
        DECLARE    @parms NVARCHAR(MAX) = N''@inneroutput INT OUT'';  
        DECLARE @inneroutput INT 
        DECLARE @codeartifact INT = (SELECT CodeTypeID FROM #temp WHERE RN <= 1 * @ctr and RN > 1 * (@ctr - 1)) 
        DECLARE @duplicatestatuscheck NVARCHAR(MAX) = ''SELECT @inneroutput = COUNT(1) FROM eddsdbo.codeartifact_''+CAST(@codeartifact AS VARCHAR)+''''  
        EXEC sys.sp_executesql @duplicatestatuscheck, @parms, @inneroutput OUT 
        SELECT @output = @inneroutput 
        IF @inneroutput > 0 
            BREAK;     
        SET @ctr = @ctr + 1; 
        END 
    END 
        ELSE 
    BEGIN 
        SELECT @output = ''0'' 
    END 
' 
DECLARE @linksql NVARCHAR(MAX) = ' 
DECLARE @sql NVARCHAR(MAX) 
DECLARE @parms NVARCHAR(MAX) = N''@output INT OUT'' 
EXEC '+QUOTENAME(@sqlserver)+'.'+QUOTENAME(@DBName)+'.sys.sp_executesql @sql, @parms, @output = @output OUT'

--This does not work  
EXEC sp_executesql @linksql, @parms, @output = @output 
PRINT @output 

--This works fine
EXEC [RemoteServerName].[DatabaseName].sys.sp_executesql @sql, @parms, @output = @output OUT ```

Appreciate any help

Yeah the sql will execute and return the output is I execute like this: EXEC [RemoteServerName].[DatabaseName].sys.sp_executesql. It does not work with the dynamic sql though — iWROTEitANDdidITlive, Jan 29 '20 at 21:02

user2065377 · Answer 1 · 2020-01-29T22:25:46.267

you would need to use OPENROWSET. Something like this:

    DECLARE @ServerName VARCHAR(255) = '...'
    ,@DatabaseName VARCHAR(255) = '...'
    ,@Pwd VARCHAR(255) = '...'
    ,@UID VARCHAR(255) = '...'

    ,@Name VARCHAR(255) = 'Master'

DECLARE @connect varchar(max) = '''Server=' + @ServerName + ';database=' + @DatabaseName + ';Uid=' + @UID + ';Pwd=' + @Pwd + ';'''
declare @sqlstring varchar(max) = 'SET ANSI_NULLS OFF; SET ANSI_WARNINGS OFF;
    SELECT CodeTypeID FROM EDDSDBO.Code WHERE Name = ''''' + @Name + ''''' '

declare @TheExecutableStr varchar(max) = '
    SELECT *
    FROM OPENROWSET(''SQLNCLI'', ' + @connect + ',
         ''' + @sqlstring + ''')'

SELECT @TheExecutableStr
--EXEC(@TheExecutableStr)

Please note you have to count single quote very diligently, hence SELECT @TheExecutableStr. It is for debugging purposes. You can view the query to be executed and actually copy/paste and execute before uncommenting last statement. Hope it helps.

just noticed, you have sys.sp_executesql within sys.sp_executesql which might be problematic. you might need to find a way to refactor the code.

Getting OUTPUT from Nested Dynamic SQL with a Remote Server

1 Answers1