none
Ad hoc updates to system catalogs are not allowed.

    Question

  • in SQLServer2005 how can I allow update system catalogs (by mouseclick)?

    sp_configure 'allow updates', 1 - works, but I get the message

    Msg 259, Level 16, State 1, Line 1

    Ad hoc updates to system catalogs are not allowed.

    Friday, September 16, 2005 11:38 AM

Answers

  • Catalog updates are still not supported.
     
    But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.

    Thanks
    Laurentiu
    Monday, September 19, 2005 4:47 PM
    Moderator
  • Hafi, in SQL Server 2005, search for topic in Books Online "Troubleshooting Orphaned Users".  This has a solution for your exact problem without doing any ad-hoc updates to system tables.

     

    Monday, September 19, 2005 5:12 PM
  • I omitted to say that the server also has to be started in single-user mode, for the updates to be allowed (using the -m flag). By itself, DAC will allow you to see system tables, but to update them, you also need to have the server started in single-user mode.

    Thanks
    Laurentiu

    Tuesday, September 20, 2005 5:43 PM
    Moderator
  • Even in single user mode, catalog updates are not supported by Microsoft.

    For your other comments, you should post them on a related forum (SQL Server Database Engine, for example). If you do not receive a useful suggestion or workaround for your issue, please open a request at:

    https://connect.microsoft.com/feedback/default.aspx?SiteID=68&wa=wsignin1.0

    Thanks
    Laurentiu

    Monday, December 04, 2006 7:54 PM
    Moderator
  •   By the way, I forgot to mention. We are trying to make SQL Server a better product that doesn’t require unsupported system tables that may affect other areas of your systems, including security. We changed the rules to modify the system tables as a defense in depth measurement, but we are also trying to improve our features in SQL Server in order to minimize the need for such (unsupported) system table hacks; for example the addition of ALTER AUTHORIZATION statements or additions to existing syntax such as ALTER USER … WITH LOGIN.

     

      BTW. Laurentiu wrote an excellent article describing some of these changes that are new in SQL Server 2005 SP2:

    http://blogs.msdn.com/lcris/archive/2007/02/19/sql-server-2005-some-new-security-features-in-sp2.aspx

     

      I hope this information helps, and we appreciate your comments and questions as they help us to make a better product. Thanks a lot.

     

     -Raul Garcia

      SDE/T

      SQL Server Engine

    Thursday, March 22, 2007 5:37 PM
    Moderator
  • See my answer in your duplicate, separate post.

     

    As for the other comments, removing the ability to directly screw around with data in system tables was NOT an "executive decision" at Microsoft.  I can assure you of that.  For over 15 years, Microsoft has been telling everyone to NOT directly update system tables and that the functionality would be removed at a later date.  You can NOT do this in Oracle or DB2.  It is an extraordinarily bad idea and incredibly bad practice.  In SQL Server 2000, there were a miniscule number of cases where direct updates had to be done, because there was no other way.  (There isn't a single example in this thread which would have required direct system table modification in SQL Server 2000 and each case offer thus far for screwing with the system data is a result of bad code meeting bad practices.)  Removing the ability to directly screw around with data in system tables is a USER request and I'm one of those users who will be screaming if anyone decides that it is a good idea to bring back the ability of someone to completely blow up an instance with the click of a button.  Rewriting the system catalogs as been and always will be an extraordinarily BAD idea.

     

    Numbers of X is no excuse for writing bad code or being lazy.  I've managed tens of thousands of SQL Server 2000 instances with millions of databases.  In one client, my day to day responsibilities were to manage more than 30,000 SQL Server instances with in excess of 200,000 databases, BY MYSELF.  We had a LOT of scripts that were executed and not a single one of them screwed around with system table data.  In the 15+ years that I've been working with SQL Server, the number of times I've ever had to directly manipulate system table data is less than 50 and every single one of those cases was because an interface did not exist to make the change any other way.  There is exactly 1 case that I've come across in SQL Server 2005 in the about 5 years I've been working with it that required a direct update to the metadata and that was filed as a bug and fixed more than 2 years ago.

     

    In every single instance that you will dream up to screw around with the system tables, SQL Server 2005 has an interface that is supported to make the changes that you need to make.

     

    As for the "I have to change the recovery model of thousands of databases, etc."  Ever heard of doing the following:

     

    DECLARE @cmd   nvarchar(4000)

     

    SELECT @cmd = 'ALTER DATABASE ' + name + 'SET RECOVERY.....' FROM sys.databases

     

    EXEC sp_executesql @cmd

     

    I'll leave it up to you to decide whether you want to drive this in a loop within TSQL or if you want to use SQLCMD to drive input values to a second instance of SQLCMD.  Runs just as fast as your completely unsupported method of screwing with the metadata and it does everything the way you should have been doing it for years.

    Monday, October 01, 2007 10:57 AM
    Moderator
  • Bryden,

     

    Please take a look at Paul Randalls Blog Post for resolution to this specific problem:

     

    TechEd Demo: Using the SQL 2005 Dedicated Admin Connection to fix Msg 8992: corrupt system tables

     

    Pay attention to his text in Red.  The fix to this is undocumented and unsupported, so you do it at your own risk, but his post shows how to solve the problem.
    Thursday, July 10, 2008 1:44 PM
    Moderator
  • Use sp_dropserver and sp_addserver in SQL:

     

    http://msdn.microsoft.com/en-us/ms143799.aspx

     

     

    Tuesday, September 30, 2008 2:10 PM
    Moderator
  • If you require the colids to not have gaps, you need to use a script that recreates the table.  You should not edit the system tables to do this.  For example, the following change:

     

    Code Snippet

    CREATE TABLE [dbo].[TestTable](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [value1] [int] NULL,

    [value2] [int] NULL,

    [value3] [int] NULL,

    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED

    (

    [id] ASC

    )

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[TestTable]

    DROP COLUMN [value3]

    GO

    ALTER TABLE [dbo].[TestTable]

    ADD [value4] [int] NULL

    GO

    select * from sys.columns where object_id = object_id(N'TestTable')

     

     

    Will leave gaps in the table column id's.  To make the same change above without leaving gaps, you need to do the following:

     

    Code Snippet

    CREATE TABLE dbo.Tmp_TestTable

    (

    id int NOT NULL IDENTITY (1, 1),

    value1 int NULL,

    value2 int NULL,

    value4 int NULL

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT dbo.Tmp_TestTable ON

    GO

    IF EXISTS(SELECT * FROM dbo.TestTable)

    EXEC('INSERT INTO dbo.Tmp_TestTable (id, value1, value2)

    SELECT id, value1, value2 FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')

    GO

    SET IDENTITY_INSERT dbo.Tmp_TestTable OFF

    GO

    DROP TABLE dbo.TestTable

    GO

    EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT'

    GO

    ALTER TABLE dbo.TestTable ADD CONSTRAINT

    PK_TestTable PRIMARY KEY CLUSTERED

    ( id )

    GO

    select * from sys.columns where object_id = object_id(N'TestTable')

     

     

    Thursday, October 23, 2008 7:33 PM
    Moderator

All replies

  • From Books Online:

    This 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.
    Saturday, September 17, 2005 11:47 PM
  • Thanks Greg, but it doesn't help. The question is like follows:
    I restore a database from an other server, the schema (object owner) is not dbo but -for example- abcd. In sql2000 I could update the sid in the sysusers-table from master.dbo.sysxlogins to enable the connection across odbc. In sql2005 there is no sysusers-table in the restored db and no sysxlogins-table in the master-db. If I try to connect across odbc I get the message "Cannot open user default database. Login failed."
    Greetings hafi
    Monday, September 19, 2005 9:12 AM
  • direct updates to the system tables were never supported in SQL Server. But it looks like, in SQL 2005 they are not even ALLOWED and that is a wrong move from Microsoft. Though not supported, sometimes you can't get away without updating system tables.

    For ex., try moving a log shipped database from one server to another, without losing it's synchronization.
    Monday, September 19, 2005 2:22 PM
  • Catalog updates are still not supported.
     
    But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.

    Thanks
    Laurentiu
    Monday, September 19, 2005 4:47 PM
    Moderator
  • Hafi, in SQL Server 2005, search for topic in Books Online "Troubleshooting Orphaned Users".  This has a solution for your exact problem without doing any ad-hoc updates to system tables.

     

    Monday, September 19, 2005 5:12 PM
  • I tried through DAC (sqlcmd -A) and still can't update system tables. As per BOL, DAC allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests. But, it doesn't say to anything about updating system tables.


     Laurentiu Cristofor wrote:
    Catalog updates are still not supported.
     
    But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.

    Thanks
    Laurentiu
    Monday, September 19, 2005 6:21 PM
  • I omitted to say that the server also has to be started in single-user mode, for the updates to be allowed (using the -m flag). By itself, DAC will allow you to see system tables, but to update them, you also need to have the server started in single-user mode.

    Thanks
    Laurentiu

    Tuesday, September 20, 2005 5:43 PM
    Moderator
  • IdeaThanks to all for problem solving, especially to Greg, your hint "Trouleshooting Orphaned Users" was vital importance and most usefullIdea
    Monday, September 26, 2005 8:54 AM
  • I used to be able to make my stored procedure which I loaded on master to execute in the context of the current database (not the master database) by:

     

    sp_configure 'allow updates', 1

    reconfigure with override

    update sysobjects set status = 0xc0000001 where name = 'sp_name'

    sp_configure 'allow updates', 0

    reconfigure with override

    how can this be done now?

    christos

    Monday, October 03, 2005 2:39 PM
  • I believe you can do exec your_db.sys.sp_rename to make sp_rename execute in your_db. Also if you do "use your_db; sp_rename", this will be equivalent to above. Most of system sps were moved to resource database (you can see them in sys.system_objects catalog) in SQLServer 2005. They are nowis user-db neutral and will take current db context as the one to execute in.
    Monday, October 03, 2005 5:38 PM
  • Hi, I'm having the same problem, what was your solution?
    Monday, July 17, 2006 2:13 AM
  • See the following: http://searchsqlserver.techtarget.com/originalContent/0,289142,sid87_gci1102100,00.html?bucket=NEWS&topic=301343

    I dug it up during my search on this topic. If you have a stored proc in master, it can be called using:

    execute('sp_procName ''param1'',''param2''')

    or just general SQL commands

    execute('update table set allfields = null')

     

    I've tested this and it works for my case. Check the article out.

    Thursday, October 12, 2006 6:50 PM
  • All these answers are great workaround-coverage for not allowing ad hoc updates to the catalog tables. And I can see some reasoning behind not allowing DBAs to get their job done in an efficient and expedient manner. But this is just another example of executive thinking --and decision making-- by MS.

    My case is hundreds of databases and no way to use the wonderful method of set processing to get the job done. Now I am begin told if I want to set the status of a hundred databases from 'Full' to 'Simple' recovery, I have to use the mouse? No way!

    Will I have to hack my way into one of the 'system' procs to get the job done or is there a system function available? I do not --repeat-- do not want to place the entire server in SUM (-m) just to make an update to the catalogs.

    If I need to update the catalogs, the functionality of sp_configure 'allow up', 1 and reconfigure with override needs to start working again, or the configuration function needs to allow full access to all options.

    In all, this seems a bit Draco to me --or it's as if someone just rebuilt the Berlin Wall--, this time around the system catalog tables.  

    Monday, December 04, 2006 7:04 PM
  • Even in single user mode, catalog updates are not supported by Microsoft.

    For your other comments, you should post them on a related forum (SQL Server Database Engine, for example). If you do not receive a useful suggestion or workaround for your issue, please open a request at:

    https://connect.microsoft.com/feedback/default.aspx?SiteID=68&wa=wsignin1.0

    Thanks
    Laurentiu

    Monday, December 04, 2006 7:54 PM
    Moderator
  • Hi,

    Well I tried running sp_change_users_login and it fails because 'dbo'/'sa' are not allowed.

    The problem I have is that after moving 2000 db's to a 2005 instance, the 'dbo' in one of them is orphaned and has a sid other than '0x1', the default for 'dbo'.

    Running the following confirms this:

    EXEC sp_change_users_login 'Report'

    Output:

    dbo 0x81BC9C004448614FB6D074446190AAEA

    So, how do I get this orphaned dbo back in sync with the server 'sa' login? I've tried updating the system catalogues directly, but of course that's not permitted.

    PLEASE HELP!

    Thanks

    Richard

    rmcsharry@hotmail.com

    Thursday, March 22, 2007 1:39 PM
  •   ALTER AUTHORIZATION statement is designed to change the ownership of the object. In the case of databases it updates the DB principals catalog to reflect the identity of the new owner (dbo). Try the following statement:

     

    ALTER AUTHORIZATION ON DATABASE::your_db_name TO sa

    Go

     

      Let us know if this work and/or if you have further questions or problems.

     

      -Raul Garcia

       SDE/T

       SQL Server Engine

     

    Thursday, March 22, 2007 5:26 PM
    Moderator
  •   By the way, I forgot to mention. We are trying to make SQL Server a better product that doesn’t require unsupported system tables that may affect other areas of your systems, including security. We changed the rules to modify the system tables as a defense in depth measurement, but we are also trying to improve our features in SQL Server in order to minimize the need for such (unsupported) system table hacks; for example the addition of ALTER AUTHORIZATION statements or additions to existing syntax such as ALTER USER … WITH LOGIN.

     

      BTW. Laurentiu wrote an excellent article describing some of these changes that are new in SQL Server 2005 SP2:

    http://blogs.msdn.com/lcris/archive/2007/02/19/sql-server-2005-some-new-security-features-in-sp2.aspx

     

      I hope this information helps, and we appreciate your comments and questions as they help us to make a better product. Thanks a lot.

     

     -Raul Garcia

      SDE/T

      SQL Server Engine

    Thursday, March 22, 2007 5:37 PM
    Moderator
  • We used SQL code with cursor to drop all user defined Views in sysobject table periodically as part of our DB maintenance and cleanups.  Now SQL 2k5 does not allow update in Sysobjects table.  How do we do this in SQL 2k5 with SQL code (to drop all user defined views)?  It has to be executed with code by SQL jobs.
    Tuesday, May 01, 2007 6:10 AM
  • You should be able to still select from the sysobjects tables from a curos, and then just run:
    exec sp_executesql 'drop view <viewname>'
    Tuesday, May 01, 2007 6:16 PM
  • Dear Laurentiu Cristofor

     

    Could you plz, elaborate all steps once again together. I require to update sysxlogins table to include one more column into it in SQL 2000. And that i very important to its working.

     

    Should i give -m switch in service parameter ?

     

    What is DAC. I am using queryanalyzer how can i use sqlcmd -a.

     

    I am using sql 2000 and actually trying to develop a wrapper UI over sql security so that logins can created and modified right from my UI.

     

    Doing this is very crucial for my current project.

     

    Kindly help. Any help would be much appreciated.

     

    So far i have tried, -m switch on service parameters and sp_configure "allow updates", 1 reconfigure with override

     

    but nothing works....

    Monday, October 01, 2007 10:00 AM
  • See my answer in your duplicate, separate post.

     

    As for the other comments, removing the ability to directly screw around with data in system tables was NOT an "executive decision" at Microsoft.  I can assure you of that.  For over 15 years, Microsoft has been telling everyone to NOT directly update system tables and that the functionality would be removed at a later date.  You can NOT do this in Oracle or DB2.  It is an extraordinarily bad idea and incredibly bad practice.  In SQL Server 2000, there were a miniscule number of cases where direct updates had to be done, because there was no other way.  (There isn't a single example in this thread which would have required direct system table modification in SQL Server 2000 and each case offer thus far for screwing with the system data is a result of bad code meeting bad practices.)  Removing the ability to directly screw around with data in system tables is a USER request and I'm one of those users who will be screaming if anyone decides that it is a good idea to bring back the ability of someone to completely blow up an instance with the click of a button.  Rewriting the system catalogs as been and always will be an extraordinarily BAD idea.

     

    Numbers of X is no excuse for writing bad code or being lazy.  I've managed tens of thousands of SQL Server 2000 instances with millions of databases.  In one client, my day to day responsibilities were to manage more than 30,000 SQL Server instances with in excess of 200,000 databases, BY MYSELF.  We had a LOT of scripts that were executed and not a single one of them screwed around with system table data.  In the 15+ years that I've been working with SQL Server, the number of times I've ever had to directly manipulate system table data is less than 50 and every single one of those cases was because an interface did not exist to make the change any other way.  There is exactly 1 case that I've come across in SQL Server 2005 in the about 5 years I've been working with it that required a direct update to the metadata and that was filed as a bug and fixed more than 2 years ago.

     

    In every single instance that you will dream up to screw around with the system tables, SQL Server 2005 has an interface that is supported to make the changes that you need to make.

     

    As for the "I have to change the recovery model of thousands of databases, etc."  Ever heard of doing the following:

     

    DECLARE @cmd   nvarchar(4000)

     

    SELECT @cmd = 'ALTER DATABASE ' + name + 'SET RECOVERY.....' FROM sys.databases

     

    EXEC sp_executesql @cmd

     

    I'll leave it up to you to decide whether you want to drive this in a loop within TSQL or if you want to use SQLCMD to drive input values to a second instance of SQLCMD.  Runs just as fast as your completely unsupported method of screwing with the metadata and it does everything the way you should have been doing it for years.

    Monday, October 01, 2007 10:57 AM
    Moderator
  • Hi Mike,

     

    You're pretty knowledgeable on this subject. I don't need to mess with any security type of stuff, but what I would love to be able to do is to easily alter user-defined datatypes. You know, easy stuff like changing udt_MyType from a varchar(20) to a varchar(200).

     

    I found a post somewhere (not here) that showed how to do that with the sp_configure 'allow updates' , then RECONFIGURE WITH OVERRIDE, then updating systypes and syscolumns with a new length.

     

    It doesn't work, because I can't even create a Stored  Proc with those lines of code in it (it attempts to run the code instead of create a SP ... very strange) and I get the "Ad hoc updates blahblahblah" error message.

     

    Do you have any advice for an easy, foolproof SP I could create that would allow me to do that? And why doesn't the above stuff work anyway?

     

    I'm using SQL Server 2005.

     

    TIA,

    Thursday, October 18, 2007 11:36 PM
  • Laurentiu,

     

    I did as you suggested and and submitted the following request to Microsoft Feedback.

     

    I am creating a backup with a script using "exec xp_cmdshell" . When running RECONFIGURE after using sp_configure to enable xp_cmdshell, I get the following error "Ad hoc update to system catalogs is not supported." How can I get this to run successfully?

     

    The reply I got back was...

     

    It sounds like you or someone else has also enabled the "allow updates" sp_configure parameter. You should turn that off and then run the reconfigure.

    Following the advice I received back from Microsoft, I added the following to my script...

     

    use master

    GO

    exec sp_configure 'allow updates', 0;

    GO

    RECONFIGURE;

    GO

    use master

    GO

    exec sp_configure 'xp_cmdshell', 1;

    GO

    RECONFIGURE;

    GO

     

     < the rest of my original db_create_and_backup_script.sql>

     

    use master

    GO

    exec sp_configure 'xp_cmdshell', 0;

    GO

    RECONFIGURE;

    GO

     

    Resulting Messages:

    Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

    Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

     < my script messages output>

    Configuration option 'xp_cmdshell' changed from 1 to 0. Run the RECONFIGURE statement to install.

     

    The rest of my script ran with no problems. Thank you for helping me resolve my issues. I hope this will help others.
    Wednesday, October 24, 2007 9:00 PM
  •  

    "(There isn't a single example in this thread which would have required direct system table modification in SQL Server 2000 and each case offer thus far for screwing with the system data is a result of bad code meeting bad practices.) "

     

    Your right, so let me give you one.  For example.  I MUST update system tables for this server move and I have had several instances in the past where I had to do the same.  I have moved probably 20 servers to new hardware with out issue but I needed to update a few system tables in most of the cases.  I was told that we are going from 2000 to SQL 2005 and that it MUST be a clean install and that all systemn databases be moved so there is no hiccpu with sids and jobs, existing confi, job history, operators, alerts, etc.  I mean there was a demand for no hiccup and it had toi be a clean install.  Well guess what, that forces me to perform a hybrid system update.  I was given a machine in which only had a C:\ drive in which I could install 2000 on, copy system databases to, perform an upgrade in place to upgrade the system tables and then I had to move those system tables to the new clean install on the new servers. 

     

    Problem was the production box had many drives and the system databases were on D:\.  Inorder to be sucessful I had to start SQL with -T3608 and then update sysaltfiles to repoint msdb, tempdb and model to the c:\ drive location.  Then I could start sql via service and perform the upgrade. 

     

    Dont tell me that I could have run an alter database with just master online because the database you are altering must be online before you can alter it and tempdb must be online, (started) before you can start model or msdb and since there was no D:\ drive I was screwed therefore I made the necessary sysaltfiles changes and restarted SQL with no issues.  Yes there was more to it than that but my point is still the same. (also had to update sysdatabases for the .mdf.

     

    ALso, have you ever seen sysaltfiles get dirty, once in a while in 2000 after many years of detaching and attaching various databases sysaltfiles can get dirty because it doesnt, for some reason, clean up 100.00% of the time.  It would show database files that no longer existed.

    Saturday, November 10, 2007 12:54 AM
  •   There shouldn’t really be a need for using the system tables directly, in cases there is functionality missing we would really appreciate if you contact u to understand your needs, for example I was talking with an expert on this area, and he told me that you SQL Server 2005 explicitly allows to use ALTER DATABASE MODIFY FILE (FILENAME=…)for databases that failed to start, and there are BOL sections that refer to moving both user and system databases, and we strongly encourage to refer to them.

     

      On the other hand having to fix system tables that get corrupted may be a workaround for an experienced DBA who understand them and needs to get the system up and running without time to loose, but this should be considered an extraordinary circumstance and it should not be used under normal conditions. We would really appreciate if you would contact us with all the details of this event as it may be a bug (or even a usability problem) that may affect more people and we really need to understand the circumstances and investigate if it is possible to solve the problem in the product itself.

     

      BTW. Are you moving the log files (.ldf) along with the DB files (.mdf)? If not, it may be possible that the corruption on sysaltfiles may be caused by using attach/detach. Using BACKUP/RESTORE is a better option than attaching and detaching the database files as you described.

     

      Thanks a lot,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Tuesday, November 13, 2007 2:29 AM
    Moderator
  • Hi Raul,

     I have the issue of some garbage temp files that have somehow got into the sysobjects table. I cannot remove them with drop table and cannot delete them directly due to the new restrictions on editing systables. Their names are:
    #tmp_tblClientProd__________________________________________________________________________________________________00000000012A
    #tmp_tblClientProdWant______________________________________________________________________________________________000000000126
    #tmp_tblClientWant__________________________________________________________________________________________________000000000127
    #tmp_final_tblClient________________________________________________________________________________________________000000000128
    #tmp_tblClient______________________________________________________________________________________________________000000000129

    Ignoring them is not an option as they are interfering with other processes. Do you have any idea how I can get rid of them?

    Regards
    David
    Tuesday, November 27, 2007 5:20 AM
  •    We would like to understand more about you particular scenario, and we would appreciate if you could share some details with us and describe how are these tables interfering with your process.

     

       I asked the experts on metadata and they said that they seem to be cached temp tables, but couldn’t deduce from the name how you obtained them or how they may interfere with any normal operation. They also suggested that this information may be coming from selecting tempdb.sys.objects (which by the way they recommended against doing, but again we were not sure on how you got these tables).

     

      Thanks a lot,

     

     -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, November 28, 2007 10:33 PM
    Moderator
  • How does one accomplish the following without updating the sysobjects table?  The below information is found in http://support.microsoft.com/kb/835860.

     

    -----------------------------------------------------

    How to remove SQL Server replication attributes from all replicated objects in a restored SMS site database

    We do not recommend that you, or any user, change system tables directly. For example, do not try to modify system tables by using DELETE, UPDATE, or INSERT statements or by using user-defined triggers.

    To remove the SQL Server replication attributes, follow these steps.

    Note The following procedure depends on SQL Server system tables. The structure of these tables may vary with different SQL Server versions.

    1. Click Start, point to Programs, click Microsoft SQL Server, and then click Enterprise Manager.
    2. In SQL Server Enterprise Manager, click SQL Query Analyzer on the Tools menu.
    3. Above the Query window, click the Database list, and then click the restored SMS database.
    4. In the Query window in the right pane, copy and paste the following code.
    sp_configure 'allow updates', 1
    go
    reconfigure with override
    go
    begin transaction
    update sysobjects set replinfo = '0' where replinfo >'0'
    5. Click the Parse Query button to look for errors in the code.
    6. Click the Execute Query button to run the query on the restored database.
    7. Verify that all objects in the restored database's sysobjects table have the replinfo attribute set to 0 (zero), and then verify that only one row was affected. If the intended row in the sysobjects table was updated, commit or roll back the transaction by using one of the following commands.
    commit transaction
    go
    rollback transaction
    go
    
    8. Run the following code to set the allow updates attribute back to 0 (zero).
    sp_configure 'allow updates', 0
    go
    reconfigure with override
    go
    You can re-create the replication objects after you upgrade the SMS site database or after you run the the SMS Setup program with the /testdbupgrade switch to test the database.

     

    ------------------------------------------------------------------------------------

     

    I am not requiring the exact functionality described above, but what I *am* doing is backing up a customer's replicated database and restoring it to my local machine.  I cannot restore without preserving the replication settings because the restore fails at the end with the error:  System.Data.SqlClient.SqlError: Cannot drop the table 'dbo.MSpeer_lsns' because it is being used for replication. (Microsoft.SqlServer.Smo).  If I restore preserving the replication settings, the restore is successful, but when I execute sp_removedbreplication, I get the error: 

    Msg 3724, Level 16, State 2, Procedure sp_MSdrop_peertopeer_tables, Line 27

    Cannot drop the table 'dbo.MSpeer_lsns' because it is being used for replication.

     

    So, I cannot remove replication because the table is being used for replication - catch-22.  In SQL 2000 I could use the above functionality to remove replication.  In 2005, I now cannot.  Any help in accomplishing this task would be greatly appreciated.

     

    -Scott

    Tuesday, December 04, 2007 5:11 AM
  • Hi Raul,

     

    Thanks for replying and apologies in my slackness in getting back to you - I have been out of the office.

     

     I do not know how the tables were created - one of processes must have glitched, but they were interfering with our processes as the procs were checking for the existence of similarly named tables using wildcards based on the first few letters and therefore assuming the tables existed. You are correct in that they were temp tables, and I was eventually able to get rid of them by re-booting the server (fortunately a test server) which terminated the spids that created them and hence the sysobjects records.

     

    Thanks again

    David

     

    Wednesday, December 05, 2007 12:48 PM
  • @DDroog

      I am glad to hear that the problem got fixed, and thanks a lot for letting us know that the problem got fixed.

     

    @Scott Bourdeau

       The link you pointed out is a KB for SMS, not SQL Server (they seem to be using SQL Server 7 and SQL Server 8 for this KB though). This document in particular is most likely outdated and I would recommend looking at the link marked in the references (at the bottom of the document) for KB326352. In this KB the author refers to the correct mechanism to remove replication attributes: sp_removedbreplication. We are already working on sending the feedback to the owner of KB835860 to make sure they refer to the proper mechanism to remove replication attributes.

     

      In the case of the particular table that you mentioned, it seems to be a replication table that was introduced in SQL Server 2005. After talking to the experts on replication they mentioned that this is unexpected and that most likely the reason behind it is that the table was explicitly marked for replication. The replication team is already investigating how to prevent this issue based on your feedback, but this seems to be an anomaly and not a common scenario.

     

      If you need assistance with this particular scenario, I would recommend contacting the replication experts using the replication forum, but I need to emphasize that this is an abnormal situation (probably caused by a bug in the product) and any direct system table modification is a workaround for this bug, but not a normal scenario. The same may also apply to other KBs that describe how to modify system tables as a workaround for product bugs on some particular version of the product, but it is not intended to be used as a object access/manipulation mechanism for normal usage.

     

      Thanks,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Wednesday, December 05, 2007 9:16 PM
    Moderator
  • Raul,

     

    Thank you for looking into my situation and informing the replication team of this scenario.  As mentioned in my first post I already unsuccessfully attempted to use the correct mechanism of sp_removedbreplication.  Hopefully this anomaly can be resolved soon.  It is unfortunate we have lost our ability to modify system tables as workarounds for bugs, etc.  BTW, KB326352 also lists the same workaround as KB835860, but the article *does* say it applies only to SQL 7.0 and 2000, so it should be no suprise it does not work under 2005.

     

    Perhaps the engineers *have* overstepped their bounds by disallowing all mods to system tables.  As a software engineer myself, I find our company sometimes also has the inclination to give the customers what we think they need as opposed to what they want.  Since we customers purchase SQL for our use, it would behoove the makers of the product to hear our plea.  If we screw up our SQL installation by incorrectly modifying system tables, the burden is on us.  Judging from the posts in this thread, it would appear the SQL administrators would happily accept the ability to screw up their databases in exchange for also being able to repair them when necessary.

     

    Kind regards,

     

    Scott

    Monday, December 10, 2007 11:50 PM
  •  

    Would this help?:

     

    DECLARE c_db CURSOR FOR

    SELECT [name], * FROM sys.databases WHERE recovery_model = 3

    OPEN c_db

    DECLARE @db varchar(255)

    FETCH NEXT FROM c_db INTO @db

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN

    PRINT('ALTER DATABASE [' + @db + '] SET RECOVERY FULL')

    EXEC('ALTER DATABASE [' + @db + '] SET RECOVERY FULL')

    FETCH NEXT FROM c_db INTO @db

    END

    CLOSE c_db

    DEALLOCATE c_db

     

     

    Try before you die

    Friday, March 07, 2008 1:52 PM
  • Here's a different scenario that I hope I can get some help on.

     

    migrated a db from sql 2000 to sql 2005 by using attach / detach.

     

    all seemed fine at first until a dbcc checkdb was executed.

     

    sql 2k5 reported inconsistencies between sys.columns and sys.objects where there are 26 entries in sys.columns that do not have a corresponding entry in sys.objects i.e they are orphaned entries.

     

    runnning the dbcc checkdb on the same db in sql 2k results in a clean check.

     

    The difficulties now arise in the veritas backup software using the sql agent configured to perform a db check prior to the backup now fails due to these 26 entries and so the backup is not performed.

     

    so to resolve this problem the simplest way would have been to delete these 26 entries.

     

    any suggestions and help greatly appreciated

     

     

    Tuesday, May 06, 2008 1:15 PM
  • Have you tried running using the Repair options of DBCC CHECKDB to repair this?
    Tuesday, May 06, 2008 2:47 PM
    Moderator
  • yep

     

    tried both  REPAIR_REBUILD  and  REPAIR_ALLOW_DATA_LOSS 

     

    I'm working on a copy of the db so can do anything with it at the moment

     

    both still give me the message

     

    Msg 8992, Level 16, State 1, Line 1

    Check Catalog Msg 3853, State 1: Attribute (object_id=1413632129) of row (object_id=1413632129,column_id=1) in sys.columns does not have a matching row (object_id=1413632129) in sys.objects.

     

     

     

    Tuesday, May 06, 2008 3:45 PM
  •  Michael Hotek wrote:


     

      (There isn't a single example in this thread which would have required direct system table modification in SQL Server 2000 and each case offer thus far for screwing with the system data is a result of bad code meeting bad practices.)

     

     

    In every single instance that you will dream up to screw around with the system tables, SQL Server 2005 has an interface that is supported to make the changes that you need to make.

     



    Here's one for you then:

    I need to change about 25,000 unique clustered indexes into primary keys.  The indexes meet all requirements to be PK's.  In SQL 2000 this worked perfectly:  update sysindexes set status = 18450 where name = 'PS_ADDL_PAY_DATA'

    In SQL 2005 this won't work.  I am now fully protected from creating a DR server.  This is not exactly an incentive to upgrade, or even continue with the MS product.

    I wouldn't have to do this if MS replication would treat the unique clustered Indexes as PK's, but it won't even though they are identical in every way except for that status flag.  The schema is not mine, it's PeopleSoft's and I will stipulate that it was designed by a troop of particularly stupid baboons. 

    So, where is the interface for this one?  And, no, dropping and recreating all those indexes is not a viable option.  That would take days.  The changes I would make to sysindexes would take seconds. 


    Tuesday, July 01, 2008 7:18 PM
  • No, a unique clustered index is NOT the same as a primary key.  A primary key does NOT allow NULLs.  A unique index does allow at least 1 NULL.  That is a VERY big difference and therefore, a unique, clustered index cannot simply be turned into a primary key by SQL Server.  This is also the reason that the replication engine does not magically treat unique indexes as if they were primary keys, because it is impossible to match something with WHERE Column = @parm when @parm = NULL since it would match absolutely nothing in the table immediately causing the subscriber to be out of synch with no ability to synch it up, so if replication allowed you to do this, the next that that would happen would be calls into PSS complaining that "SQL Server doesn't work".

     

    The reason that PeopleSoft uses unique indexes instead of primary keys is because their database is designed to be as platform independent as possible, so you wind up with the least common denominator - ANSI standard SQL without any platform specific extensions or syntax.  The design of the PeopleSoft database is not Microsoft's problem, nor is it a problem for SQL Server to solve.  If you don't like the design of the PeopleSoft database, then get rid of PeopleSoft and use something else.

     

    If your requirement to use a database platform is to be able to muck around with the system tables and directly manipulate the data stored, good luck.  Neither SQL Server, Sybase, DB2, nor Oracle allow you to do this.

     

    Your assertion that you cannot create a DR server also doesn't wash.  You can use failover clustering, log shipping, and database mirroring, NONE of which will require you to do anything at all with the table structure in your PeopleSoft database.  Additionally, if your goal is to make the entire database highly available, replication would be the 4th option on my list of HA technologies to utilize with Database Mirroring being at the top of the list wrt to PeopleSoft.

     

    There DOES exist a way to directly manipulate the system tables, even in SQL Server 2005, as long as you have appropriate SQL Server permissions and can take the instance offline.  I'll leave that method up to someone else, because I am still VERY adamant about NOT mucking around with the system tables.  I've seen WAY too many systems blown up because people did this.  Additionally, directly modifying the system tables is NOT SUPPORTED and I do not feel the least bit sorry for anyone who blows up a database, calls PSS, and is told they can't help because directly changing the system tables is not supported.

     

    Friday, July 04, 2008 7:32 AM
    Moderator
  •  Bryden wrote:

     

     

    Msg 8992, Level 16, State 1, Line 1

    Check Catalog Msg 3853, State 1: Attribute (object_id=1413632129) of row (object_id=1413632129,column_id=1) in sys.columns does not have a matching row (object_id=1413632129) in sys.objects.

     

     

     

     

    I'm having the same error on a Db obtained making restore on a sql2005 server of a backup coming from a sql2000 server.

    Same error (obviulsy different ID) and some problem to try to correct it (no possibility to change the sys table and checdk that isn't able to correct it.

     

    Have you solved the matter in some way?

    If someone can halp it is wellcome.

    Thank's in advance

    Thursday, July 10, 2008 1:40 PM
  • Bryden,

     

    Please take a look at Paul Randalls Blog Post for resolution to this specific problem:

     

    TechEd Demo: Using the SQL 2005 Dedicated Admin Connection to fix Msg 8992: corrupt system tables

     

    Pay attention to his text in Red.  The fix to this is undocumented and unsupported, so you do it at your own risk, but his post shows how to solve the problem.
    Thursday, July 10, 2008 1:44 PM
    Moderator
  • Thank's, that solves my problem!!!

    Friday, July 11, 2008 8:46 AM
  • OK, I see a lot of people here saying you never need to modify the system tables.  So how do I get around this one then?  Our live production server has got it's knickers in a twist when we changed it's name.  We can connect to it fine through management studio and .NET.  But VB6 is screwed.  The Reason?  We changed the name from NEW_OUR_SERVER to OUR_SERVER.  Everything links fine to it as OUR_SERVER, but vb6 fails when it tries because VB6 verifies the name in Sys.Servers.  And in Sys.Servers it still says NEW_OUR_SERVER for 'name' and data_source.  How am I going to change Sys.Servers to say OUR_SERVER???!
    Tuesday, September 30, 2008 12:34 PM
  • Use sp_dropserver and sp_addserver in SQL:

     

    http://msdn.microsoft.com/en-us/ms143799.aspx

     

     

    Tuesday, September 30, 2008 2:10 PM
    Moderator
  • thanks, that did it!
    Tuesday, September 30, 2008 3:25 PM
  • Through some how I want to change system catalog (syscolumns 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 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.

     

    Any help?

     

     

    Thursday, October 23, 2008 7:18 PM
  • If you require the colids to not have gaps, you need to use a script that recreates the table.  You should not edit the system tables to do this.  For example, the following change:

     

    Code Snippet

    CREATE TABLE [dbo].[TestTable](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [value1] [int] NULL,

    [value2] [int] NULL,

    [value3] [int] NULL,

    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED

    (

    [id] ASC

    )

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[TestTable]

    DROP COLUMN [value3]

    GO

    ALTER TABLE [dbo].[TestTable]

    ADD [value4] [int] NULL

    GO

    select * from sys.columns where object_id = object_id(N'TestTable')

     

     

    Will leave gaps in the table column id's.  To make the same change above without leaving gaps, you need to do the following:

     

    Code Snippet

    CREATE TABLE dbo.Tmp_TestTable

    (

    id int NOT NULL IDENTITY (1, 1),

    value1 int NULL,

    value2 int NULL,

    value4 int NULL

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT dbo.Tmp_TestTable ON

    GO

    IF EXISTS(SELECT * FROM dbo.TestTable)

    EXEC('INSERT INTO dbo.Tmp_TestTable (id, value1, value2)

    SELECT id, value1, value2 FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')

    GO

    SET IDENTITY_INSERT dbo.Tmp_TestTable OFF

    GO

    DROP TABLE dbo.TestTable

    GO

    EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT'

    GO

    ALTER TABLE dbo.TestTable ADD CONSTRAINT

    PK_TestTable PRIMARY KEY CLUSTERED

    ( id )

    GO

    select * from sys.columns where object_id = object_id(N'TestTable')

     

     

    Thursday, October 23, 2008 7:33 PM
    Moderator
  •     I have a different issue of sorts but along the same subject.  We do development work for a mid-market ERP package.  Once in a while we receive a copy of the customer's database to work on issues.   Part of this ERP program is a synchronization utility that synchs the database to the server so it can be registered with the ERP's company in order to use the program.  Well one of things this Synch utility does is recreate all the users it finds in sysUsers on the SQL Server if they do not exist.  As we don't want our server to have all these users, we use to just delete them from sysUsers before running the Synch Utility.   That worked fine in SQL 2000 but now we see that it is impossible in SQL 2005.  This being the case, how do you recommend we delete these users now if the old method no longer works?

     

    Thanks

    Wednesday, October 29, 2008 8:53 PM
  •  Lou_Davis wrote:

    As we don't want our server to have all these users, we use to just delete them from sysUsers before running the Synch Utility.   That worked fine in SQL 2000 but now we see that it is impossible in SQL 2005.  This being the case, how do you recommend we delete these users now if the old method no longer works?

     

    The supported method in 2005 is to use the DROP USER DDL command for each user:

     

    http://msdn.microsoft.com/en-us/library/ms189438(SQL.90).aspx

     

    For SQL Server 2000 you should have been using sp_dropuser:

     

    http://msdn.microsoft.com/en-us/library/aa933287(SQL.80).aspx

     

    or sp_revokedbaccess:

     

    http://msdn.microsoft.com/en-us/library/aa238876(SQL.80).aspx

     

     

     

    Wednesday, October 29, 2008 9:04 PM
    Moderator
  •  

      Well it took a little bit of T-SQL Gymnastics but I created a script which goes through sysUsers and transfers schema permissions to the one remaining user in the database (received error for this when trying to drop the users) and then dropped the users that were no longer needed.  Thanks for the assist.

     

    Wednesday, October 29, 2008 11:29 PM
  • To put in simple words, as Seniors and Gurus above said, Catalogs should not be played around with.But on an emergency situation which is very rare,,,it helped me as follows,,,in a simple way

    sp_configure 'allow updates',1
    reconfigure with override

    Apply your sql statements

    For Eg:
    --Do necessary action
    --On an Emergency scenario, I had to repair the File
    --Alter database abc_bdo set emergency--
    --DBCC CHECKDB(abc_bdo, REPAIR_ALLOW_DATA_LOSS)

    Below action is required to get the state to its normal fashion
    --Sp_configure 'allow updates', 0
    --RECONFIGURE WITH OVERRIDE


    amateur

    • Edited by Suresh Kumar VC Wednesday, November 10, 2010 9:34 AM Commented as a KB
    Tuesday, March 16, 2010 6:32 PM
  • simply confirm changes usin following statement:

     

    reconfigure

     

    with override

    go

    Tuesday, May 25, 2010 9:52 AM