Replace Apostrophes for OpenQuery??

Posted on 2003-03-23
Medium Priority
Last Modified: 2012-08-14
Hi All,

I have trigger that sends data from one table to another.
I am using the Openquery statement within the trigger to send the data.

However when I Insert anything with an apostrophe into the table where the trigger resides it errors saying...

"Incorrect syntax near" where the apostrophe is and this puts out the number of Quotes in the Openquery Statement.

I use the Replace function written as  REPLACE(@VARIABLE, '''', '''''') to replace One Apostrophe with 2.
I'm not sure whether I can use this with Openquery or not / or whether the syntax is correct for Replace in conjunction with OpenQuery.

If someone can give me anysight into this and any code on how to do this would be great.

Question by:Hotstepper
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 13

Accepted Solution

ispaleny earned 100 total points
ID: 8193672
Openquery is a static statement. Use sp_executesql or EXEC().

Assisted Solution

xenon_je earned 100 total points
ID: 8193673
Can you post your exact code?
In general if you have characters of ', then you need to double them. The replace you wrote should work fine...

Here is some code example:
declare @t varchar(20)
declare @t2 varchar(20)
create table tmp1 (c1 varchar(30))
set @t='ab'''
set @t2 = @t + 'abc'
declare @ss varchar(200)
set @ss = 'insert into tmp1 values(''' + replace(@t,'''','''''') + ''')'
print @ss
select * from tmp1
select @t
select @t2
select replace(@t2,'''','''''')
select @t2
drop table tmp1
So as you can see the replace is good, but use it just where you need it. In my example I used replace only on the @t variable, not on the entire @ss variable.
Hope this helps..
LVL 13

Expert Comment

ID: 8193679
For replacing apostrophes, you can use QUOTENAME().

Good luck !
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

LVL 13

Expert Comment

ID: 8198890
I compared

[1]. QUOTENAME(@TestString,'''')
[2]. ''''+REPLACE(@TestString,'''','''''')+''''

[1] is 10 % faster than [2].

Expert Comment

ID: 9275612
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.
LVL 12

Expert Comment

ID: 11183667
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: ispaleny http:#8193672, xenon_je http:#8193673

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question