SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Saravanakumar.Rathinam
Saravanakumar.Rathinam
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 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.
Andras Belokosztolszki
Andras Belokosztolszki
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5467 Visits: 1585
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 Smile

Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8178 Visits: 1719
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
Saravanakumar.Rathinam
Saravanakumar.Rathinam
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 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
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8178 Visits: 1719
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
Saravanakumar.Rathinam
Saravanakumar.Rathinam
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 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.
matt stockham
matt stockham
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2632 Visits: 3178
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.
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8178 Visits: 1719
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
ericksonfabricio
ericksonfabricio
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 122
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
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8178 Visits: 1719
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search