vCenter Server database event task cleanup stored procedure fails to run

Products

• VMware vCenter Server

Issue/Introduction

• The vCenter Server database Event Task Cleanup stored procedure fails to run
• The Job Activity monitor within the SQL Management Studio shows an error similar to:
  
  Msg 245, Level 16, State 1, Procedure cleanup_events_tasks_proc_KB, Line 49
Conversion failed when converting the nvarchar value 'true' to data type int.

Environment

• VMware vCenter Server 5.1.x
• VMware vCenter Server 5.0.x
• VMware vCenter Server 4.0.x
• VMware vCenter Server 5.5.x
• VMware vCenter Server 6.0.x

Cause

This issue can occur when the cleanup_events_mssql.sql stored procedure is owned by the dbo schema and the tables are owned by another schema (for example, vc_admin).

Resolution

The stored procedure needs to be in the same schema as the tables and views.

 

To resolve this issue:

1. Log in to the SQL Management studio.
2. In the Object Explorer window, navigate to the vCenter database > Programmability > Stored Procedures object.
3. Check and ensure that the stored procedures and tables are owned by the same schema.
4. If cleanup_events_tasks_proc is belongs to a different schema than the tables, right-click cleanup_events_tasks_proc and click Delete.
5. Log in to SQL Management Studio as a user with the schema where the tables belong.
6. Copy the stored procedure C:\Program Files\VMware\Infrastructure\VirtualCenter Server\cleanup_events_mssql.sql from vCenter Server to the vCenter Server database server.
7. Click File > Open > File.
8. Select the cleanup_events_mssql.sql file.
9. Click OK.
10. Ensure that the selected database in the Available Database dropdown is the vCenter Server database.
11. Click Execute. If the job is created successfully, you see the message:
    
    Command(s) Completed successfully
12. Right-click Event Task Cleanup and click Execute. Verify that it completes.