Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue with Openquery and single quotes Expand / Collapse
Author
Message
Posted Thursday, April 29, 2010 2:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 17, 2014 12:37 PM
Points: 2, Visits: 105
I am working with an Oracle 10G db that is part of a "packaged" application and I have no control over it. I have created a linked server and am able to successfully use openquery to retrieve data.

The issue I have run into is my inability to use single quotes without terminating the openquery statement. Using double quotes in the oracle query causes it to fail so I am stuck trying to find a way to escape the single quotes.

Has anyone encountered this before?

Thanks for your help,
Derek
Post #913259
Posted Thursday, April 29, 2010 2:25 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
a mix of parameters and dynamically generated sql statement does the trick... check here for details http://www.xdevsoftware.com/blog/post/Use-Parameters-with-OPENQUERY-in-SQL.aspx

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #913268
Posted Tuesday, June 8, 2010 11:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 17, 2011 3:17 PM
Points: 11, Visits: 96
You would also use single quotes for escape sequence.

Lets say you have a query like this in Oracle:

SELECT employee from Departments where employee='John'

In order to have it run in SQL Server as a Linked Server you will do this:

SELECT employee from OpenQuery(LinkedServerName,'SELECT employee from Departments where employee=''John''')

Hope this will help :)

Post #934151
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse