ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.
Deleting unnecessary CP tables in MSSQL
Article ID: 87897
Updated On:
Products
CA Automic Workload Automation - Automation Engine
Issue/Introduction
E.g, you had 9 CPs in your system. Now you don't need som many CPs any more and want to delete CPs CP006 to CP009. The normal and correct way to do this is to delete the server objects in the GUI. This removes the CPs completely from the system. However, the MQCP-tables created for these CPs will not be deleted and remain in the database. This is in general no problem and has no side effects, but if you want to get rid of them, you can delete them manually by SQL-command.
For MS SQL, the command looks as following:
In the SELECT you have to enter the tables to delete in the where-clause. In this example, these are MQCP006 to MQCP009.
Check with
if the tables are gone.
For MS SQL, the command looks as following:
DECLARE @tableName varchar(50), @dsql nvarchar(95)
DECLARE cur CURSOR FOR
SELECT name FROM sys.Tables
WHERE name in ('MQCP006', 'MQCP007', 'MQCP008', 'MQCP009')
OPEN cur
FETCH NEXT FROM cur INTO @tableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @dsql = 'DROP TABLE ' + @tableName
EXEC sp_executesql @dsql
FETCH NEXT FROM cur INTO @tableName
END
CLOSE cur DEALLOCATE cur
GO
DECLARE cur CURSOR FOR
SELECT name FROM sys.Tables
WHERE name in ('MQCP006', 'MQCP007', 'MQCP008', 'MQCP009')
OPEN cur
FETCH NEXT FROM cur INTO @tableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @dsql = 'DROP TABLE ' + @tableName
EXEC sp_executesql @dsql
FETCH NEXT FROM cur INTO @tableName
END
CLOSE cur DEALLOCATE cur
GO
In the SELECT you have to enter the tables to delete in the where-clause. In this example, these are MQCP006 to MQCP009.
Check with
SELECT name FROM sys.Tables WHERE name LIKE 'MQCP%'
if the tables are gone.
Cause
Cause type:
By design
By design
Environment
Release: AUTWAB99000-10.0-Automic Workload Automation-Base Edition
Component:
Component:
Resolution
This field was added on 30/03/2017. This article has not been updated yet. Refer to the "Description" or "Workaround" sections for solution information.
Feedback
Yes
No
Powered by
