Issue with Openquery and single quotes

  • 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,


  • a mix of parameters and dynamically generated sql statement does the trick... check here for details

    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.
  • 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 🙂

  • 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+'')



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

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