Hi All,
I have trigger that sends data from one table to another. I need to make sure that if the data that needs to be sent across has an apostrophe, that it is replaced by two apostrophies so it adheres to the SQL syntax and that it doesn't error.
Is it possible to use the REPLACE function for this or is there some other way to get around this??
Can I write REPLACE(@.VARIABLE, ''', '''') for this or won't this work??
If someone can give me anysight into this and any code on how to do this would be great.
Thanks
AnthonyREPLACE(@.VARIABLE, '''', ''')
Showing posts with label sends. Show all posts
Showing posts with label sends. Show all posts
Wednesday, March 21, 2012
Replace Apostrophes for Openquery?
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
AnthonyYou are posting on multiple forums :)
Once again EXEC(), QUOTENAME()|||How do I use QUOTENAME() if I want to use a variable that has a value of "O'Reilly" for instance for the following OPENQUERY statement within the trigger
SET @.TSQL = 'INSERT INTO ' +
'OPENQUERY(AUTHTEST,''Select nar_num, ser_key, alp_key from aunrmast'') ' +
'VALUES(0, '''', ''' + @.VARIABLE + ''', 99)'
EXEC(@.TSQL)
Thanks for your help on this
Anthony|||select QUOTENAME('O''Reilly','''')sql
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
AnthonyYou are posting on multiple forums :)
Once again EXEC(), QUOTENAME()|||How do I use QUOTENAME() if I want to use a variable that has a value of "O'Reilly" for instance for the following OPENQUERY statement within the trigger
SET @.TSQL = 'INSERT INTO ' +
'OPENQUERY(AUTHTEST,''Select nar_num, ser_key, alp_key from aunrmast'') ' +
'VALUES(0, '''', ''' + @.VARIABLE + ''', 99)'
EXEC(@.TSQL)
Thanks for your help on this
Anthony|||select QUOTENAME('O''Reilly','''')sql
Subscribe to:
Posts (Atom)