SQL to check for Special Character works, but Fails in Automation

I have a SQL Script looking for a special CHR:
WHERE DESCR254_MIXED LIKE ('%%')
(Doesnt display here)

But it works... put it to Automation to flag rows that contain this non-printable special CHR to and Excel file and email it... it fails to export to Excel with the following error:
WHERE DESCR254_MIXED LIKE ('%%')
2024-06-24 12:31:43 PM: PODESCR_1 - '', hexadecimal value 0x02, is an invalid character. Line 3, position 30.

2024-06-24 12:31:43 PM: '', hexadecimal value 0x02, is an invalid character. Line 3, position 30.

How do I get around this?
Any Ideas? I can run it manually but would like it to be automated to spit out the rows meeting this Criteria to show the issues.

Using Toad DataPoint 6.2.3.25681

First of all, welcome to the forum for TDP.

Not sure if Excel is the culprit here (e.g. doesn't like the special char code) or TDP's Automation engine, etc. but couple options to consider:

  1. Which Export capability in TDP are you using in the Automation Designer to perform the export? Select To File? Export Wizard? Do you get the same result when using either of these?

  2. What happens if you export to a different target format type? Say, CSV, for example. Still get the same error?

  3. Maybe another option is to save the result set (e.g. the rows containing the offending 0x02 char) to your Local Storage, and then export the rows in that Local Storage table directly to Excel (assuming that Excel isn't the one complaining).

Thanks Gary. I did some further trouble shooting and I was adding another query to an existing Automation. I tried creating a new one just for this one new Query.

It appears I cannot even save the automation (or run it) as long as I have SQL code entered looking for that non-Printable Character. It throws and long winded error:
Could not Serialize 'SqlScriptEmbed' on object type of 'Quest.Toad.Workflow.Activities.Database.SelectDataActivity' as Simple Property. Please Override SerializeToString() Mehod in your seriazler or checkout the exceptions thrown by SerialzeToString() method.

When I remove the special Character I'm searching for it works. Right now the only option I see is to write code that looks not for that particular Character but any Non-printable character in my text field.

The non Printable Character actually shows as a ASCII ? surrounded by a box. Toad Shows it as Blank and it executes fine and pulls back the results but the Automation seems to choke on it. I have tried the Wizard, Select to Export and email... XLS and CSV... it always chokes with that Paste of the Character.

image

I found a work around:
WHERE INSTR(DESCR254_MIXED, CHR(2)) > 0;

If I do not have that Special CHR pasted then it does not Choke. I am just Surpized it runs as SQL but the Automation Chokes on it.

Glad you found a work-around John.