Various tasks fail while calling stored procedures with errors: "Error executing query usp

Products

VMware Aria Suite

Issue/Introduction

Symptoms:

After upgrading or migrating to vRealize Automation (formerly known as vCloud Automation Center) 6.1, 6.2.x or later, various tasks (such as data collections, provisioning, destroying machines and other tasks) fail.
After a new installation of vRealize Automation 6.1 or later, you see tasks failing due to stored procedure failures.
In the vRealize Automation admin log files, you may see Microsoft Distributed Transaction Coordinator (MSDTC) errors.
You see errors similar to these within Infrastructure > Monitoring > Logs:

Error processing workflow creation Error executing query usp_SearchInitializingRequestVirtualMachines Inner Exception: Error executing query usp_SelectGroup
DataBaseStatsService: ignoring exception: Error executing query usp_SelectAgent Inner Exception: Error executing query usp_SelectAgentCapabilities
stack trace at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReader(RecordReader`1 recordReader, String storedProcedure, String whereClause, Boolean firstRecordOnly, SqlParameter[] collection) at DynamicOps.VMPS.DataAccess.DataAccessLayer.SearchInitializingRequestVirtualMachines(Int32 resultCount) at DynamicOps.VMPS.DataAccess.DataAccessLayer.<>c__DisplayClass181.
In your IaaS Server Event Logs, you see the error similar to:

A caller has attempted to propagate a transaction to a remote system, but MSDTC network DTC access is currently disabled on machine 'SQL_MACHINE'."

Environment

VMware vRealize Automation 7.2.x
VMware vRealize Automation 7.1.x
VMware vRealize Automation 6.2.x
VMware vCloud Automation Center for Server 6.1.x
VMware vRealize Automation 7.0.x
VMware vRealize Automation 7.3.x

Cause

This issue occurs due to an MSDTC failure at the SQL server side as MSDTC is either not configured or stopped on the SQL server or cluster. Another possible cause for this is if your IaaS servers and SQL database server are cloned and their Windows security identifier has not been changed (CID) through sysprep or other method.

Resolution

To resolve this issue, execute the following procedure:

Log in to the SQL server or cluster primary node.
Stop the Manager Service.
Stop the SQL Server service.
Open a command prompt on the machine with the Manager Service and issue the following command:

msdtc -uninstall
Open a registry editor on the Manager Service and delete the following keys if they exist:

HKLM/Software/Microsoft/Software/MSDTC
HKLM/System/CurrentControlSet/Services/MSDTC
HKEY_CLASSES_ROOTCID
Reboot the machine with the Manager Service.
Open a command prompt on the machine with the Manager Service and issue the following command:

msdtc -install
Perform steps 3-6 on the machine running the SQL Server.

This procedure generates new CID values for MSDTC on both servers.

Notes:

For clustered MSDTC instances make sure the settings are enabled on both the Local DTC and the clustered DTC on all hosts in the cluster.
If you believe that your Windows CID's are the same on IaaS and SQL servers, see the Microsoft Knowledge Base article 306843.
It is important to note that the Microsoft Distributed Transaction Coordinator (MSDTC) is uninstalled and reinstalled if the SQL Server and vRealize Automation virtual machines are cloned from the same base image without running sysprep before cloning (not a best practice). The reason for this is that the original and cloned virtual machine have the same CID values that causes communication failures between the vRealize Automation and the SQL server.
Also, after reinstalling MSDTC, you need to set the Distributed Transaction Coordinator service to Automatic (Delayed Start) and turn it on.

For additional information, see Troubleshooting Problems with MSDTC.

Disclaimer: VMware is not responsible for the reliability of any data, opinions, advice, or statements made on third-party websites. Inclusion of such links does not imply that VMware endorses, recommends, or accepts any responsibility for the content of such sites.

Even though the Microsoft Distributed Transaction Coordinator is enabled, the distributed transaction may fail if the firewall is turned on.
The Manager Service machine hosting the Manager Service should be able to resolve the NETBIOS name of the IaaS MS-SQL DB machine.
If it is not achievable due to any reason, the workaround will be to add the NETBIOS name of the DB machine to the /etc/hosts file of the Manager Service machine and restarting Manager Service.

Additional Information

在调用存储过程时执行的各种任务失败并显示以下错误：“执行查询 usp_<SP Name> 时出错 (Error executing query usp_<SP Name>)”

Impact/Risks:
Directly modifying the Windows registry can harm your computer. Take a back up and do at your own risk.