Here is my query:
SELECT REPLACE([TEXT],'"','''') as EVENT from DB_name
This version of the query works in the DB connect editor, but does not work when saved as a job.
Adding an escape character:
SELECT REPLACE([TEXT],'\"','''') as EVENT from DB_name
Allows the query to work when saved, but does not replace the quotes at all.
I am trying to use a simple query that works in SQL. What am I missing?
Try using the CHAR() function which can be used to set a single ANSI character in your code. E.g. CHAR(34) will print a single double-quote ". To come up with ANSI codes, you can also use ANSI('"').
For your problem, you can use:
SELECT REPLACE([TEXT], CHAR(34) ,'''') as EVENT from DB_name
This should work on most database systems...