sp_configure 'Allow updates', 1 DOES NOT WORK

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » sp_configure 'Allow updates', 1 DOES NOT WORK...

21 posts, Page 1 of 3

1 2 3 »»»

sp_configure 'Allow updates', 1 DOES NOT WORK

Rate Topic

Display Mode

Topic Options

Author

Message

Matthew Mark-379998

Posted Monday, March 10, 2008 11:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, March 21, 2009 10:27 PM
Points: 8, Visits: 26

I've read many postings saying how this works. Everything I've tried hasn't. This dis-allowing of system database updates has already caused me many wasted hours. I knew how to modify what I needed modified in SQL 2000.

Situation: Using Sql Server 2005, Service pack 2 -

I've been doing extensive testing getting ready to move merge replication from Sql 2000 to Sql 2005. Since MANY things are different, and the "wonderful" Microsoft Update Advisor does very little except point out the obvious, much work is needed to comb through views, procedures, etc looking for these changes.

It's probably rather easy if you haven't used a lot of procedures, functions and replication, however, that's not where I find myself.

At some point, on my publisher server, the distribution database lost a reference point. (so much for Microsoft not letting you change something important). The name distributor shows in my "System Databases", with no "+" alongside. No properties etc are available.

I saw this, and tried to drop the distributor. did not work.

I thought, I'll stop the service and delete the database files for distribution db.

After restarting service, it still showed and I remembered, "Oh, there's an entry in the master tables sys.databases and sys.sysdatabases for distribution db.

I thought then, "I'll just delete them, and run scripts to recreate."

I cannot update system catalogs.

Okay, so I tried:

Use Master
go
sp_configure 'Allow updates',1
go
reconfigure with override
go

these ran fine.

Then I tried:

Delete sys.databases where database_ID = 16

or

Delete sys.sysdatabases where DBID = 16

Same error as always: cannot do ad hoc updates on system catalogs.

Apparently some people are succeeding with this. I'd appreciate any assistance finding ways to get around all the limitations of Microsofts "Improvements."

Sound a bit cynical? Well, I have no reason to praise Microsoft yet.

Thanks a lot.

Matthew

Post #466853

Jack Corbett

Posted Monday, March 10, 2008 9:20 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

sys.databases and sys.sysdatabases are both views in SQL 2005. The base tables are hidden. Not sure what you need to do.

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 #467125

Deepak-405225

Posted Tuesday, March 11, 2008 3:14 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, October 26, 2012 2:14 PM
Points: 402, Visits: 598

You cannot modify system tables in Sql Server 2005 normally but you can modify the same if you are connected using Dedicated Administrator Connection (DAC). Refer the below link regarding the same,

http://msdn2.microsoft.com/en-us/library/ms189595.aspx

Please note that it is NOT advisable to modify system tables and if you perform the same it is at your own risk.

- Deepak

Post #467241

Jack Corbett

Posted Tuesday, March 11, 2008 2:55 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Got this in my SQLServerPerformance.com newsletter today:

Ad hoc updates to system catalogs are not allowed.
Error Message:
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
Severity level:
16.
Description:
This error message appears when you try to run an UPDATE statement against SQL Server's system tables.
Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.
Resolution:
Errors of Severity Level 16 are generated by the user and are corrigible by the user. The statement cannot be executed this way. You should refrain from manipulating system tables as you might render the database unusable.
Versions:
All versions of SQL Server.
Example(s):
UPDATE sysobjects SET name = NULL
Remarks:
In SQL Server 2000 there was an extended option that would allow running modification queries against system tables. In SQL Server 2005 this method has been removed. Although there are still other ways, all are unsupported and you are better off calling SQL Server Product Support.

Post #467771

GilaMonster

Posted Wednesday, March 12, 2008 1:17 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 37,731, Visits: 29,994

The DAC connection will let you read the base system tables, not change them.
You cannot (and should not) change the system tables in sQL 2005.

What are yuo tryng to do? There's very likely another way.

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 #467927

Phillip Cox

Posted Wednesday, March 12, 2008 1:26 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, October 12, 2012 7:55 AM
Points: 2,133, Visits: 604

Hi,

Ok, slightly confused as to why "At some point, on my publisher server, the distribution database lost a reference point"? Can you provide some more information as to how you upgraded from SQL 2000 to SQL 2005. This will allow us to figure out what went wrong, as trying to modify system tables means something has gone very wrong.

Thanks,
Phillip Cox
MCITP - DBAdmin

Post #467930

Bob Fazio

Posted Wednesday, March 12, 2008 2:38 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, January 06, 2012 2:39 PM
Points: 954, Visits: 683

I'm thinking that the issue is that the name of the DB has "Special characters" in the name.

Just place []'s around the Name

CREATE DATABASE [This Is a Stupid DB Name + 7] 

DROP DATABASE [This Is a Stupid DB Name + 7]

Post #468397

TraderSam

Posted Friday, March 21, 2008 1:21 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 12:13 PM
Points: 307, Visits: 565

I know this is a little late, but thought I would post it in case someone else came across this post looking for answers...

Matthew Mark (3/10/2008)

I cannot update system catalogs.

Okay, so I tried:

Use Master
go
sp_configure 'Allow updates',1
go
reconfigure with override
go

these ran fine.

From SQL Server 2005 BOL...

The allow updates option is still present in the sp_configure stored procedure, 
although its functionality is unavailable in Microsoft SQL Server 2005 
(the setting has no effect). 
In SQL Server 2005, direct updates to the system tables are not supported.

So the command is still there in SQL 2005, but just doesn't do anything...nice!

Matthew, what did you do to resolve your situation? The obvious would be to rewrite if that is even an option. I have a similar issue and I am researching the different options to present to the 'powers'.

If it was easy, everybody would be doing it!;)

Post #473076

ALZDBA

Posted Friday, March 21, 2008 1:50 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 6,862, Visits: 8,049

as already stated more than once.

SQL2005 : nomore update to system objects. And that's a nice job !

I guess with "Delete sys.sysdatabases where DBID = 16" you're trying to fool sqlserver a database has been droped, That would be a inconsitancy to the actual situation.

Why not just perform

declare @sql nvarchar(500)
set @sql = " --drop database ['+database_name(16)+'];'
-- commented the @SQL to prevent copy paste test disaster !!
exec (@sql)

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't 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" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #473086

Maqsood Ahmad

Posted Thursday, October 23, 2008 1:26 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 07, 2009 7:12 PM
Points: 6, Visits: 42

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. Why I want to change? When I delete a column from a Table, these colid values do not update automatically but I want to have consecutive number without any gap like (1,2,3,4,5)Say I have 5 columns in a table then in syscolumns table value of last row's colid column will be 5.(1,2,3,4,5) If i delete last column and add a new column the new column's colid value is 6. It should be 5 after automatic updated but its not.

I am facing problems with these gaps. My application uses these number and due to these gaps my application thinks that some columns are missing because these numbers are not consecutives.

Actually we have our own data access layer. We are not using DataSet, DataTable etc. because we have our own classes to wrap SQL Tables in C# code.

Any help?
Thanks
MAQSOOD

Post #590743

« Prev Topic | Next Topic »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions