Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
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 !
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

578 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