We use native replication very infrequently at work, but still have a couple boxes using it. During one of our last releases (schema changing) we had an issue with replication distribution agent restarting. It seemed to have mixed up the order of new fields and data being sent to the subscriber, and we would get errors about extra fields or missing fields in the sp_MS proc thats autogenerated.
We edited the proc to add and remove fields as needed. Once we worked our way past all the data flowing and errors that kept coming, all seemed to be working well.
We edited the proc to add and remove fields as needed. Once we worked our way past all the data flowing and errors that kept coming, all seemed to be working well.
Yesterday, we encountered the same error again, with the same published article and the same proc. It was odd. We went back to the same troubleshooting we had previously tried by editing the proc depending on the errors we saw. However, it simply was not working. Without thinking out of the box, we tried to do the same things as before, but without success. One of the other DBA's suggested we add all the fields but with null defaults. When you think about it, it makes sense. For some reason the data being replicated kept switching from 15 fields to 16 fields. Each time it had 16, and only 15 were being sent to the proc, it errored. By setting the 15th and 16th columns in the proc to nullable, and letting them be used or not, seemed to fix the issue this particular article was having.
One thing I have learned about replication, whichever solution that is used, is that its all a pain in the butt, but it gives you a solution that is super handy, yet difficult.
No comments:
Post a Comment