topic Re: Cannot remove the table from source end point (SQL server) replication in Qlik Replicate

Cannot remove the table from source end point (SQL server) replication

SaranyaK — Tue, 01 Mar 2022 18:43:57 GMT

The source end point (SQL server on AWS, incremental thru online tlogs) has a schema change, so we stopped the Qlik task and tried to remove the impacted table from replication but for some reason that table is not showing up in the publisher even though the table is removed from the task in Qlik. Any suggestions to do this is much appreciated

Table is not getting released from replication for data type changes , even after removing the table from the task in Qlik

There are several observations.

We observed two publication. One publication was created by Source end point DBA, other one was created by Qlik (not sure when). Does Qlik have the capability to create publication in the source?

“005” publication contains all the articles , but not allowing to remove the article.

Getting below error when trying to remove the article.

In “006” publication showing zero articles.

After creating a new task in Qlik, the tables started showing “006” publication. It also allowed to remove the articles from “006” , but still couldnot make the data change and the error is “Cannot alter the table 'GINameDataDT' because it is being published for replication.” Can you please advise if you encountered similar situation

Re: Cannot remove the table from source end point (SQL server) replication

Heinvandenheuvel — Tue, 01 Mar 2022 17:21:46 GMT

Hmm, this is tricky stuff mostly, if not totally under SQLserver control once Replicate had created the Article.

With an article in place, MS SQL disallows certain DML (Truncate!) and DDL (PK contraints), but it does allow adding or dropping columns best I know. What specific DDL was being tried?

You may want to google for non-Replicate SQL server hint. There are several For example this older topic: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/10654e4d-b506-4bc3-afc1-0da1bd2a62d7/alter-table-not-possible?forum=sqlreplication

Personally I'm was pestered with a table I could not drop because it had a publication, but I could not drop that because the server (my laptop) was renamed. After many googles and system table alters I just blew away the whole server and started fresh, but that's rarely a realistic option. :-).

Not much help I'm afraid, just sympathizing and nudging you towards SQL server resources to help with this.

Also, ask a colleague to read you post. I had a hard time interpreting exactly what was (not) happening. Could be me, or could a that some edits to the text would help

Hein.

Re: Cannot remove the table from source end point (SQL server) replication

PGN — Tue, 01 Mar 2022 18:54:48 GMT

You don't list how you're modifying the table. If you're using GUI, then SSMS sometimes creates a temp table, copies the old table data into the temp table, and then tries to drop the old table. If that's the case, then it cannot be dropped because it's being replicated. Try altering the table the table using T-SQL. However, altering a replicated table calls replication-related stored procedures that may require dbo, or in some cases, sysadmin access.

If you decide to remove the table from replication (with sufficient permissions), be sure to remove it from all tasks first (stop task/save), otherwise, Replicate will keep trying to add it back. I believe you stated you did this, just making sure it's been removed from all tasks.

Re: Cannot remove the table from source end point (SQL server) replication

Steve_Nguyen — Tue, 01 Mar 2022 19:45:56 GMT

@SaranyaK

1. Notes , replicate would create the AR.. Publication, i see you have 0006 and 0005 so this mean at some point you have two Replicate server using this source.

2. best is to stop all running task, and just remove the ARxx publication, so when task resume it would recreate the publication.

this would be a clean AR,,publication.

Re: Cannot remove the table from source end point (SQL server) replication

SaranyaK — Tue, 01 Mar 2022 20:25:50 GMT

Thanks Steve will try that and let you know

Re: Cannot remove the table from source end point (SQL server) replication

Heinvandenheuvel — Tue, 01 Mar 2022 20:33:20 GMT

@Steve_Nguyen wrote:

@SaranyaK

1. Notes , replicate would create the AR.. Publication, i see you have 0006 and 0005 so this mean at some point you have two Replicate server using this source.

Noop. That number is DB_ID('database_name')

Remember an article is like a flag to indicate 'this table is of interest'. No more no less. Therefor with 2 tasks having interest in a given table, there only needs to be 1 article

fwiw,

Hein.