We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
Need help with the proper SQL syntax when querying a linked Server
Posted on 2016-09-13
I am currently using this statement to query a linked Server:
I want to add a WHERE clause to filter by date using the RECDATE field. I try
but it is not working, error received: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '20140101'.
What is the correct syntax?
INSERT INTO WOMAST01
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01')
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'')
but it is not working, error received: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '20140101'.
What is the correct syntax?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
1 Comment
Active today
You're not properly escaping the single quotes. It should look like this:
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.
SELECT * FROM OPENQUERY(ATQAV, 'SELECT * FROM WOMAST01 WHERE RECDATE > ''20140101''')
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs).
Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month11 days, 21 hours left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.