Syntax issue in SQL Server, using OPENROWSET

Question

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.

Dave C · Accepted Answer · 2014-07-17 13:32:59Z

2

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)

edited Jul 17, 2014 at 13:32

answered Jul 16, 2014 at 20:54

Dave C

7,4821 gold badge21 silver badges30 bronze badges

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

2 Comments

SantoshGupta7 Over a year ago

That definitely helped! However, I'm now getting this issue: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '2014'. Msg 105, Level 15, State 1, Line 2 Unclosed quotation mark after the character string ')'.

Dave C Over a year ago

Updated, was missing a ,. I'm not seeing any un-closed quotation marks though. Did you get it sorted out?

Hart CO · Accepted Answer · 2014-07-16 20:55:52Z

1

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)

answered Jul 16, 2014 at 20:55

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

2 Comments

SantoshGupta7 Over a year ago

That definitely helped! However, I'm now getting this issue: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '2014'. Msg 105, Level 15, State 1, Line 2 Unclosed quotation mark after the character string ')'. Where 2014 is a part of the MAX date found in @Filt

Hart CO Over a year ago

@user3259896 Can you show me a string that executes with a hard-coded value instead of the dynamic version? I'm just not sure how EXEC GetData.db.Staging @Mode = ''''Date''' +@Filt+ ') should get structured.

Cibo FATA8 · Accepted Answer · 2023-12-05 13:09:25Z

0

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), '');

answered Dec 5, 2023 at 13:09

Cibo FATA8

1016 bronze badges

Collectives™ on Stack Overflow

Syntax issue in SQL Server, using OPENROWSET

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related