Post reply

Updates System Catalogs

  • January 19, 2010 at 9:06 am

    #138128

    Hi..

    is it possible to update system catalog values? if so then tell me the procedures how to update the system catalog values. i have tried with the following statements but does not help me out.

    reconfigure with override RECONFIGURE

    update master.sys.sysdatabases set status = 16 where name = 'db'

    Its giving the error message " Ad hoc updates to system catalogs are not allowed."

    Thanks in advance

  • January 19, 2010 at 9:09 am

    #1105493

    No, you cannot update system tables.

  • January 19, 2010 at 10:27 am

    #1105558

    The above statement is half true. Its actually allowed, but discourages to be used and you should be extra careful using it, you may end up corrupting your whole sql server. Also, use it only if no other work around is there for the problem.

    Please read - http://sqlserver-qa.net/blogs/tools/archive/2007/04/24/sql-server-2005-ad-hoc-updates-to-system-catalogs-are-not-allowed.aspx and http://dbaspot.com/forums/sqlserver-server/333867-sql-2005-update-systable.html

    Also, please update us if it works for you.

  • January 19, 2010 at 10:50 am

    #1105573

    I have to question updating the system tables to begin with. This isn't something that should be done without a lot of thought and testing. The fact that you have to start the instance in single user mode to use the DAC to accomplish this task means it really shouldn't be done unless absolutely necessary.

  • January 19, 2010 at 11:06 am

    #1105592

    Lynn Pettis (1/19/2010)

    I have to question updating the system tables to begin with. This isn't something that should be done without a lot of thought and testing. The fact that you have to start the instance in single user mode to use the DAC to accomplish this task means it really shouldn't be done unless absolutely necessary.

    Though it is possible, I would not recommend it. If you must venture down that path - make sure you have full database backups, full system backup, and a system state backup prior to making these changes. Sound paranoid? Maybe. Every precaution should be taken when making ad hoc changes to system tables. I also would not do this unless it was an absolute last option if it were a production database.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 19, 2010 at 11:16 am

    #1105598

    try this.

    USE master

    EXEC sp_configure 'show advanced option', '1'

    go

    RECONFIGURE WITH OVERRIDE

    go

    EXEC sp_configure 'allow updates', '1'

    go

    RECONFIGURE WITH OVERRIDE

    go

    --Now update the table u wanna update

    begin tran

    update master.sys.sysdatabases set status = 16 where name = 'db'

    then

    select * from master.sys.sysdatabases

    where name = 'db'

    ---confirm if correct record is update

    commit or rollback tran

    and revert back the optons

    EXEC sp_configure 'allow updates', '0'

    go

    RECONFIGURE WITH OVERRIDE

    go

    USE master

    EXEC sp_configure 'show advanced option', '0'

    go

    RECONFIGURE WITH OVERRIDE

    go

    Although make a note of the value ur trying to update before running the update stmt and try doing in a transaction and check ony one row is updated and then select the record to verify that correct record is updated then commit or rollback the tran.

    Cheers....!!!

  • January 19, 2010 at 11:17 am

    #1105600

    Ramji29 (1/19/2010)

    try this.

    USE master

    EXEC sp_configure 'show advanced option', '1'

    go

    RECONFIGURE WITH OVERRIDE

    go

    EXEC sp_configure 'allow updates', '1'

    go

    RECONFIGURE WITH OVERRIDE

    go

    --Now update the table u wanna update

    begin tran

    update master.sys.sysdatabases set status = 16 where name = 'db'

    then

    select * from master.sys.sysdatabases

    where name = 'db'

    ---confirm if correct record is update

    commit or rollback tran

    and revert back the optons

    EXEC sp_configure 'allow updates', '0'

    go

    RECONFIGURE WITH OVERRIDE

    go

    USE master

    EXEC sp_configure 'show advanced option', '0'

    go

    RECONFIGURE WITH OVERRIDE

    go

    Although make a note of the value ur trying to update before running the update stmt and try doing in a transaction and check ony one row is updated and then select the record to verify that correct record is updated then commit or rollback the tran.

    Cheers....!!!

    Won't work in SQL Server 2005.

  • January 19, 2010 at 12:06 pm

    #1105649

    Ramji29 (1/19/2010)

    update master.sys.sysdatabases set status = 16 where name = 'db'

    Even ignoring that 'allow updates' is ignored on SQL 2005, sysdatabases isn't even a table any longer. It's a view and not an updatable one.

    That advice was dangerous for SQL 2000, and is useless for SQL 2005.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 21, 2010 at 7:18 am

    #1106683

    Lynn Pettis (1/19/2010)

    Ramji29 (1/19/2010)

    try this.

    USE master

    EXEC sp_configure 'show advanced option', '1'

    go

    RECONFIGURE WITH OVERRIDE

    go

    EXEC sp_configure 'allow updates', '1'

    go

    RECONFIGURE WITH OVERRIDE

    go

    --Now update the table u wanna update

    begin tran

    update master.sys.sysdatabases set status = 16 where name = 'db'

    then

    select * from master.sys.sysdatabases

    where name = 'db'

    ---confirm if correct record is update

    commit or rollback tran

    and revert back the optons

    EXEC sp_configure 'allow updates', '0'

    go

    RECONFIGURE WITH OVERRIDE

    go

    USE master

    EXEC sp_configure 'show advanced option', '0'

    go

    RECONFIGURE WITH OVERRIDE

    go

    Although make a note of the value ur trying to update before running the update stmt and try doing in a transaction and check ony one row is updated and then select the record to verify that correct record is updated then commit or rollback the tran.

    Cheers....!!!

    Won't work in SQL Server 2005.

    Yes...i tried in sql server 2005 and was not working that's why i have posted this question... thanks yours reply

  • January 21, 2010 at 10:51 am

    #1106844

    You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 22, 2010 at 9:54 am

    #1107429

    Setting a status of 16 ? hmmm ...

    Why not do it the easyway with alter database ???

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • January 22, 2010 at 12:32 pm

    #1107507

    rudy komacsar - Doctor "X" (1/22/2010)

    Why not do it the easyway with alter database ???

    Where's the fun in that?

    :hehe:

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply