Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

required the way to update the System Catalog in Sql Server 2005 Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2008 5:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, January 30, 2008 6:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Wednesday, January 30, 2008 7:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Posted Wednesday, January 30, 2008 10:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, January 30, 2008 10:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Posted Wednesday, January 30, 2008 10:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, January 30, 2008 10:30 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Saturday, October 18, 2014 9:34 AM
Points: 754, Visits: 3,164
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
Posted Wednesday, January 30, 2008 10:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Posted Wednesday, November 19, 2008 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 10:44 AM
Points: 2, Visits: 120
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
Posted Wednesday, November 19, 2008 12:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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 »

Add to briefcase

Permissions Expand / Collapse