使用 MSDASQL 使用变量进行远程查询

Question

我正在尝试为 SSRS 报告创建查询。我需要从链接服务器(到 iSeries(获取最近 3 个月的交易。

所以我构建了一个我想用OpenQuery执行的SQL变量。

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)

但似乎openquery不喜欢使用变量

正确的方法是什么？

Answer 1

OPENQUERY 不接受变量作为其参数。

但是您可以将整个 OPENQUERY 语句移动到动态 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

Answer 2

在Oleksandr Kucher的帮助下，我设法用下面的代码做了我想做的事情(我在查询字符串中添加了char(39)来强制引号(：

 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

谢谢