OPENQUERY and variable question

I am trying to create a stored proc that uses OPENQUERY to get data from a db2 database. Can anyone look at the code below and let me know what I am doing wrong.

This is my error msg:

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'SELECT'.

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near ')'.

This is my code:

USE [FVDashBoard]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spDaily_TBT_Load_OpenQuery]

as

-- define variables

declare @.FM_DT char(10)

set @.FM_DT = (SELECT CALENDAR_DT FROM dbo.PROD_SCHD WHERE(ISSUER_NO = 90)AND (CALENDAR_DT =CONVERT(VARCHAR(10), GETDATE() - 2, 120)))

declare @.remotesql nvarchar(4000)

set @.remotesql =

'select * from OPENQUERY (BACARDI, '+

'SELECT BRAND_NO FROM WRKCON.DLY_TBT_RCNCLTN_CONTROL

WHERE SOR_REC_CREAT_DT ='+@.FM_DT+ ')'

PRINT @.remotesql

EXEC (@.remotesql)

Thanks!

The second parameter of OPENQUERY (sql statement) should be string – enclosed by single quote. Try the below query..

Code Snippet

set @.remotesql =

'select * from OPENQUERY (BACARDI, '+

'''SELECT BRAND_NO FROM WRKCON.DLY_TBT_RCNCLTN_CONTROL

WHERE SOR_REC_CREAT_DT ='+@.FM_DT+ ''')'

|||

This statement

'select * from OPENQUERY (BACARDI, '+

'SELECT BRAND_NO FROM WRKCON.DLY_TBT_RCNCLTN_CONTROL

WHERE SOR_REC_CREAT_DT ='+@.FM_DT+ ')'

seems to be missing a closing parenthesis.

|||

Thanks so much for your reply.

As a follow-up question, how could I change the vaule of @.FM_DT? Currently the value of the variable is something like 2007-08-03. What I need is for the variable to be something like '2007-08-03'. Do you know of a way to do this.

Thanks for your help!

|||

You need to add a few more quotes...

Something like this:

= '''+ @.FM_DT + ''')'

Do a PRINT or SELECT on your statement until it is correct.