I've read many postings saying how this works. Everything I've tried hasn't. This dis-allowing of system database updates has already caused me many wasted hours. I knew how to modify what I needed modified in SQL 2000.
Situation: Using Sql Server 2005, Service pack 2 -
I've been doing extensive testing getting ready to move merge replication from Sql 2000 to Sql 2005. Since MANY things are different, and the "wonderful" Microsoft Update Advisor does very little except point out the obvious, much work is needed to comb through views, procedures, etc looking for these changes.
It's probably rather easy if you haven't used a lot of procedures, functions and replication, however, that's not where I find myself.
At some point, on my publisher server, the distribution database lost a reference point. (so much for Microsoft not letting you change something important). The name distributor shows in my "System Databases", with no "+" alongside. No properties etc are available.
I saw this, and tried to drop the distributor. did not work.
I thought, I'll stop the service and delete the database files for distribution db.
After restarting service, it still showed and I remembered, "Oh, there's an entry in the master tables sys.databases and sys.sysdatabases for distribution db.
I thought then, "I'll just delete them, and run scripts to recreate."
I cannot update system catalogs.
Okay, so I tried:
sp_configure 'Allow updates',1
reconfigure with override
these ran fine.
Then I tried:
Delete sys.databases where database_ID = 16
Delete sys.sysdatabases where DBID = 16
Same error as always: cannot do ad hoc updates on system catalogs.
Apparently some people are succeeding with this. I'd appreciate any assistance finding ways to get around all the limitations of Microsofts "Improvements."
Sound a bit cynical? Well, I have no reason to praise Microsoft yet.
Thanks a lot.