Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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,065, 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
Posted Wednesday, June 22, 2016 8:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 9, 2016 9:51 AM
Points: 1, Visits: 36
Hi ,


with your second query using openquery give me solution for using paramerter

my query is something like this.
declare @birthdate varchar(8)
set @birthdate = CONVERT (varchar (8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1) ,112)
select * from openquery (linkedserver, 'select * from database.dbo.myname where birthdate= '+@birthdate+'')

Thanks
Mustafa
Post #1796489
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse