Issue with Openquery and single quotes

  • Derek.Moore

    SSC Veteran

    Points: 262

    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

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    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

    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

    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

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

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