Up system table

Question

Post reply

Up system table

AIRWALKER-375999

Hall of Fame

Points: 3040
More actions
October 18, 2006 at 7:13 am

#75077

Hi guys, I need to update a system table in sql server 2000. I know this isnt reccomended but I need to do it.
I've used sp_configure to allow updates, however when I try to update thet syscomments table I get the following message:
'syscomments' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]System table 'syscomments' was not created, because ad hoc updates to system catalogs are not enabled.
any ideas where Im going wrong?
thanks in advance

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Mark,

You will be loosing the support from Microsoft, if you try to change any of the system catalogs.

Thanks

Lucky

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Ya and there are many other ways to update the text of the stored procs / views... without going to that extreme.

What do you need to do exactly?

AIRWALKER-375999 Hall of Fame Points: 3040 More actions · Answer 3

I've been asked to change the datatype of the 'text' column in syscomments from nvarchar(4000) to ntext.

so far I havnt been successful in doing this. any suggestions would be greatly appreciated

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

So your boss wants you to redesign sql server. I think your time would be better spent doing something else... something doable and usefull.

Also this would definitly void any further support from Microsoft.

Why exactly does he want you to do this?

AIRWALKER-375999 Hall of Fame Points: 3040 More actions · Answer 5

Its an order that come down from 'on high', theres a problem with certain records being too big, a change of datatype has been suggested.

our stored procedures are more than 8000 characters long, The maximum length for syscomments is 8000 characters if im not mistaken. And syscomments is where all our storedprocedures, triggers etc are stored. It has been suggested that changing the datatype from nvarchar to ntext would resolve the issue

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

I'm glad to informa you that Microsoft already thaught of that problem. If the text size goes beyong 4000 characters, a new row is inserted in SysComments to make sure everything is saved.

Now back to that problem. Is there a problem with any object you created that would make you think that it's too big for sql server to handle? Or is this just theorical problematics??

Lowell SSC Guru Points: 323518 More actions · Answer 7

Rgr is absolutely right....if you had a stored proc that was 30K in length, it would just occupy 7 rows in the syscomments table(30K /4K = 7 rows)

your boss on high is second guessing a functionality that he doesn't fully understand. While the table might be limited to a 4K slice of data, but the APPLICATION that uses the data is not limited.

I would speculate that if you actually were able to change the datatype, you would no longer be able to use views, functions or procedures...why? because the extended functions in the dll's that allow SQL server to CREATE the compiled objects, and store the data in syscomments ASSUME the field is a specific data type...change the underlying data type and they will no longer be compiled correctly.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

I would go as far as saying that the queries reading from syscomments would make the app fail right there, but that's another story...

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 9

is there a way to rebuild/defrag the indexes of a system table? just done a showcontig on sysobjects and the indexes r a mess!!!!

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 10

Wow, not sure why you guys want to mess with the system tables. The design dates back to 98 with SQL Server 7 in the beta 3 design and has worked great for 9 years without messing with the tables.

No matter who "on high" suggests it, you don't change the structure of the system tables. It's asinine. Tell them to come and explain it here and we'll have someone from Microsoft tell them better.

If your stored procedures don't fit in text, they're still there in compiled form. If you plan on using syscomments as your VCS, that's stupid as well. Go get Sourcesafe/Subversion/Vault, or some real system.

The system tables have their own indexes and don't need to be defragged. They don't get enough updates to need it. They're also not highly involved in your queries. You don't "scan" sysobjects, you seek to the object you need.