| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2003-12-04 : 13:41:42
|
| Hi SQL Server experts,(I don't like to cross-post, but didn't get any replies to my post in SQL Server Setup and Administration)I used the Copy Database Wizard to copy my SQL Server 7 dbto another server which is SQL 2000.I thought everything went well until I attempted to change a row in the destination (2000) tables in Enterprise manager.I get 1 of these errors every time:"Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database. Database error: INITCOLVS: The parameter 'nickname' is invalid. The statement has been terminated" (There are no other users accessing this db)The other error is similar but says "Violation of PRIMARY KEY constraint 'aaaaaNoSort_File_PK' Cannot insert duplicate key in NoSort_File"This is the layout of the table:Key Serial_Number varchar 16 NoSort_Count smallint 2 rowguid uniqueidentifier 16 (newid())Only 1 primary key and no foreigns.Is there something I missed when I used the Copy DB Wizard ?(Note: This db uses Merge Replication)Thanks for any ideas. John |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-04 : 13:44:37
|
| It is recommended to use detach/attach or backup/restore methods for migrating databases. I doubt anything went wrong with the copy database wizard. What are you modifying? Show us the data. From the second error, you are trying to insert a row that already has the same primary key. How do you know that no other users are on the system? Did you check sp_who? Do your data modifications in Query Analyzer instead of EM.Tara |
 |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2003-12-04 : 14:18:32
|
| Thanks for the reply Tara,What am I modifying? well, data. I'm simply trying to change a value in 1 of the columns... for example change Order_Number 1 to 2. Any col I try to change gives this error: "Database error: ODBC SQL Server Driver INITCOLVS: The parameter 'nickname' is invalid." I used Query Analyzer and get the same error.This is a new server no one else even knows it exists - no one else i s using this db.Unfortunately, Detach/Attach is not an option. I can't detach it from my production server. It will be awhile before I get this new server configured and ready to replace the old server running SQL 7.I tried Restoring from my backup, but even though I specified my new server as the Destination... it restored over the source database.I used Enterprise Manager to perform the restore db.Do you know how I can restore to another server than the source server? I think the system tables may be messed up, causing the error I described.What do you think? Thanks, John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-04 : 14:21:12
|
| Does Order_Number 2 already exist in that table? Could you post the command that you used in Query Analyzer? What command did you use for the RESTORE? It works fine with different servers and different database names.Tara |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2003-12-04 : 14:55:39
|
| Tara - Didnt use a command. Just right-clicked on the table name--> Open to see the rows. Then changed a col value. And to do the Restre, in Ent Mgr, right-clicked the db name--> All Tasks--> Restore Then chose which Backup Set, which Destination server, db etc. Is there a better way to Restore than using Ent Mgr?John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-04 : 14:59:36
|
| You said that you get the same error in Query Analyzer, but you didn't use a command. You have to use a command in Query Analyzer. EM just only be used to quickly view things. EM should not be used for making modifications. For the restore, you should use Query Analyzer as well. But in EM, you need to connect to the correct server. You don't ever specify a destination server in the screens. You connect to the correct server and do the restore there.Tara |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2003-12-04 : 15:26:13
|
| Tara - I've used EM for years, to make quick-and-dirty fixes to data in user tables. Never had a problem.I've discovered that the term 'nickname' relates to tables that are replicated - in my case the source db used merge replication. I think when replication is being used, it's probably best to just recreate the database from scratch on the new server. Then configure replication. It will be alot of work - but no more work than dealing with these weird errors. Probably will end up with a cleaner db as well.Thanks for replying. I posted to 3 forums - you are the only one that tried to help :) John |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-12-04 : 15:50:54
|
| John, you are getting that error, because of a trigger on that table. This trigger gets created when you merge publish a table. You could get a list of triggers on this table, by running sp_helptrigger or by simply right clicking on the table in EM and going to All Tasks -> Manage Triggers. Just delete these triggers in EM or use DROP TRIGGER command. Once you do that you should be able to update your tables. You will probably have the same problem with other tables.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2003-12-08 : 22:33:31
|
Thanks VyasKN !I used DROP TRIGGER on a few tables. Now I can change/delete/insert rows.That worked well.I have to first use the sp_helptriggers on each table to find the trigger names. Then drop them 1-by-1. Microsoft should allow you to drop ALL triggers for a given table - without having to specify the trigger names.Thanks a lot for your help.  John Nashville TN USA |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-12-09 : 00:47:26
|
quote:
should allow you to drop ALL triggers for a given table - without having to specify the trigger names
YEAH! That would exponentially increase the number of questions we get that say :"I deleted all my triggers in EM and I have no source control, how do I get them back"Can't wait  Damian |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-12-09 : 04:05:01
|
| John, have you looked at sp_removedbreplication? See BOL for more info.You could also write a little script that loops through all tables, and drops triggers.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
|
Copied ver 7 DB -->2000 DB -Now Cannot Modify Rows