August 28, 2017 at 6:49 pm
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
August 29, 2017 at 4:28 am
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
August 29, 2017 at 9:40 am
If youSET 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