Remote query with variable using MSDASQL

Question

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?

possible duplicate of including parameters in OPENQUERY

Pred
– Pred

2014-12-18 15:52:03 +00:00
Commented Dec 18, 2014 at 15:52 — Pred
– Pred, Commented Dec 18, 2014 at 15:52

Oleksandr Kucher · Accepted Answer · 2014-12-18 15:54:21Z

1

OPENQUERY does not accept variables for its arguments.

But you can move whole OPENQUERY statement into your dynamic SQL:

declare @EarliestDate varchar(8), @SQL VARCHAR(MAX), @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 openquery(BOCTEST2, ''SELECT * FROM YEDB03P WHERE TCDAT  >' + '''''' + @EarliestDate +'''''' + ' ORDER BY TCDAT DESC'')'
print @EarliestDate
print @SQL

EXEC @SQL

answered Dec 18, 2014 at 15:54

Oleksandr Kucher

4893 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Our Man in Bananas Over a year ago

I had to alter it slightly (adding an extra quote) but it works with sp_executeSQL so thanks

Oleksandr Kucher Over a year ago

Great! Yeah, I did not test the query, so it may be not perfect in terms of single quotes, which are a nightmare when you use dynamic SQL :)

Community · Accepted Answer · 2017-05-23 12:05:52Z

0

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

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Dec 18, 2014 at 16:59

Our Man in Bananas

6,00525 gold badges104 silver badges158 bronze badges

Collectives™ on Stack Overflow

Remote query with variable using MSDASQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related