Replace Apostrophies for Openquery??

[aljubicic]

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.

Thanks
Anthony

 

[sguslan]

Post the entire code please ... ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[Vinodk]

You can surely use quotes with OpenQuery(). If you post us the complete code then we can surely debug the problem. Here is an example that works perfectly.

SELECT * FROM OPENQUERY([servername], ' SELECT * FROM northwind.dbo.employees WHERE employeeID = ''1'' ')

HTH,
Vinod Kumar

 

[aljubicic]

The code for the trigger is set out below...

CREATE TRIGGER ARAUTHORITY_RES ON dbo.Residential
FOR INSERT AS
IF EXISTS
(Select * from Inserted where Inserted.Authority_Updated = 'N')

/**(Select Inserted.Authority_Updated from Inserted) = 'N' **/

BEGIN

BEGIN DISTRIBUTED TRANSACTION
SET XACT_ABORT ON


DECLARE @SURNAME VARCHAR(100)
DECLARE @GIVEN1 VARCHAR(25)
DECLARE @GIVEN2 VARCHAR(25)
DECLARE @INITIALS VARCHAR(5)
DECLARE @INITIAL VARCHAR(5)
DECLARE @TITLE VARCHAR(5)
DECLARE @SEX VARCHAR(1)
DECLARE @MARITAL VARCHAR(1)
DECLARE @PENSION_TYPE VARCHAR(3)
DECLARE @PENSION_NO VARCHAR(10)
DECLARE @DOB VARCHAR(10)
DECLARE @POSTCODE VARCHAR(6)
DECLARE @ENTIRE_NAME VARCHAR(50)
DECLARE @STREET_ADDRESS VARCHAR(50)
DECLARE @SUBURB_ADDRESS VARCHAR(50)
DECLARE @HOME VARCHAR(20)
DECLARE @MOBILE VARCHAR(20)
DECLARE @WORK VARCHAR(20)
DECLARE @FAX VARCHAR(20)
DECLARE @EMAIL VARCHAR(128)
DECLARE @WEBSITE VARCHAR(130)
DECLARE @DATE VARCHAR(20)
DECLARE @SERKEY VARCHAR(20)
DECLARE @ALPHAKEY VARCHAR(12)
DECLARE @TSQL VARCHAR(1000)
DECLARE @NAMEID VARCHAR(10)

IF (Select Inserted.SName2 from Inserted) = ''
SET @SURNAME = (Select Inserted.SName1 from Inserted)
ELSE
SET @SURNAME = (Select Inserted.SName1 from Inserted) + ' ' + (Select Inserted.SName2 from Inserted)

SET @SURNAME = REPLACE(@SURNAME, '''', '''''')

SET @GIVEN1 = (Select Inserted.GName1 from Inserted)
SET @GIVEN2 = (Select Inserted.GName2 from Inserted)

IF (Select Inserted.GName2 from Inserted) = ''
BEGIN
SET @INITIALS = SUBSTRING((Select Inserted.GName1 from Inserted),1,1)
SET @INITIAL = SUBSTRING((Select Inserted.GName1 from Inserted),1,1)
END
ELSE
BEGIN
SET @INITIALS = SUBSTRING((Select Inserted.GName1 from Inserted),1,1) + ' ' + SUBSTRING((Select Inserted.GName2 from Inserted),1,1)
SET @INITIAL = SUBSTRING((Select Inserted.GName1 from Inserted),1,1) + '' + SUBSTRING((Select Inserted.GName2 from Inserted),1,1)
END

SET @ALPHAKEY = SUBSTRING((Select Inserted.SName1 from Inserted), 1, 9)
SET @ALPHAKEY = UPPER(@ALPHAKEY + ' ' + @INITIAL)



SET @TITLE = (Select Inserted.Salutation from Inserted)
SET @SEX = (Select Inserted.Sex_Flag from Inserted)
SET @MARITAL = (Select Inserted.MStatus_Flag from Inserted)
SET @PENSION_TYPE = CONVERT(VARCHAR ,(Select Inserted.Pension_Type from Inserted))
SET @PENSION_NO = (Select Inserted.Pension_Number from Inserted)
SET @DOB = CONVERT(VARCHAR, (Select Inserted.Date_Of_Birth_Day from Inserted)) + '-' + CONVERT(VARCHAR, (Select Inserted.Date_Of_Birth_Month from Inserted)) + '-' + CONVERT(VARCHAR, (Select Inserted.Date_Of_Birth_Year from Inserted))
SET @POSTCODE = (Select Inserted.Postcode from Inserted)
SET @ENTIRE_NAME = (Select Inserted.Salutation from Inserted) + ' ' + @INITIALS + ' ' + (Select Inserted.SName1 from Inserted)
SET @STREET_ADDRESS = (Select Inserted.AddressLne1 from Inserted)
SET @SUBURB_ADDRESS = (Select Inserted.Suburb from Inserted) + ' ' + (Select Inserted.State from Inserted) + ' ' + (Select Inserted.Postcode from Inserted)
SET @HOME = (Select Inserted.Contact_Number_Home from Inserted)
SET @MOBILE = (Select Inserted.Contact_Number_Mobile from Inserted)
SET @WORK = (Select Inserted.Contact_Number_Work1 from Inserted)
SET @FAX = (Select Inserted.Contact_Number_Fax from Inserted)
SET @EMAIL = (Select Inserted.Contact_Internet_Email from Inserted)
SET @WEBSITE = (Select Inserted.Contact_Internet_Web from Inserted)
SET @DATE = SUBSTRING((Select Inserted.Creation_Date from Inserted) + ' ' + (Select Inserted.Creation_Time from Inserted), 1, 19)
/**SET @DATE = CONVERT(DATETIME, (Select Inserted.Creation_Date from Inserted) + ' ' + (Select Inserted.Creation_Time from Inserted))**/
SET @NAMEID = (Select Inserted.NameID from Inserted)

SET @TSQL = 'INSERT INTO ' +
'OPENQUERY(AUTHTEST,''Select nar_num, ser_key, alp_key, nam_fam, nam_gv1, nam_gv2, nam_ini, nam_ttl, snr_flg, sex, mar_stu, pen_typ, pen_num, ' +
'dte_brt, hou_add, pst_cde, fmt_nm1, fmt_nm2, res_ad1, res_ad2, res_ad3, dpid, barcode, pst_add_flg, ' +
'hme_ph1, hme_ph2, mob_phn, bus_phn, bus_fax, bus_com, bus_dox, eml_add, not_pre, acn_num, abn_num, hme_pge, nam_pri, add_pri, phn_pri, ' +
'brt_pri, stu_flg, cre_dte_tme, mod_dte_tme, mod_opr from aunrmast'') ' +
'VALUES(0, '''', ''' + @ALPHAKEY + ''', ''' + @SURNAME + ''', ''' + @GIVEN1 + ''', ''' + @GIVEN2 + ''', ''' + @INITIALS + ''', ''' + @TITLE + ''', '''', ''' + @SEX + ''', ''' + @MARITAL + ''', ''' + @PENSION_TYPE + ''', ''' + @PENSION_NO + ''', ' +
'''' + @DOB + ''', '''', ''' + @POSTCODE + ''', '''', ''' + @STREET_ADDRESS + ''', ''' + @SUBURB_ADDRESS + ''', '''', '''', ''' + @NAMEID + ''', '''', ''N'', ''' + @HOME + ''', '''', ''' + @MOBILE + ''', ''' + @WORK + ''', ' +
'''' + @FAX + ''', '''', '''', ''' + @EMAIL + ''', '''', '''', '''', ''' + @WEBSITE + ''', '''', '''', '''', '''', ''Y'', ''' + @DATE + ''', ''' + @DATE + ''', 99)'




EXEC(@TSQL)

IF @@error <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION



END


I have only added the replace function for @SURNAME however I want to add it to other variables later once I find an answer to this problem.

Thanks for your help on this.

Anthony

 

[sguslan]

Try it this way. Many times i find it hard to code any tsql in a string and pass it to EXEC. It becomes daunting to find the problem with all the quirks that can happen due to the improperly aligned single quotes.

SET @TSQL = 'INSERT INTO OPENQUERY(AUTHTEST,''Select nar_num, ser_key, alp_key, nam_fam, nam_gv1, nam_gv2, nam_ini, nam_ttl, snr_flg, sex, mar_stu, pen_typ, pen_num,
dte_brt, hou_add, pst_cde, fmt_nm1, fmt_nm2, res_ad1, res_ad2, res_ad3, dpid, barcode, pst_add_flg,
hme_ph1, hme_ph2, mob_phn, bus_phn, bus_fax, bus_com, bus_dox, eml_add, not_pre, acn_num, abn_num, hme_pge, nam_pri, add_pri, phn_pri, brt_pri, stu_flg, cre_dte_tme, mod_dte_tme, mod_opr from aunrmast'')
VALUES(0, '''', '''
SET @TSQL = @TSQL + '@ALPHAKEY + '', '''
SET @TSQL = @TSQL + @SURNAME
SET @TSQL = @TSQL + ''', '''
SET @TSQL = @TSQL + @GIVEN1
SET @TSQL = @TSQL + ''', '''
SET @TSQL = @TSQL + @GIVEN2
SET @TSQL = @TSQL + ''', '''
SET @TSQL = @TSQL + @INITIALS
SET @TSQL = @TSQL + ''', '''
SET @TSQL = @TSQL + @TITLE
SET @TSQL = @TSQL + ''', '''', '''
SET @TSQL = @TSQL + @SEX
SET @TSQL = @TSQL + ''', '''
SET @TSQL = @TSQL + @MARITAL
SET @TSQL = @TSQL + ''', '''
SET @TSQL = @TSQL + @PENSION_TYPE
SET @TSQL = @TSQL + ''', '''
SET @TSQL = @TSQL + @PENSION_NO
SET @TSQL = @TSQL + ''', '
SET @TSQL = @TSQL + ''''
SET @TSQL = @TSQL + @DOB
SET @TSQL = @TSQL + ''', '''', '''
SET @TSQL = @TSQL + @POSTCODE + ''', '''', '''
SET @TSQL = @TSQL + @STREET_ADDRESS
SET @TSQL = @TSQL + ''', '''
SET @TSQL = @TSQL + @SUBURB_ADDRESS
SET @TSQL = @TSQL + ''', '''', '''', '''
SET @TSQL = @TSQL + @NAMEID
SET @TSQL = @TSQL + ''', '''', ''N'', '''
SET @TSQL = @TSQL + @HOME
SET @TSQL = @TSQL + ''', '''', '''
SET @TSQL = @TSQL + @MOBILE
SET @TSQL = @TSQL + ''', '''
SET @TSQL = @TSQL + @WORK
SET @TSQL = @TSQL + ''', '
SET @TSQL = @TSQL + ''''
SET @TSQL = @TSQL + @FAX
SET @TSQL = @TSQL + ''', '''', '''', '''
SET @TSQL = @TSQL + @EMAIL
SET @TSQL = @TSQL + ''', '''', '''', '''', '''
SET @TSQL = @TSQL + @WEBSITE
SET @TSQL = @TSQL + ''', '''', '''', '''', '''', ''Y'', ''' SET @TSQL = @TSQL + @DATE
SET @TSQL = @TSQL + ''', '''
SET @TSQL = @TSQL + @DATE
SET @TSQL = @TSQL + ''', 99)'

________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[aljubicic]

But this doesn't resolve my first issue which is....

If one of these variables obtains a value with an apostrophe in it, it will error. That is lets say that @SURANME obtains a value of &quot;O'Reilly&quot;. The apostrophe in the name will cause the openquery statement to error. If the value was &quot;OReilly&quot; without an apostrophe it will work fine, so my statement is fine with the right number of apostrophes, but it will become out of sync when the values obtained for the variables contain apostrophes. How do I get around this problem??

Regards
Anthony

 

[SQLSister]

look at the set quoted identifier command in Books online and see if this will do what you want. You will have to use &quot; instead of ' if you do this.