Openquery Update Single Quote Issue

  • Hi All,

    I am using Openquery to update a remote server but run into an issue where a quote exists in the Select statement returning the data to be updated.

    Example :

    I run this code to format the query to replace any single quotes:
     
    SELECT @tsql = 'UPDATE OPENQUERY(REMOTESERVERDB, ''select ID,
                    LastName
                   FROM Account
                   WHERE ID = ''''' + @ID +'''''
                             '')  
       SET LastName=''' + replace(@LastName,'''','''''') + '''
       where ID = ''' + @ID +'''
    '
    exec (@tsql)

    The error I get is:

    fails to query <MALFORMED_QUERY:
    LastName = 'O'Connor'

    The issue seems to be related to the data being returned from the select portion of the openquery. As it is returning the data that needs to be updated. The data in the variables have all been formatted to handle the single quote.

    How do I format this data so the single quote is handled correctly?

    Thanks  for  any  help in solving this.

    Gribbs

  • Hi All,

    I have resolved the issue.

    The issue was the ODBC  Driver I was using to connect to  the remote server.
    Apparently it was a known bug and all I had to do was update the driver.
    Once I updated the driver  the Openquery ran without any errors and updated correctly.

    Having said that, Openquery is still such a pain.
    But at least it's working and we are able to integrate with a remote server.

    Thanks .

    Gribbs

  • If you
    SET OUOTED_IDENTIER OFF;
    you can eliminate some of the pain.

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

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