January 30, 2008 at 5:44 am
In sql server 2005, I am trying to edit the type / replinfo values in sysobjects, I got the error message "update or insert of view or function 'o' failed because it contains a derived or constant field"
Steps I did in SqlServer 2005
1. Make the server as a single user mode. using sqlservr.exe -m -c -s
2. i'm open one more cmd window and login the server using osql
3. then, i configre the allow update as 1 .. exec sp_configure 'allow updates', '1'
4. then, i provide " RECONFIGURE with OVERRIDE"
5. Now i am trying to update the sysobject column values using the below command ->
update o set replinfo = 1 from sysobjects o where name = 'TEST'
It is not allow me to update the values as 1.
is any other way to update the system catalog in Sql Server 2005 ?
Awaiting your replies. thanks i advance.
January 30, 2008 at 6:12 am
Please note that the sysobjects is no longer a system table on SQL Server 2005. These have been replaced with system views, so sysobjects is a system view, which is in addition is there only for compatibility reasons. So instead of sysobjects you should use sys.objects, but this is a view as well. If you want to play around with the real system tables, you can look at them via an admin connection. The tables that the sysobjects view is taking its information from are sys.sysschobjs , sys.syssingleobjrefs.
Warning: make sure you know what you are doing 🙂
Andras
January 30, 2008 at 7:40 pm
Following on from Andras post (and I agree - don't go changing the system tables) - what is it you're trying to do that you can't do with regular DDL? There's a good reason that you can't normally change the system tables in 2005 - it can cause nasty problems.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 30, 2008 at 10:10 pm
Thanks for your reply!
How can i access these tables sys.sysschobjs , sys.syssingleobjrefs.
Can you provide me the steps to accessing these table. even i am trying the sa password i am not able to select these tables.
Thanks & regards
Saravanakumar.R
January 30, 2008 at 10:18 pm
What is it you're really trying to do? You shouldn't need to manually change system tables in 2005.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 30, 2008 at 10:26 pm
for example I'm trying to block the statement like drop a objects;
I don't want to drop a table even accidental.
January 30, 2008 at 10:30 pm
You should investigate DDL triggers - search on that and you will find that what you are trying to do is often given as an example of their usage.
Screwing around with system tables is likely to make you wish you had only accidentally dropped a table.
January 30, 2008 at 10:43 pm
Absolutely - DDL triggers is the way to go. I was just about to post an example for you that prevents DDL when I remembered that Kimberly had blogged it back in July. Check out http://www.sqlskills.com/blogs/kimberly/2007/07/24/EXECUTEASAndAnImportantUpdateYourDDLTriggersForAuditingOrPrevention.aspx.
Hope this helps.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 19, 2008 at 12:23 pm
Hi everyone,
In my case, I have any tables that I renamed your names. But, I have any procedures that uses the tables that I have renamed.
I want to find these tables automatically in all my procs and replace your names. To do this, I need to update syscomments system table but I can´t do this.
How can I active my goal?
Thanx!
Erickson Fabricio
November 19, 2008 at 12:56 pm
No - if you change table names, you need to redefine your SPs. You shouldn't change the system tables manually to do this. There are various tools that you can buy to assist with large-scale schema changes (like MS's Datadude for instance).
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply