t-sqlsql-server-2008-r2linked-serveropenquerymsdasql

Remote query with variable using MSDASQL


I am trying to create a query for an SSRS report. I need to get the last 3 months transactions from a linked server (to an iSeries).

So I have built a SQL variable that I want to execute with OpenQuery

declare @EarliestDate varchar(8), @SQL VARCHAR(200), @sDate varchar(8)

SET @EarliestDate=CAST(DATEPART(YEAR,DATEADD(m,-3, getdate())) AS VARCHAR(4)) 
    + RIGHT('00' + CAST(DATEPART(mm, DATEADD(m,-3, getdate())) AS varchar(2)), 2)+ '01'

SELECT @SQL= 'SELECT * FROM YEDB03P WHERE TCDAT  >' + '''' + @EarliestDate +'''' + ' ORDER BY TCDAT DESC'
print @EarliestDate
print @SQL

select * from openquery(BOCTEST2,@SQL)

But it seems that openquery doesn't like using a variable

What's the correct way to do this?


Solution

  • with help from Oleksandr Kucher I managed to do what I want with the below code (I added char(39) to the query string to force the quotes):

     declare @EarliestDate varchar(8), @SQL NVARCHAR(1000), @sDate varchar(8)
    
    SET @EarliestDate= CAST(DATEPART(YEAR,DATEADD(m,-3, getdate())) AS VARCHAR(4)) 
        + RIGHT('00' + CAST(DATEPART(mm, DATEADD(m,-2, getdate())) AS varchar(2)), 2)+ '01' 
    
    SELECT @SQL= 'insert into DBO.YEDB03P select * from openquery(BOCTEST2, ''SELECT * FROM YEDB03P WHERE TCDAT  >'+char(39)+CHAR(39) + @EarliestDate  +char(39)+CHAR(39)  + ' ORDER BY TCDAT DESC'')'
    
    --print @SQL
    
    /*
    The prints as
    
    insert into DBO.YEDB03P select * from openquery(BOCTEST2, 'SELECT * FROM YEDB03P WHERE TCDAT  >''20141001'' ORDER BY TCDAT DESC')
    
    */
    exec sp_executesql @SQL
    select * from PION..YEDB03P
    

    thanks