Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
COURSE of the MONTH
11 days, 23 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
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
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Course of the Month11 days, 23 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.