SQL Dumb Ass

I love Microsoft error messages. I really do. Especially since I charge by the hour.

I was setting up a SQL "Maintenance Plan" to do regular backups and stuff. I've done it many times before, and it always worked pretty smoothly. After setting them up, I go through and manually run the Agent jobs to make sure they all work. They always have before, but as I said, I charge by the hour, so I go ahead and check them out. They all work except the transaction log backup step! Microsoft gives me the incredibly terse and ultimately meaningless error message shown here:


Now, this is simply not a lot to go on, so I examine the system logs and such and come upon the following:

This was one I struggled with for a long time. I was attempting to publish a SQL Server 2000 database, using merge replication. Everything seemed to work fine. I set up the publisher and distributor as per the books. I Published the database. I had to add GUID's and spent some time removing identity columns (demoting them to just INTs) wherever I could in the database. I learned a lot about identity range management. When all was said and done, I then subscribed to the database. The subscription went smoothly. I tested, and replication seemed to be working. Wow, I thought. Just like in the books. Soon thereafter, I discovered how horribly wrong I was.

Since the testing phase is that part of the development cycle where errors are guaranteed to fail to appear, my database replicated flawlessly. So, after a few weeks of testing, it went into production. Production is the phase after development where errors occur. I suddenly started receiving replication errors. The typical error was:

This is where I learned about Identity range management. Basically, when replicating identity values, each subscriber (and the publisher) is given their own range of identity values to use. When a subscriber gets about 80% of the way through its' range, it will communicate with the publisher and get a new range. So I republished with outrageous ranges for the identity values. I continued to get the same error. I ran DBCC CHECKIDENT('table', NORESEED) to see what the current values were. They were well within the 80%. I went ahead and reseeded anyway (DBCC CHECKIDENT('table', RESEED)) and promptly continued to receive error messages. I wailed. I cried. I gnashed my teeth. Despite all evidence to the contrary, SQL Server would continue to insist that my identity range was full.

Since it was completely clear that the identity range was not, in fact, full, it seemed to me that I must somehow be misunderstanding the message. Unfortunately, no matter how I examined the message it seemed to keep saying that the identity range was full. It mocked me. I noticed that the errors always seemed to occur in tables with foreign key dependencies. It began to dawn on me with glacial slowness that the error had nothing at all to do with identity ranges. In fact, it had nothing to do with identity values at all. Of Course! The error message seems so obvious now! Thank you Microsoft, for your clear and informative error messages! The error message had even gone so far as to include a solution that would not fix the problem.

What was happening was that, during replication, data would start getting pumped into ancillary tables (you know, the kind that hold phone numbers), but sometimes (not always) the agent would try to pump the data into the ancillary table before a matching entry in the primary table was present. This generated a relational error, and prevented the data from getting propagated across. By the time I went to the conflict viewer to resolve the conflicts, the primary table was populated, so I could accept the data and it would then propagate fine.

Once I realized that the true error was due to referential integrity, I began examining the relationships themselves. And I looked once more at the property called "Not for replication". The palm of my hand exploded upon my forehead! All the devices of Bill Gate's minions were now laid bare before me, and I realized my folly. I had looked at that option before, when I was first preparing the database for replication. And my first interpretation of that option was that by checking it, the relationship would not be enforced at the subscriber. Well, of course I wanted it enforced at the subscriber, so I left in unchecked. What the option really means, though, is that the relationship is not to be enforced during the replication process. The same meaning as the "not for replication" option for Identity values.

By declaring relationships as "not for replication", the problem was solved. I feel like an idiot. I mean, in retrospect, the error message is perfectly clear. At the risk of appearing exceedingly stupid, I have to ask: Why would you ever want a relationship enforced during replication? As the rules were enforced already during the initial data entry, you know that parent records have to exist. So why isn't "not for replication" the default setting? Why is it an option at all? Anyone?