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


sp_configure 'Allow updates', 1 DOES NOT WORK


sp_configure 'Allow updates', 1 DOES NOT WORK

Author
Message
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41897 Visits: 14925
Maqsood,

If you have read through this thread you should have understood that there is not a way to update system tables in SQL Server 2005/2008.

IMHO, you need to change your .NET code to not rely on this data since there is no way to guarantee it will be consecutive. You could dummy up another user table to "duplicate" the sys.columns table that you could control. I'm not sure that this would be the answer either.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
GilaMonster
GilaMonster
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210597 Visits: 46249
Maqsood Ahmad (10/23/2008)
Through some how I want to change system catalog (syscolumns table)

In syscolumns table there is a column "colid" I want to change the value of this colid.


Nope. Can't be done. System tables can not be modified in 2005 and higher. Besides, syscolumns isn't even a table. It's a view these days

Besides, if you did find a way to do this, the first time you made a change you would mess the system up so badly that you would probably have to restore a backup. The colids are used internally in the indexes, in the constraints and in a number of other places. If you did manage to change a colid and you didn't find every single place it was referenced, you would be corrupting the database and potentially it would become suspect the next time SQL has to recover it (like at DB start)

Either add your own metadata column that you maintain, or change the .net code so that it can handle gaps.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Maqsood Ahmad
Maqsood Ahmad
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 42
Thanks for your prompt reply

I understand that in SQL Server 2005 we can't update it but because we have migrated our application from SQL Server 2000 to 2005 and in 2000 we were able to change these numbers. everthing was fine in 2000.
What i think that if we delete a column from any table SQL Server should update these number as well.
Now its hard to change whole application layer at this stage
GilaMonster
GilaMonster
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210597 Visits: 46249
Maqsood Ahmad (10/23/2008)
Thanks for your prompt reply

I understand that in SQL Server 2005 we can't update it but because we have migrated our application from SQL Server 2000 to 2005 and in 2000 we were able to change these numbers. everthing was fine in 2000.


Maybe, maybe not. Run DBCC CHECKDB('< Database Name > ') WITH NO_INFOMSGS on that db and see what errors checkDb returns. In 2000 CheckDB disn't check the metadata. In 2005 it does. You may already have serious problems with that DB that you're not yet aware of.

What i think that if we delete a column from any table SQL Server should update these number as well.


Why? The colid isn't a meaningful number. It's just an artificial key - a number that the DB engine can use to reference the column elsewhere in the metadata

Now its hard to change whole application layer at this stage


Even in SQL 2000 there were warnings and cautions about changing the system tables. In fact MSDN states:

Caution Updating fields in system tables can prevent an instance of Microsoft® SQL Server™ from running or can cause data loss. On production systems, you should not enable allow updates except under the direction of Microsoft Product Support Services. Because system tables are critical to the operation of SQL Server, enable allow updates only in tightly controlled situations.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Maqsood Ahmad
Maqsood Ahmad
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 42
I executed this command
DBCC CHECKDB('AlarmSwitch') WITH NO_INFOMSGS
It took about 3 minutes and then I got message 'Command(s) completed successfully.'
Its mean there is no problem with my DB. Am I right?

You are right we should not rely on these values. In fact we should store this kind of info in our own tables but because this db access layer was desinged by an other team for us about five years ago (for SQL Server 200) and they used this approach.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210597 Visits: 46249
Maqsood Ahmad (10/23/2008)
I executed this command
DBCC CHECKDB('AlarmSwitch') WITH NO_INFOMSGS
It took about 3 minutes and then I got message 'Command(s) completed successfully.'
Its mean there is no problem with my DB. Am I right?


Yup. No problems there

You are right we should not rely on these values. In fact we should store this kind of info in our own tables but because this db access layer was desinged by an other team for us about five years ago (for SQL Server 200) and they used this approach.


Unfortunately you're going to have to change your app. The other team designed things in a really bad way that went against every recommendation and best practice there is regarding the system tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28033 Visits: 8986
you're suffering another bad practice .....
and you'll have to pay the price to get rid of it.

Don't mess with system objects !

That's like: I'm sysadmin of the dbserver hosting our paycheck system, so I'll give myself a raise because I know which column in which table to update .... just because I can....
Someone will notice in the long run...


Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
C.K.Shaiju
C.K.Shaiju
SSC Eights!
SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)

Group: General Forum Members
Points: 924 Visits: 711
Hi Maqsood,

Can you recreate the table at the time of dropping or creating a new field? Before that you have to move/copy the existing data to a temporary table and copy back after the table created. If that is the case colid will be in sequence and also no need to touch the system tables.

Shaiju C.K.

_____________________________________________
One ounce of practice is more important than tonnes of dreams
SimonH
SimonH
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2388 Visits: 1039
I need to Automatically Update the system Catalogues on 2008.
Basically I want Automatically remove the extended properties for a given table,
Now I know this can't be done, is there another way around this?

Exec sp_configure 'allow updates', 1 Reconfigure with override
waitfor delay '00:00:01'

DELETE
from sys.extended_properties
WHERE major_id =446624634


Exec sp_configure 'allow updates', 0 Reconfigure with override
SimonH
SimonH
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2388 Visits: 1039
Forget my last post
I can use the system Stored Procedures to do this.
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