The vCenter Server database Event Task Cleanup stored procedure fails to run
book
Article ID: 322242
calendar_today
Updated On:
Products
VMware vCenter Server
Issue/Introduction
Symptoms:
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:
Log in to the SQL Management studio.
In the Object Explorer window, navigate to the vCenter database > Programmability > Stored Procedures object.
Check and ensure that the stored procedures and tables are owned by the same schema.
If cleanup_events_tasks_proc is belongs to a different schema than the tables, right-click cleanup_events_tasks_proc and click Delete.
Log in to SQL Management Studio as a user with the schema where the tables belong.
Copy the stored procedure C:\Program Files\VMware\Infrastructure\VirtualCenter Server\cleanup_events_mssql.sqlfrom vCenter Server to the vCenter Server database server.
Click File > Open > File.
Select the cleanup_events_mssql.sqlfile.
Click OK.
Ensure that the selected database in the Available Database dropdown is the vCenter Server database.
Click Execute. If the job is created successfully, you see the message:
Command(s) Completed successfully
Right-click Event Task Cleanup and click Execute. Verify that it completes.