OpenQuery to Linked Server (Oracle) not working

Transact-SQL
1

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!

2

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
3

I have variables I will be adding this was just testing something new I wanted to try.

4

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
5

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 :smile: