Variables in OPENQUERY "Deferred prepare could not be completed" error

Question

I understand that you cannot include variables in OPENQUERY so the work around is dynamic SQL and I did the following:

DECLARE @etd AS DATETIME = '2014-06-28'
DECLARE @source AS VARCHAR(46)
DECLARE @dbName AS VARCHAR(30)
DECLARE @query AS VARCHAR(MAX)
DECLARE @openQuery AS VARCHAR(MAX)

SELECT TOP(1) @source = [Source], @dbName = DbName
FROM dbo.SomeTable
WHERE SystemCode = 'SomeSystem'

SET @query = 'SELECT *
    FROM [' + @dbName + '].dbo.Table1 t1
    LEFT JOIN [' + @dbName + '].dbo.Table2 t2 ON t1.bookno = t2.tranno
    WHERE (YEAR(t1.etddate) = ' + CAST(YEAR(@etd) AS VARCHAR(4)) + 
    ' AND MONTH(t1.etddate) = ' + CAST(MONTH(@etd) AS VARCHAR(2)) + 
    ' AND DAY(t1.etddate) = ' + CAST(DAY(@etd) AS VARCHAR(2)) +')'

SET @openQuery = 'SELECT * FROM OPENQUERY([' + @source + '],''' + @query + ''')'

EXECUTE (@openQuery)

When I use SELECT @openQuery I don't see anything wrong with the query string, but once I execute it, I received the following error:

OLE DB provider "SQLNCLI11" for linked server "xxx.xxx.xxx.xxx,1433" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'xxxx.dbo.t1'. (where 'xxxx' is the table name variable)

I've been searching for answers but I cannot find any, I really need your help guys.

Source is a linked server? Can you execute the query built in @query in your Management Studio? — Martin K., Jul 15 '14 at 10:57

score 1 · Answer 1 · answered Sep 28 '15 at 05:08

You might temporarily change the EXECUTE to PRINT (PRINT @openQuery), see what SQL is actually being generated, then attempt to run the generated sql directly in SSMS. It might be obvious when you see the generated sql, but if not, you might get a more descriptive error message.

Variables in OPENQUERY "Deferred prepare could not be completed" error

1 Answers1