Hey,
I wanted to get back to the original theme of the blog for a bit, making stupid mistakes! I reckon I can fill the internet with these… But one in particular I wanted to share with you real quick.
I get where Rick Deckard (Blog) was coming from. I’ve often thought of shooting rogue replicants in the face when they get out of hand. One such tale…
While moving some databases around in a consolidation effort, replication from one of our primary transaction databases was constantly failing to the new Data Warehouse, it kept failing with an error message like.
Could not find “OldDataWarehouse” in sysservers. Run “sp_addlinkedserver” to add the server to sysservers”
So with OldDataWarehouse not being involved any more, I couldn’t figure out why the new server was getting this error message. I cast my mind back and remember I had used OldDataWarehouse as the distributor for this replication in the past, and thought that my NewDataWarehouse just couldn’t connect to it due to firewalls or something.
So, I dropped replication/distribution on my primary server and set it all up again, with no sign of OldDataWarehouse. I create the publication, connect up NewDataWarehouse, and sit back.
Could not find “OldDataWarehouse” in sysservers. Run “sp_addlinkedserver” to add the server to sysservers”
WTF???
Check through the logs, applying scripts successfully, and then just bombs out. I check where it’s failing and it’s on a particular stored procedure. The stored procedure I wrote… To copy data to OldDataWarehouse…
I should also add I was at this for 2 days beating my head against the wall.
What happened was a custom stored procedure I had written to archive data manually to the OldDataWarehouse, when replication tried to create it on the NewDataWarehouse machine, it couldn’t find a path to OldDataWarehouse, wouldn’t create the stored procedure, and tried to apply the snapshot from the start again. Lather, rinse, repeat. Always repeat.
I took the Stored Procedure out of the articles for publication, and lo and behold, it worked perfectly. Damn.
So the lesson is, when you’re replicating a database and you have hard coded links to servers in there, make sure that A) Every server has the same links or B) You just do it manually and update the server links appropriately. Do not C) Swear loudly at how bad replication is for 2 days, then try to hide the fact that you’re a dumbass.
In case it’s a bit unclear, I had a setup like:
Primary server stored procedure.
Insert into OldDataWarehouse.dbo.Archive etc. etc.
When it tried to copy it to NewDatawarehouse as I had selected all articles, when it parsed that code, it couldn’t find the linked server, and bombed.
Short and to the point this week, but I hope to get another few out like this with small, stupid mistakes I’m making in the hopes you can learn from them.
Good luck!
Stephen