• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

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?
0
maximus1974
Asked:
maximus1974
1 Solution
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now