required the way to update the System Catalog in Sql Server 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » required the way to update the System Catalog...

required the way to update the System Catalog in Sql Server 2005

Rate Topic

Display Mode

Topic Options

Author

Message

Saravanakumar.Rathinam

Posted Wednesday, January 30, 2008 5:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, Visits: 493

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.

Post #449298

Andras Belokosztolszki

Posted Wednesday, January 30, 2008 6:12 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585

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

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Post #449314

Paul Randal

Posted Wednesday, January 30, 2008 7:40 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:19 AM
Points: 1,910, Visits: 1,606

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

Post #449776

Saravanakumar.Rathinam

Posted Wednesday, January 30, 2008 10:10 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, Visits: 493

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

Post #449797

Paul Randal

Posted Wednesday, January 30, 2008 10:17 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:19 AM
Points: 1,910, Visits: 1,606

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

Post #449798

Saravanakumar.Rathinam

Posted Wednesday, January 30, 2008 10:26 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, Visits: 493

for example I'm trying to block the statement like drop a objects;
I don't want to drop a table even accidental.

Post #449804

matt stockham

Posted Wednesday, January 30, 2008 10:30 PM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, June 06, 2013 9:36 AM
Points: 750, Visits: 2,946

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.

Post #449805

Paul Randal

Posted Wednesday, January 30, 2008 10:43 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:19 AM
Points: 1,910, Visits: 1,606

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

Post #449808

ericksonfabricio

Posted Wednesday, November 19, 2008 12:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, March 10, 2013 11:11 PM
Points: 2, Visits: 107

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

Post #605342

Paul Randal

Posted Wednesday, November 19, 2008 12:56 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:19 AM
Points: 1,910, Visits: 1,606

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

Post #605366

« Prev Topic | Next Topic »

Permissions