SQL Server Performance

Ad hoc updates to system catalogs are not enabled.

Discussion in 'General DBA Questions' started by rdegw, Jun 12, 2007.

  1. rdegw New Member

    Hi There,

    Would you be able to point me in the right direction. I'm trying to run a script which enables a database user to update sysusers to set SID.

    Although on retuning the script in QA I'm presented with the following error

    'Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.'

    Could you advise.

    Many Thanks

    Rich
    rdegw, Jun 12, 2007
    #2

  2. dineshasanka Moderator

    right click the server and select properties . There you have the option to enable

    Bue becarefull when updating system tables

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
    dineshasanka, Jun 12, 2007
    #2

  3. FrankKalis Moderator

    Check out sp_configure in BOL. Note, that if you now get used to this update, be aware that it won't work in SQL Server 2005 and above anymore.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
    FrankKalis, Jun 12, 2007
    #2

  4. FrankKalis Moderator

    FrankKalis, Jun 12, 2007
    #2

  5. satya Moderator

    It is possible in SQL 2005http://sqlserver-qa.net/blogs/tools...dates-to-system-catalogs-are-not-allowed.aspx check my blog,but as suggested it is not supported and unless CSS refers to do so.

    Rich,
    I would like to know who referred or why you are attempting to update the system tables.


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, Jun 12, 2007
    #2

  6. FrankKalis Moderator

    The DAC is nothing the common user will ever get to know about at all. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Jun 12, 2007
    #2

  7. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] Unless someone like us experiences, will share the knowledge anyway [<img src='/community/emoticons/emotion-5.gif' alt=';)' />].<br /><br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
    satya, Jun 12, 2007
    #2

  8. FrankKalis Moderator

    FrankKalis, Jun 12, 2007
    #2

  9. dr_maq@hotmail.com New Member

    Hi SatyaI am trying to explain why I want ot update syscolumn table. In syscolumns table there is a column "colid" I want to change the value of this colid. Why I want to change?When I delete a column from a Table, these colid values do not update automatically but I want to have consecutive number without any gap like (1,2,3,4,5)Say I have 5 columns in a table then in syscolumns table value of last row's colid column will be 5.(1,2,3,4,5) If i delete last column and add a new column the new column's colid value is 6. It should be 5 after automatic updated but its not.I hope you understand what I mean.
    Thanks
    Maqsood
    dr_maq@hotmail.com, Oct 22, 2008
    #3

  10. FrankKalis Moderator

    Is it just for the "aesthetics" or are you facing some issues with these gaps?
    FrankKalis, Oct 22, 2008
    #4

  11. dr_maq@hotmail.com New Member

    Thanks for you quick reply
    Its not aesthetics. I am facing problems with these gaps. My application uses these number and due to these gaps my application thinks that some columns are missing because these numbers are not consecutives.
    Actually we have our own data access layer. We are not using DataSet, DataTable etc. because we have our own classes to wrap SQL Tables in C# code.
    I have to remove these gaps through some how. Can you advice me how to remove these gaps?
    Thanks
    dr_maq@hotmail.com, Oct 23, 2008
    #5

  12. FrankKalis Moderator

    You probably shouldn't use these columns in your app in the first place. What about using the ORDINAL_POSITION column in INFORMATION_SCHEMA.COLUMNS? Will this also do the trick?
    Since you've posted this in the old SQL Server 2000 thread, I will assume that you are using SQL Server 2000. If so, have a look at the first links here: http://www.google.ch/search?hl=de&q=how+to+fix+inconsistent+metadata&btnG=Google-Suche&meta=
    Might work out for you, but in the long run I would try to get away from using this columns, because accessing and updating system tables is even more difficult in SQL Server 2005.
    FrankKalis, Oct 24, 2008
    #6

  13. ColinI New Member

    In my sys.database_files table in SQL2005 I have what appears to be an orphan record that I cannot get rid of that is preventing me from backing up this database. I have tried dropping and re-creating the catalog that I believe must have created it originally, but this orphan is still there:
    65538 88E01758-FF24-4EC8-ADBC-032EF2C62C62 4 FULLTEXT 1 sysft_{2D2E9CF7-70C7-4B04-A1EF-876B5C57E2EC} C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTDataGIROFullTextCatalog0001 0 ONLINE 0 0 0 0 0 0 0 0 1751000000068700387 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    What can I do?
    ColinI, Jan 8, 2009
    #7

  14. satya Moderator

    Welcome to the forums.
    How big is this database?
    Is it possible to detach and attach the same database to see whether this is corrected?
    satya, Jan 11, 2009
    #8

  15. ColinI New Member

    Thanks Satya.
    The MDF file is 40Mb, and the LDF file is 22Mb. Yes, I have detatched and re-attached the database several times without success.
    - Colin
    ColinI, Jan 13, 2009
    #9

  16. satya Moderator

    What is the level fo service pack on SQL?
    satya, Jan 14, 2009
    #10

  17. ColinI New Member

    SQL Server 2005 64-bit Service Pack 2
    ColinI, Jan 14, 2009
    #11

  18. satya Moderator

    Ok, not a problem with service pack then and in this case I see that a FULLTEXT record which means in the past have you tried or stopped any of fulltext settings?
    satya, Jan 17, 2009
    #12

  19. ColinI New Member

    Yes. There is a full text catalog for this database. I have since deleted and recreated this catalog more than once to see if this ophan entry can be removed, but nothing I do seems to work.
    ColinI, Jan 17, 2009
    #13

Share This Page