Semicolon removal in MSSQL scripts

[Tamas Virag]

Hi All,

I would like to run the following command using Automic SQL Job. I have noticed that regardless of whether I pass the MSSQL script in a variable or type it directly into the editor, the system removes the semicolon at the end of the line, which means that the script is passed to the database without the semicolon, and therefore cannot be executed.

The script in question is as follows:

MERGE incidents AS target USING (SELECT '1234' AS id_number, '2028-01-01 00:00:00' AS incident_date, 'ABCD-1' AS itsd_ticket, 'Some text' AS event_description, 'Some other text' AS event_type, 'H' AS severity, 'ABC' AS bsr_classification, 'Some text' AS handling, GETDATE() AS load_date) AS source ON target.id_number = source.id_number WHEN MATCHED THEN UPDATE SET incident_date = source.incident_date, itsd_ticket = source.itsd_ticket, event_description = source.event_description, event_type = source.event_type, severity = source.severity, bsr_classification = source.bsr_classification, handling = source.handling, load_date = source.load_date WHEN NOT MATCHED THEN INSERT (id_number, incident_date, itsd_ticket, event_description, event_type, severity, bsr_classification, handling, load_date) VALUES (source.id_number, source.incident_date, source.itsd_ticket, source.event_description, source.event_type, source.severity, source.bsr_classification, source.handling, source.load_date);

MERGE incidents AS target USING (SELECT '1235' AS id_number, '2028-01-01 00:00:00' AS incident_date, 'ABCD-2' AS itsd_ticket, 'Some text 2' AS event_description, 'Other text' AS event_type, 'M' AS severity, 'Just text' AS bsr_classification, 'Final text' AS handling, GETDATE() AS load_date) AS source ON target.id_number = source.id_number WHEN MATCHED THEN UPDATE SET incident_date = source.incident_date, itsd_ticket = source.itsd_ticket, event_description = source.event_description, event_type = source.event_type, severity = source.severity, bsr_classification = source.bsr_classification, handling = source.handling, load_date = source.load_date WHEN NOT MATCHED THEN INSERT (id_number, incident_date, itsd_ticket, event_description, event_type, severity, bsr_classification, handling, load_date) VALUES (source.id_number, source.incident_date, source.itsd_ticket, source.event_description, source.event_type, source.severity, source.bsr_classification, source.handling, source.load_date);

What should I do to prevent Automic from removing the semicolon from the end of SQL commands?

Thanks a lot!

Kind regards

Tamas Virag

------------------------------
Tamas Virag
------------------------------

[Tamas Virag]

One additional comment, I have the same issue on Automic 24.3 and 24.4 as well.

------------------------------
Tamas Virag
------------------------------

[Michael Dolinek]

Hi @Tamas Virag

To help us understand exactly what is happening with your SQL job and find the problem, please follow these steps:

1. Update System Settings:
   • Open UC_CLIENT_SETTINGS
   • Find the setting (or "key") named EXT_REPORTS
   • Set/add the value for EXT_REPORTS to SQL_VAR
2. Run the SQL Job Again:
   • Execute the SQL job one more time
3. Check for the New Report:
   • After the SQL job finishes, you will find a new report
   • This new report is named "Agent log"

What the "Agent log" shows:

• This report will list all the SQL statements that the job executed
• It should also provide important clues about what might be causing the issue

Hope this helps!

Michael

see https://docs.automic.com/documentation/webhelp/english/ALL/components/DOCU/24.4.0/Automic%20Automation%20Guides/Content/AWA/Variables/UC_CLIENT_SETTINGS/UC_CLIENT_EXT_REPORTS.htm

------------------------------
Michael K. Dolinek

Engineering Program Manager | Agile Operation Division
Broadcom Software
------------------------------

[Tamas Virag]

Dear Michael,

I applied the settings that you mentioned and the agent log contains the following message right after the SQL script:

2025-07-23 09:24:48 A MERGE statement must be terminated by a semi-colon (;).

I checked the SQL script that I have and it contains the semi-colon character, but Automic somehow cuts that at the end of the last line. The agent log contains the SQL script as well, without the semicolon, as you can see below.

At the end of the line the semicolon is missing.

In the monitor I still can see that the SQL script had the semicolon

If you have any other idea, please let me know.

Best regards,

Tamas

------------------------------
Tamas Virag
------------------------------

[Mike Halliday]

The default behaviour is to convert ';' characters into separate commands within the job - as seen on the right side pane.

So this example would become 2 commands and the trailing semicolon will be stripped.

PRINT 'Hello';
PRINT 'World!';

...will appear like this in the commands pane

The work around is to edit the command and re-add the semi-colon at the end (without a new line after it).  In my example, the second line kept the initial semicolon as there wasn't a new line character after it.