Search the VMware Knowledge Base (KB)
View by Article ID

Various tasks fail while calling stored procedures with errors: "Error executing query usp_<SP Name>" (2089503)

  • 3 Ratings

Symptoms

  • After upgrading or migrating to vRealize Automation (formerly known as vCloud Automation Center) 6.1 or 6.2, 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'."

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

This issue is resolved in vRealize Automation 6.2.2, available at VMware Downloads.
To work around this issue if you do not want to upgrade, enable MSDTC on all vRealize Automation and associated SQL servers:
  1. Log in to the SQL server or cluster primary node.
  2. Navigate to Start > Administrative Tools and select Component Services.
  3. Navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator.

    • For Local Standalone DTC, right-click Local DTC and click Properties
    • Click Clustered DTCs
    • Right-click the named clustered DTC and click Properties.

  4. Click the Security tab.
  5. Select the Network DTC Access option.
  6. Select the Allow Remote Clients option.
  7. Select Allow Outbound and Allow Inbound options.
  8. Select the Incoming Caller Authentication option.
  9. Click OK.
Notes:
  • If you believe that your Windows CID's are the same on IaaS and SQL servers, use the following page to help resolve your issue:  https://support.microsoft.com/en-us/kb/306843
  • It is important to note that the Microsoft Distributed Transaction Coordinator (MSDTC) is uninstalled and reinstalled if the SQL/vRealize Automation virtual machine is cloned on which the MSDTC is configured. The reason for this is that the original and cloned virtual machine have the same GUID 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.

    Note: The preceding link was correct as of October 31, 2014. If you find the link is broken, provide feedback and a VMware employee will update the link.

  • Even though the Microsoft Distributed Transaction Coordinator is enabled, the distributed transaction may fail if the firewall is turned on.

See Also

Request a Product Feature

To request a new product feature or to provide feedback on a VMware product, please visit the Request a Product Feature page.

Feedback

  • 3 Ratings

Did this article help you?
This article resolved my issue.
This article did not resolve my issue.
This article helped but additional information was required to resolve my issue.

What can we do to improve this information? (4000 or fewer characters)




Please enter the Captcha code before clicking Submit.
  • 3 Ratings
Actions
KB: