Need help with the proper SQL syntax when querying a linked Server

I am currently using this statement to query a linked Server: 
INSERT INTO WOMAST01
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01')

Open in new window

and it works fine.

I want to add a WHERE clause to filter by date using the RECDATE field. I try 
INSERT INTO WOMAST01
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01 WHERE RECDATE > '20140101'')

Open in new window


but it is not working, error received:  Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '20140101'.

What is the correct syntax?
maximus1974Asked:
Who is Participating?
 
Russ SuterCommented:
You're not properly escaping the single quotes. It should look like this:
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01 WHERE RECDATE > ''20140101''')

Open in new window

Quick explanation...
Single quotes inside a quoted string are represented by a double single quote (''). NOTE: Not a double quote (") which looks almost identical in most fonts.

So... if you need quoted text inside quoted text it would be like this:
'The quick brown ''fox'' jumps over a lazy dog'
 -- OR --
'The quick brown fox jumps over a lazy ''dog'''

In your case the quoted text is at the end of the quoted text (as in my 2nd example) so you end up with 3 single quotes in a row. It looks a little odd at first but once you parse it it begins to make sense.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here