December 3, 2003 at 6:59 am
Hello Gurus,
I have succesfully setup transactional replication but when I ran command sp_link_publication on Subscriber to allow updates propogate to Publisher, I got following error :
1. Warning: The table 'MSsubscription_properties' has been created but its maximum row size (8361) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
2. Warning: The table '#olddata' has been created but its maximum row size (9081) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
3.Warning: The table 'MSreplication_queue' has been created but its maximum row size (9081) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Could you please tell me what does it mean or if it is going to fail at certain point.
I have other questions too but will ask in different thread.
Thanks.
December 3, 2003 at 10:56 am
Those are tables created but the sp sp_link_publication and also sp_MScreate_sub_tables.
It means that the size of columns is bigger then permited.
The tables still were created, so you shoudn't have any problem.
December 3, 2003 at 11:04 am
Thanks racosta, this helps.
Now other question :
1. If I add another table and want to make this table part of replication it enforce the whole new snapshot. Does that mean it's transferring the whole snapshot again to subscriber rather than only newly published table.
2. When I setup replication it didn't transfer trigger's on the table. Question is, If publisher goes down then to make subscriber active server do I have to create all the triggers first? What if I create trigger right now, is it going to create any problem in synchonising the data.
Thanks a lot.
December 3, 2003 at 11:44 am
You won0t be able to add a table to the publication so easily.
First you will need to drop all subscription, then recreate the publication with the new table and then susbcribe all your subscriber againg, applying the snapshot.
You can add columns to existing tables, but not new tables into the publication.
The triggers can 't be part of a publication. This means you can't replicate the creation or modificacion of a trigger.
If you need the triggers in the publisher and subscribers, then you will have to create it manually in each one.
December 3, 2003 at 11:52 am
Thanks a lot. You are really making my job easy.
Now a question on Snapshot. Since I have almost 5 GB database and taking snapshot and applying it over WAN takes more than a day. What if I backup the database and restore it at subscriber end and when creating subscription, I select "subscription already have snapshot". Will this strategy works considering no one making change during this process?
December 9, 2003 at 8:23 pm
Adding a table to an existing publication is VERY SIMPLE. You can use sp_addarticle to do this.
This will add the table to the publication but with no subscribers. Use sp_addscription to do this.
The next time the snapshot agent runs, it will generate a snapshot of the new table only.
If you create triggers on the subsriber, make sure that you understand whether you should use "NOT FOR REPLICATION". If you are publishing data that is the result of a trigger firing at the publisher you probably do not want to have the trigger fire on the subscriber. In this case, specify "NOT FOR REPLICATION" in the trigger(s).
December 10, 2003 at 6:37 am
Yes that should work. But you must be sure no transactions where made in the middle because they won't be replicated.
Due to a bug in SQL 2000, sometimes you can get an error if you add subscribers with out applying the snapshot. Don't remember it exactly, what it was something like couldn't insert duplicate key in #Snapshot! (or something like that).
To fix it, you can install a Microsoft Path, or create a publication, create a snapshot, and then manually empty all the *.bcp files.
This way if you restore your db and you have both databases equal, you create a publication, empty the bcp files, and apply the "empty" snapshot.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply