I have a query that I am trying to run against an oracle database. The oracle is a linked server and so I am using openquery to do this. The issue resolves around the date in the where clause.
Here is the statement:
SET @TSQL = 'SELECT SEQ FROM OPENQUERY([LINKED_SERVER], ''SELECT SEQ
FROM SCHEMA.TABLE
WHERE A_NUM BETWEEN 500 AND 599
AND A_DATE = to_date(''02/26/2015'',''mm/dd/yyyy'')'')'
If I do a print on that variable and take it and run it in TOAD it works fine. When I run it in SSMS I get this error:
Incorrect syntax near '02'.
If I take out the last line and run it in SSMS it returns records just fine. Am I just missing something?
Thank you!
Why are you putting it into a variable? I don't see anything dynamic in your query.
Try this:
SELECT SEQ FROM OPENQUERY([LINKED_SERVER], 'SELECT SEQ
FROM SCHEMA.TABLE
WHERE A_NUM BETWEEN 500 AND 599
AND A_DATE = to_date(''02/26/2015'',''mm/dd/yyyy'')')
1 Like
I have variables I will be adding this was just testing something new I wanted to try.
But when i looked at your code I saw you used 2 sets of single around the date and date format and that worked so thank you!
1 Like
It shouldn't have done, surely?
if I print your @TSQL I get:
SELECT SEQ FROM OPENQUERY([LINKED_SERVER], 'SELECT SEQ
FROM SCHEMA.TABLE
WHERE A_NUM BETWEEN 500 AND 599
AND A_DATE = to_date('02/26/2015','mm/dd/yyyy')')
which doesn't have enough doubling-up of the single quotes around the date and format, so I think TOAD should give you a syntax error??
But sounds like you've already found & fixed the nested quoting