updating system catalogs .. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

updating system catalogs ..

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-07-08 : 03:26:58

Hi,

Small clarification needed.

I believe in SQL 2005, the new system database RESOURCE is added so that we cannot make any
direct changes to the system catalog's.

But recently, i was experimenting the server migration..meaning, we are trying to replace existing server with new server with more hardware resources (RAM,CPu's disk space).
If everything works well on the new server, we will be renaming and assigning the same IP for the new server as existing server and bring the old server offline.

For do this, i tried with allow updates to disable all the jobs as follows

sp_configure 'show advanced options',1
reconfigure with override;
go
sp_Configure 'allow updates',1
reconfigure with override;
go
sp_configure 'allow updates'
go

Then to disable the jobs , i have used the following script as we do it in normal SQL 2000 scenario.

Here is the script

use msdb
go
update sysjobs
set enabled = 0
go

This would disable all the SQL Agent jobs.

But my question is, Why the updations of system catalogs are still supported even "Resource " database is supported.

Correct me if am wrong !!!! Am confused....

Thanks in Advance,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-08 : 12:41:12

You should not be doing this! Use the system stored procedures to do the work for you. Here you go: http://weblogs.sqlteam.com/tarad/archive/2003/10/17/325.aspx

There is almost always a system stored procedure that could whatever you need in the system catalog. You just have to look!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-08 : 15:47:42

Also, to disable ALL jobs, just disable SQL Agent.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-09 : 02:04:34

sysjobs is not a system table. It's a user table. Hence the reason you can update it (even though you shouldn't). The real system tables, things like sysrscols are hidden and you can't even see them, let alone change them

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources