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 ««123»»

sp_configure 'Allow updates', 1 DOES NOT WORK Expand / Collapse
Author
Message
Posted Thursday, October 23, 2008 1:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 11,265, Visits: 13,027
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

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
Post #590750
Posted Thursday, October 23, 2008 1:45 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 42,845, Visits: 35,974
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 2008, MVP
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

Post #590757
Posted Thursday, October 23, 2008 1:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 7, 2009 7:12 PM
Points: 6, 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
Post #590759
Posted Thursday, October 23, 2008 1:58 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 42,845, Visits: 35,974
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 2008, MVP
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

Post #590766
Posted Thursday, October 23, 2008 2:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 7, 2009 7:12 PM
Points: 6, 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.
Post #590780
Posted Thursday, October 23, 2008 2:58 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 42,845, Visits: 35,974
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 2008, MVP
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

Post #590811
Posted Thursday, October 23, 2008 11:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 7,005, Visits: 8,453
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #590981
Posted Saturday, June 20, 2009 6:39 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:16 AM
Points: 196, Visits: 658
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
Post #738810
Posted Friday, February 10, 2012 6:01 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:44 AM
Points: 986, Visits: 455
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
Post #1250228
Posted Friday, February 10, 2012 6:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:44 AM
Points: 986, Visits: 455
Forget my last post
I can use the system Stored Procedures to do this.
Post #1250230
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse