March 10, 2008 at 11:28 am
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
March 10, 2008 at 9:20 pm
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
Consultant - Straight Path Solutions
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
March 11, 2008 at 3:14 am
[font="Verdana"]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.[/font]
[font="Verdana"]- Deepak[/font]
March 11, 2008 at 2:55 pm
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.
Jack Corbett
Consultant - Straight Path Solutions
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
March 12, 2008 at 1:18 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2008 at 1:26 am
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
March 12, 2008 at 2:38 pm
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]
March 21, 2008 at 1:21 pm
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!;)
March 21, 2008 at 1:50 pm
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
Learn to play, play to learn !
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[/url]
- 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
October 23, 2008 at 1:26 pm
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
October 23, 2008 at 1:34 pm
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
Consultant - Straight Path Solutions
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
October 23, 2008 at 1:45 pm
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
October 23, 2008 at 1:47 pm
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
October 23, 2008 at 1:58 pm
Maqsood Ahmad (10/23/2008)
Thanks for your prompt replyI 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
October 23, 2008 at 2:17 pm
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.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply