Copied ver 7 DB --> 2000 - Now Cannot Modify Rows

[JohnBates]

Hi SQL Server experts,

I used the Copy Database Wizard to copy my SQL Server 7 db
to another server which is 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"


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

 

[JayKusch]

I too have had quirky issues in the past w/ the same procedure you are trying to accomplish.

Maybe you should create a backup of the DB on the SS7 server and then restore it to the SS2K server. This "may" remedy your problem.

Thanks

J. Kusch

 

[JohnBates]

Thanks for your reply Jay,

I've discovered that the ref to 'nickname is invalid' in the error message relates to merge replication. The source db is using merge replication and it will also be used on the new server.

Others have suggested that I use detach/attach db but I cannot detach from the source server - it is still in production while I'm slowly getting the new (SQL 2000) server ready.

Backup/restore very well might be better - but the destination db will still contain all of the merge-realted sys table (msMerge_tombstone, sysmergearticles, etc) that will still point to the source db. It's messy when replication is involved.

Now I'm considering just recreating the db from scratch on the new server and reconfiguring replication over there. (I have 2 new servers, the second of which will contain the subscriber db) This will be alot of work but I should end up with a cleaner db on the new server.

But I'm still open to an easier way....

John

 

[JohnBates]

For JayKusch and anyone else who may have had this problem....


My solution was to use the sp_helptriggers tablename stored procedure on each user table - this gives a list of the trigger names. Then used DROP TRIGGER triggerName
to drop each trigger 1-by-1.

A Slow way, but it worked. now I can add/chg/del rows without the above error.

Maybe this will help someone else. John