Solved

SQL Server 2008 R2 Remove Not for Replication

Posted on 2013-05-20
9
689 Views
Last Modified: 2013-07-10
I initially set up replication temporary and it removed.  

Now we've noticed the id columns have ‘FOR REPLICATION’, immediately we executed sp_removedbreplication ‘dbname’.

After executing the sp, the id column now have ‘NOT FOR REPLICATION’.  

How do you get rid of this NOT FOR REPLICATION from my id columns?


e.g.

(id)     INT       IDENTITY  (1,1)  NOT FOR REPLICATION NOT NULL,
0
Comment
Question by:Favorable
9 Comments
 
LVL 29

Assisted Solution

by:QPR
QPR earned 83 total points
Comment Utility
0
 
LVL 9

Assisted Solution

by:MattSQL
MattSQL earned 334 total points
Comment Utility
Is there an issue with having this there?
0
 

Author Comment

by:Favorable
Comment Utility
Is an issue when doing schema compare inside of visual studio 2012.

Is there a way to turn if off for all table identity columns in the database?
0
 
LVL 9

Assisted Solution

by:MattSQL
MattSQL earned 334 total points
Comment Utility
alter table tablename alter column columnname drop not for replication
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Favorable
Comment Utility
do you have to repeat for over one thousand objects in the database?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 83 total points
Comment Utility
do you have to repeat for over one thousand objects in the database?
You don't have to repeat it that many time you just need to execute it that many times.
0
 
LVL 9

Assisted Solution

by:MattSQL
MattSQL earned 334 total points
Comment Utility
Run this in the database. The output will be all of the alters that need to be run...

select
      'alter table ' +
      object_name([object_id]) +
      ' alter column ' +
      name +
      ' drop not for replication'
from sys.identity_columns where is_not_for_replication = 1
0
 
LVL 9

Accepted Solution

by:
MattSQL earned 334 total points
Comment Utility
Has this been answered?
0
 

Author Closing Comment

by:Favorable
Comment Utility
thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now