| Author |
Topic |
|
DrakeFrost
Starting Member
7 Posts |
 Posted - 2011-06-30 : 12:00:34
|
| Greetings, all. I am (relatively) new to SQL server administration, and unfortunately, most of what I have learned so far has been because I really got thrown into this feet-first, so please bear with my ramblings... Here is my situation:I am working on planning an upgrade to the SQL database of our ticketing system application, and am currently working with a backup of the data on a test server. I restore the database just fine, but the upgrade complains about a foreign key constraint from one of the views. some digging revealed that there are actually 3 foreign key records in the [sys].[foreign_keys] view. I want to remove these 3 records and see if that allows the upgrade to complete, but I am not having much luck in getting the SQL server to process the request. Some googling for this led me to the mystical incantation of:use masterexec sp_configure 'allow updates', '1'RECONFIGURE WITH OVERRIDEBut even after that, trying to remove the records still results in a MSG 259: ad hoc updates to system catalogs are not allowed. I feel sure that the solution to allow these actions to work is something simple that I am overlooking.Any help would be greatly appreciated. Oh, also, some specs:this is SQL Server 2008 R2 running on Win2008 R2, as well. i am using SQL Server Management Studio, an dam logged in as the sa user. thanks.DrakeFrost |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-30 : 12:09:45
|
| DO NOT edit the system tables. You're asking for nasty database corruption. Your 'mystic incantation' worked on SQL 2000 and caused all sorts of hell when people thought they could just mess with the internal tables without understanding the consequences. It does not work on SQL 2005 or above.To drop a foreign key, you need the name and the table that it is on. Once you have that (and you can query both from that catalog view) thenALTER TABLE <Table name> DROP CONSTRAINT <Foreign key name>--Gail ShawSQL Server MVP |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-30 : 12:11:22
|
| Don't try to update system views - even if you could it would be a bad idea unless you the structures.This is just telling you that there are foreign key constraints on tables - you need to drop those foreign keys then the entry in the view will also disappear - but you might affect the integrity of your data going forward as the foreign keys are probably there for a purpose.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-30 : 12:25:45
|
| Would also like to see the exact error message.FKs shouldn't affect the upgrade. |
|
|
|
DrakeFrost
Starting Member
7 Posts |
Posted - 2011-06-30 : 13:08:06
|
| I understand that trying to edit the system tables is a Bad Thing(TM). Unfortunately, these keys are part of a view, not a table, and the command:ALTER VIEW <view> DROP FOREIGN KEY <keyname>gives me a syntax error at the word DROP. I'm sure I'm just using the wrong incantation there (again, haven't had to do much in the way of altering/deleting things from this database). As to teh specific error message I get, it reads:-2147217900 [Microsoft][ODBC SQL Server Driver][SQL Server]The DELETE statement conflicted with the REFERENCE constraint "FKProcess ManaSECONDARY_OWNER"/ The conflict occurred in database "SDE",table "_SMDBA_.Process Mana",column "SECONDARY_OWNER".Thoughts?DrakeFrost |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-30 : 13:16:40
|
| >> these keys are part of a view, not a tableAre you sure about that?Do you have the name of the view?Are you sure it's a view?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-30 : 13:16:41
|
| Foreign keys cannot be declared on views, only tables. You can remove the constraint with the following:ALTER TABLE [SDE].dbo.[_SMDBA_.Process Mana] DROP CONSTRAINT [FKProcess ManaSECONDARY_OWNER] |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-30 : 13:22:07
|
| Foreign keys can't be a part of a view, they can only be on a table (and ALTER VIEW can only be followed by a single select statement).Re the system tables, it's not a Bad Thing, it's terrible if you don't know what you're doing. There's no referential integrity in the system tables so if you could delete from the base table (sys.foreign_keys is a view), you'd leave behind all the referencing rows (like in the base table for sys.foreign_key_columns) and your integrity checks would fail from that point onwards and all sorts of other fun things break (like upgrades)What does this return? (make sure you run it in the database SDE)SELECT name, SCHEMA_NAME(SCHEMA_ID) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName FROM sys.foreign_keysWHERE name = 'FKProcess ManaSECONDARY_OWNER'--Gail ShawSQL Server MVP |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-30 : 13:47:47
|
| One other thing...why is an upgrade trying to delete rows in one of your tables?What exactly are you doing when you get this error? |
|
|
|
DrakeFrost
Starting Member
7 Posts |
Posted - 2011-06-30 : 14:50:23
|
| first, thanks everyone for all the advice and help so far. I really appreciate it. As a SysAdmin, I'm used to being able to do things with system-level objects and not worry too much about possible ramifications, provided you are careful. apparently, those thoughts do not mesh well with SQL server administration. :-}Also, I checked the restored database, adn sure enough, there was a table called _SMDBA_.Process Mana. I conferred with the previous application admin, and he informed me that it was essentially a product of a bygone age where some PTB decreed that there shall be different tables for change management and process management, and that we are well pst that point now, so removing that table and related view was perfectly fine, and would not adversely affect the application. So, once I deleted the table and the view, those 3 foreign key entries went with them. I am currently running the upgrade again, to see if it will complete this time.Thanks again.DrakeFrost |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-30 : 15:17:30
|
quote:
Originally posted by DrakeFrost
As a SysAdmin, I'm used to being able to do things with system-level objects and not worry too much about possible ramifications, provided you are careful. apparently, those thoughts do not mesh well with SQL server administration. :-}
Would you delete files from the system32 directory on a windows server? Would you edit registry entries?That's the windows admin equivalent of directly editing the SQL system tables--Gail ShawSQL Server MVP |
|
|
|
DrakeFrost
Starting Member
7 Posts |
Posted - 2011-06-30 : 15:30:34
|
| point taken, GM. :-} Also, I meant to clarify... I'm a UNIX sysadmin... essentially being forced to admin windows servers as well... So, on to the next chapter in this saga... After dropping the table & view, the upgrade complained that these were in fact needed, so I have restored the db bakc to its original state, and am now trying to run:ALTER TABLE [_SMDBA_.Process Mana] DROP CONSTRAINT [FKProcess ManaSECONDARY_USER]And I get:Msg 4902 - Cannot Find the object "_SMDBA_.Process Mana" because it does not exist, or you do not have permissions.but I see it clearly in the table list. I am the sa user, so I should have full access to the entire database structure, correct? Or is this something I need to enable, then run the command?DrakeFrost |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-30 : 15:38:00
|
| Are you in the right database (your query window)?Have you spoken with the vendor who sold you this app? That would have been my first step hours ago, before hacking at the DB design.--Gail ShawSQL Server MVP |
|
|
|
DrakeFrost
Starting Member
7 Posts |
Posted - 2011-06-30 : 15:48:32
|
| Yes, I have the SDE database highlighted in the Object Explorer.Also, yes, I have a ticket opened with vendor support. It sounds like this will need to be addressed by them, because it is way beyond my simple capacity to fix. Thanks again, GM and the rest, for all the help.DrakeFrost |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-30 : 15:56:15
|
quote:
Originally posted by DrakeFrost
Yes, I have the SDE database highlighted in the Object Explorer.
The database context shown in object explorer and the database context of the query window are completely unrelated.--Gail ShawSQL Server MVP |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-01 : 06:27:40
|
| >> As a SysAdmin, I'm used to being able to do things with system-level objects and not worry too much about possible ramificationsWould you be happy patching the bios or the disk headers even if you were careful - without knowing how they were used?That's the sort of level you are dealing with here.These are not structures that users are given information about and you cannot know how they are used, only guess. There is information about the internal structures which you can use but it is often obtained by inspection and may not be correct and could change at any time. The system views are for displaying information only.Bit concerned that you are trying to guess about how to do things without any attempt to check - try reading through bol and also reading about basic dbms concepts.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
removing rows from [sys].[foreign_keys]?