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

[SaranyaK]

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.

1. 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?

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

1. Getting below error when trying to remove the article.

1. 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

[Heinvandenheuvel]

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-... 

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.

[PGN]

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.

[Steve_Nguyen · Accepted solution]

@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.

[SaranyaK]

Thanks Steve will try that and let you know

[Heinvandenheuvel]

---

@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.