Issue with Openquery and single quotes

Question

Issue with Openquery and single quotes

Derek.Moore

SSC Veteran

Points: 262
More actions
April 29, 2010 at 2:09 pm

#220943

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

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.

hasan.askari Mr or Mrs. 500 Points: 535 More actions · Answer 2

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 🙂

msdhuliawala SSC Journeyman Points: 83 More actions · Answer 3

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