Replicate many SQL 2008 tables to one 2000 subscription

We currently have replication turned on where many 2000 tables are pushing to one 2000 subscription table. Everything works fine until we tried to replicate data from 2008 to 2000. When the 2008 publication drops and recreates the table it causes everything to fail
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
You are trying to set up replication between non compatible data types. However replication can be set up between 2008 and 2000 but you have to change the datatypes in 2008 which are supported by 2000.

I would suggest, migrate 2000 databases to 2008 or 2012.
vraj20Author Commented:
The Subscription Table is in SQL 2000
The Publication Table is in SQL 2008

When we turn on replication and create a new snapshot for SQL 2008 Table. The Subscription Table gets drop and gets recreated and all the published tables from SQL 2000 returns with an error message that it can not find the records in the Subscription table
Deepak ChauhanSQL Server DBACommented:
Ok this does not data type issue. i again read out your question.

You have sql 2008 replication pointing to one subscription table (sql 2000) and another sql 2000 publication is also pointing to same subscription.

If this is the case table in the subscription has been overwritten from sql 2008 data thats why you are getting error in sql server 2000 publication.

Did you set the sync property to auto while setting up sql 2k8 replication.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

vraj20Author Commented:
Where can I set the synch property
vraj20Author Commented:
By default is the synch property set to auto
Deepak ChauhanSQL Server DBACommented:
Yes by default it set to auto but in your case where you dont want to overwrite existing data you have to change it to 'Replication support only'.

exec sp_addsubscription @publication = N'<>', @subscriber = N'<>',
@destination_db = N'<>', @subscription_type = N'Push', @sync_type = N'automatic',
 @article = N'all', @update_mode = N'read only', @subscriber_type = 0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vraj20Author Commented:
I will try this and let u know
Vitor MontalvãoMSSQL Senior EngineerCommented:
vraj20, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.