Does Automic add additional transactions to procedures executed from SQL Jobs?

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

When trying to use a stored procedure to maintain Indexes on all Databases on a SQL Server, following can be observed.
Running the stored Procedure in SQL Server Management Studio is working fine in all kinds of environments. Running the stored Procedure from Automic is resulting in an additional transaction and holding locks to all databases, which prevents users/apps from using the Databases until the whole job has finished.
 

Question: Why there is an additional Transaction here? Is there a difference between Automic and SSMS?
Question: What is different when running SQL from in an Automic SQL-Job?
Question: How can one change this? I.e. like in the SQL client, the SQL Job should cause no additional transaction.

 
E.g. a SQL monitoring tool can be used to see, that there is indeed an additional transaction "around" the procedure.
 
For reproduction, a simple procedure running on a test DB can be used. The sql-scripts attached can be used.

• Create a test-DB with ca. 1 mio. records (Test_Automic_TestDB.sql)
• Create a script for index rebuild (see Test_Automic_Alter_index.sql)
• Open two query windows in SSMS
• In one start the rebuild
• In the other one execute the test-select (Test_automic_SELECT.sql)
• In SSMS:
• After 10 sec. there is a short block while index rebuild is running, but after that they can select the data
• When executing in an SQL-Job:
• Select is possible until start of index-rebuild, but the table is afterwards locked until after the end of the 30 sec. delay.

Environment

Release: AOATAM99000-8.0-Automic-One Automation Tools-Application Manager
Component:

Resolution

If there is an additional transaction depends on the process. However, it can be avoided by adding the following command as 1^st line in the SQL Job
 

SET IMPLICIT_TRANSACTIONS OFF
 

This command must not be used for all SQL Jobs regardless. Therefore, SQL and DB knowledge is required.