How to drop the word for replication in table column

Question

How to drop the word for replication in table column

sqlfriend

SSC Guru

Points: 52588
More actions
July 31, 2018 at 5:07 pm

#407288

I have set up transaction replication from a database on premise to an azure sql database.
But later I decide not use it.
So I removed the configuration of transaction replication publisher and subscriper and distributor.
Then I found the identity columns in many table still have the word for replication in it.
I assume when I removed the setup and configuration of publisher or subscriber, it will automatily remove the words from table schema.
But it did not . Is it a defect or something I missed?
Now I want to manually remove the word for replication on table identity column, how can I do for multiple tables in SQL or other tools?
We have many tables and it is a lot of work to click each table and find the work replication in its definition and replace one by one.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

sqlfriends - Tuesday, July 31, 2018 5:07 PM
I have set up transaction replication from a database on premise to an azure sql database.
But later I decide not use it.
So I removed the configuration of transaction replication publisher and subscriper and distributor.
Then I found the identity columns in many table still have the word for replication in it.
I assume when I removed the setup and configuration of publisher or subscriber, it will automatily remove the words from table schema.
But it did not . Is it a defect or something I missed?
Now I want to manually remove the word for replication on table identity column, how can I do for multiple tables in SQL or other tools?
We have many tables and it is a lot of work to click each table and find the work replication in its definition and replace one by one.
Thanks

I am not quite following what you are seeing or where you are seeing it. Did you get this looking at column properties in SSMS or sp_help or ?
There is a property called "Not for replication". That is a normal column property - whether using replication or not.

Sue

ANn -425914 Right there with Babe Points: 733 More actions · Answer 2

Oh, really. I thought that shows up there is because I set up transaction replication first. But I later removed replication.2

If not what does that mean?

Thank you!

sqlfriend SSC Guru Points: 52588 More actions · Answer 3

I should add in my original post that I have an identity column like this:
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

Yes, every column has a property of Not for replication but default is false.

Mine is true. That is why I think it is left over even after I removed the transaction replication publisher and subscriber.
Somehow it does not change it back.

So this goes back to my original question, how can I fix these for many tables columns?

Thanks

sqlfriend SSC Guru Points: 52588 More actions · Answer 4

I found the solution here.

https://www.mssqltips.com/sqlservertip/1274/change-not-for-replication-value-for-sql-server-identity-columns/

Thanks