Post reply

Update System tables

  • September 12, 2007 at 9:49 am

    #174792

    How can you update a system table in SQL 2005, I had tried the sp_configure "allow update",1  reconfigure with override But that didn't work. Well it change the run value to 1 but when I ran the stored procedure that update the system table I receive "Ad hoc updates to system catalogs are not allowed.".

     

    In the Microsoft SQL SERVER 2005 book (pp 151) it say you can but it's not happening? I even try to do it from management Studio from the Server Properties but It doesn't have "Allow Direct Updates to System Table" look the book says.  Also I'm doing the from the server an login as SA.

     

    Any help would be appreciated, thank you.

  • September 12, 2007 at 10:28 am

    #733197

    I'm curious as to why you think you need to update the system tables directly anyway. What are you trying to accomplish? there might be a better way to do what you are trying to accomplish.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 12, 2007 at 10:29 am

    #733198

    You have missed one big change in SQL Server 2005 compared to the previous versions. There is no access to the system tables anymore or the old system table structure does not exists anymore.

    You can still query the system tables through system views but you cannot update, delete or insert to them.

    This is what you can find from books online under the topic "How to: Set the allow updates Option (SQL Server Management Studio)"

    "The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported."

    You should probably take a look at the topic in Books Online "Querying the SQL Server System Catalog" to find out more about the changes.

  • September 12, 2007 at 12:18 pm

    #733237

    (In the Microsoft SQL SERVER 2005 book (pp 151) it say you can but it's not happening?)

    If the book is new return it because skilled SQL Server users knows what you are trying to do is not good practice in previous versions of SQL Server and invalid in SQL Server 2005.  These are the reasons the book I recommend for SQL Server is the BOL because most books writers are vague at best to not covering the version of SQL Server they are writing about.

     

    Kind regards,
    Gift Peddie

Viewing 4 posts - 1 through 3 (of 3 total)

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