Syntax issue in SQL Server, using OPENROWSET

8gsdolmq 于 5个月前发布在 SQL Server

关注(0)|答案(3)|浏览(84)

I am trying to Execute a stored procedure that requires to variables be passing into to it. One is a static, the other is a dynamic variable.

DECLARE @Filt DATETIME 
SET @Filt = (SELECT DISTINCT MAX(Date) FROM Data.db.Staging)
SELECT * INTO #tempData FROM OPENROWSET('SQLNCLI', 'Server=ISR14  \MSSQL2012;Trusted_Connection=yes;', 'EXEC GetData.db.Staging @Mode = ''Date'' @Filt ')

but that doesn't work, got the error back "Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '@Filt'."

I'm guessing it is because Filt is dynamic statement. So I tried this

DECLARE @FilterData DATETIME
DECLARE @sql VARCHAR(200) 
SET @Filt = (SELECT DISTINCT MAX(AsOfDate) FROM Data.db.Staging)
SET @sql = 'EXEC GetData.db.Staging @Mode = ''Date'' @Filt =  '  + @Filt

SELECT * INTO #tempData FROM OPENROWSET('SQLNCLI', 'Server=ISR14\MSSQL2012;Trusted_Connection=yes;',
 @sql)

But I get the message back

"Msg 102, Level 15, State 1, Line 24 Incorrect syntax near '@sql'."

It seems that OPENROWSET can only accept strings. But I want to pass a variable that is dynamic.

sql-server

来源：https://stackoverflow.com/questions/24790396/syntax-issue-in-sql-server-using-openrowset

3条答案

按热度按时间

ogsagwnx1#

You have to put the whole statement into a variable and run it, and convert @FilterData to a varchar to concatenate it.

You can't use variables with openquery/openrowset.

Try this and check the print output... if it works and looks ok, then EXEC(@sql2)

DECLARE @FilterData DATETIME
DECLARE @sql VARCHAR(200), @sql2 VARCHAR(500)
SET @FilterData = '2014-07-01'--(SELECT DISTINCT MAX(AsOfDate) FROM Data.db.Staging)
SET @sql = 'EXEC GetData.db.Staging @Mode = ''''Date'''', @Filt =  '''''  + CONVERT(VARCHAR(20),@FilterData ,120) + ''''''

SET @sql2 = 'SELECT * INTO #tempData FROM OPENROWSET(''SQLNCLI'', ''Server=ISR14\MSSQL2012;Trusted_Connection=yes;'',
 '''+@sql+''')'

print @sql2
--exec(@sql2)

赞(0）回复(0）举报 5个月前

hsvhsicv2#

You need to make the whole query dynamic, not sure if I got it nailed down, but something like:

DECLARE @Filt DATETIME 
       ,@sql VARCHAR(MAX)
SET @Filt = (SELECT MAX(Date) FROM Data.db.Staging)
SET @sql = 'SELECT * INTO #tempData FROM OPENROWSET(''SQLNCLI'', ''Server=ISR14  \MSSQL2012;Trusted_Connection=yes;'', ''EXEC GetData.db.Staging @Mode = ''''Date''' +@Filt+ ')'
EXEC (@sql)

赞(0）回复(0）举报 5个月前

zz2j4svz3#

I got error Incorrect syntax near '' when executing @sql. In my case there was invalid character at the beginning of NVARCHAR. This helped me:

SET @sql = REPLACE(@sql, NCHAR(0xFEFF), '');

赞(0）回复(0）举报 5个月前

我来回答

Syntax issue in SQL Server, using OPENROWSET

3条答案

相关问题

热门标签

最新问答