topic Re: Stream component failed at subtask and Failed to create DDL events trigger and There is already an object named 'attrep_mscdc_ddl_catcher' in the in Qlik Replicate

Stream component failed at subtask and Failed to create DDL events trigger and There is already an object named 'attrep_mscdc_ddl_catcher' in the data

ramu123 — Fri, 31 Mar 2023 10:41:53 GMT

Hi Team ,

Below error message I have received for log stream.

SQL_ERROR SqlState: 42S01 NativeError: 2714 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'attrep_mscdc_ddl_catcher' in the database. Line: 1 Column: -1.

But Could not find in target database with this name ' 'attrep_mscdc_ddl_catcher'.

Could you please suggest what could be reason, where to check? is this error related source database or target database?

is there any impact?

Thanks

Ramu

Re: Stream component failed at subtask and Failed to create DDL events trigger and There is already an object named 'attrep_mscdc_ddl_catcher' in the

Heinvandenheuvel — Fri, 31 Mar 2023 23:32:57 GMT

Surely 'ddl_catcher' is related to the source DB. No doubt!

I would expect it to be under programmability - similar to the Attunity provided scalar values functions dbo.attrep_fn_LsnSegmentToHexa and dbo.attrep_fn_NumericLsnToHexa

If you find it , delete it and try again. I think this is a relatively new addition and the security attributes might not be compatible with what there is. Not sure.

Hein.

Re: Stream component failed at subtask and Failed to create DDL events trigger and There is already an object named 'attrep_mscdc_ddl_catcher' in the

ramu123 — Sat, 01 Apr 2023 17:14:33 GMT

Hi,

This below error I'm getting exactly. Could you explain clearly so that it is very helpful to me .

Qlik replicate version is 2022.5.0.499 and Qlik Enterprise Manger Version is 2021.11.0.151.

Stream component 'st_0_PROD M2C ISU' terminated
Stream component failed at subtask 0, component st_0_PROD M2C ISU
Error executing command
Failed to create DDL events trigger
RetCode: SQL_ERROR SqlState: 42S01 NativeError: 2714 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'attrep_mscdc_ddl_catcher' in the database. Line: 1 Column: -1
Failed (retcode -1) to execute statement: '
CREATE TRIGGER [attrep_mscdc_ddl_catcher]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
SET NOCOUNT ON;
declare @x XML
declare @ddlOperation varchar(32)
declare @schemaName nvarchar(128)
declare @tableName nvarchar(128)
declare @loginName varchar(64)
declare @Username varchar(64)
declare @eventTime varchar(32)

declare @orgT varchar(256)
declare @cdcT varchar(256)

declare @orgObjId integer
declare @cdcObjId integer

set @x = EVENTDATA();
set @ddlOperation = @x.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(32)')
set @schemaName = @x.value(N'(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)')
set @tableName = @x.value(N'(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
set @loginName = @x.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(64)')
set @Username = @x.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(64)')
set @eventTime = @x.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(32)')

set @orgT = @schemaName+'.' + @tableName
set @cdcT = 'cdc.' + @schemaName+'_' + @tableName+'_CT';

set @orgObjId = OBJECT_ID(@orgT,N'U')
if @orgObjId is null
set @orgObjId = 0;
set @cdcObjId = OBJECT_ID(@cdcT,N'U')
if @cdcObjId is null
set @cdcObjId = 0;

INSERT INTO [dbo].[attrep_mscdc_ddl_history]
([ddlOperation], [schemaName], [tableName], [loginName], [userName], [eventTime], [orgObjId], [cdcObjId])
VALUES
(@ddlOperation,@schemaName,@tableName,@loginName,@userName,@eventTime,@orgObjId,@cdcObjId)

delete from [dbo].[attrep_mscdc_ddl_history] where ddlId = (select max(ddlId) from [dbo].[attrep_mscdc_ddl_history])
END;
'

Thanks

Ramu

Re: Stream component failed at subtask and Failed to create DDL events trigger and There is already an object named 'attrep_mscdc_ddl_catcher' in the

Dana_Baldwin — Mon, 03 Apr 2023 16:50:13 GMT

Hi @ramu123

The software is trying to create a trigger on the source that already exists, not sure why. I searched our solution database but did not find helpful results. Can you please open a support case and attach a diagnostics package for the task, so we can look into this more deeply?

Thanks,

Dana

Re: Stream component failed at subtask and Failed to create DDL events trigger and There is already an object named 'attrep_mscdc_ddl_catcher' in the

Heinvandenheuvel — Mon, 03 Apr 2023 17:52:41 GMT

[duplicate of reply to: "I'm not able to create Qlik support case" ]

@ramu123 - thanks for getting down to the failing statement: "CREATE TRIGGER [attrep_mscdc_ddl_catcher]
ON DATABASE"

Was there an object existance test before the create ?

Is this (also) happening, or reproducible in DEV (non-production - as the Stream component name 'st_0_PROD M2C ISU' suggests production.

You should see the trigger in SMSS under databases - <source-db-name> - Programmability - Database_Triggers.

As a workaround can you just pre-delete the trigger and try again?

DROP TRIGGER [attrep_mscdc_ddl_catcher] ON DATABASE;

Hein.