SQL error when migrating SQL DB to Azure SQL

Oct 22, 2012 at 9:21 AM

Hi all,

using the SQL Azure Migration Wizard tool, I get the following error in around 10 tables, when trying to connect to the SQL Azure instance. The first step for connect to the Umbraco DB is supposed that generate the script without problems.

Starting copy...SQLState = 23000, NativeError = 2627Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Violation of PRIMARY KEY constraint 'PK_cmsContent'. Cannot insert duplicate key in object 'dbo.cmsContent'. The duplicate key value is (1).SQLState = 01000, NativeError = 3621Warning = [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been terminated.BCP copy in failed

Someone could help me in this?? Thanks in advance!

Coordinator
Oct 22, 2012 at 3:24 PM

Hi,

 

It looks like you have duplicate data and thus are failing a primary key constraint.  You need to identify which records are duplicated and remove them.  Note that during the export process, I generate the TSQL script with the BCP commands.  At the end of the phase one, you will see a Results Summary dialog with two tabs.  If you click on “SQL Script” tab, you will see the generated script.  You can save the script off (or do a Ctrl-A) and edit the script in SQL Server Management Studio (or favorite editor of choice).  Search for 'PK_cmsContent' and you should be able to see what columns are required to be unique.  You can remove the PK to just get the data loaded, just be sure that you still have a clustered index.

 

Now, what you really need to do is find out which record SQL is getting upset about.  I will be happy to take a look at the data if it is not top secret.  If you can isolate a table that is causing you an issue and send me the TSQL to create the table and the BCP file so that I can upload the data I will try to replicate the issue and resolve.  If you want, contact me offline through codeplex and I will be happy to work with you.

 

Regards,

George

 

 


Oct 22, 2012 at 5:18 PM

Hi George,

thanks for your answer!

but right now I have resolved my issue, In first time I drooped the tables in SQL Azure, after generate the script just for the structure and after for the data, and surprisingly, don't ask me why, the Db has been created correctly.

All what I have done has been through this tool, and after that I have created a new DB with the same data for avoid future problems.

Thank you for your offering.

Jorge.

Coordinator
Oct 22, 2012 at 5:43 PM

Hi Jorge,

I am glad you are up and running.  If you run into any issues, please let me know.

Thanks,
George

Oct 23, 2012 at 12:49 PM

Thanks for that!!

May 22, 2013 at 1:22 PM
Same for me. I do the job in 2 phases. 1st phase is for the data structure, 2nd phase for the table data.
Jan 27 at 4:29 PM
Hello everybody,
I’m encountering the same error. I did some test as follow:
  1. Schema and data together.
  2. Before only the schema and then only the data.
  3. Set false Check Constraints and Unique Key.
The error is still there. I checked my table and I don’t found problem. After all, if primary key are active and checked by SQL Server when I insert or update the data, how is possible to have duplicate key?
I don’t use SQL Migration Wizard for 3 years. It’s ever a really god tools. I hope to be able to use in my architecture.

Regards
Davide