0

I have a database on SQL Azure which has an identity primary. After using SQL Server Import and Export Wizard, I transferred the data to my SQL Server 2008 R2 database.

My ASP.NET Application runs fine and reads the data. But When I try to insert a value in a table 'User', it gives me an error: Cannot insert null in column 'UserId'.

The reason being that it is not able to generate the identity value.

How can I overcome this issue?

PS: I tried Generating the scripts from SQL Azure, but the SQL file is 500MB in size and my host does not allow that big a script to run.

Edit: using Entity Framework for data access. The UserId field has an IDENTITY property (1,1).

Edit Tried to create the schema from SQLAzure Migration tool and then used the import/export data to copy the data. But the wizard does not maintain the relations amongst the rows.

manishKungwani
  • 925
  • 1
  • 12
  • 44

2 Answers2

0

The data import/export wizard doesn't preserve the whole structure of your database objects.

i.e. it will only copy the data, not the whole structure of the table that the data fits into - including identity and key definitions.

You could import the data, and then manually set all the primary keys and default fields to match your desired database definition, or you could connect to your Azure instance and use the generate script option to generate your schema in the 2008 database prior to copying.

But the real answer is that you should be using the Copy Database Wizard to accomplish this, which works fine with Azure. It was designed for this scenario.

Community
  • 1
  • 1
Ryan Weir
  • 6,377
  • 5
  • 40
  • 60
  • As per the reply, the copy database is the "Import/Export Wizard". I also tried to first create the schema and then import the data, but the relations in the copied data were not maintained. Thus a downvote. – manishKungwani Mar 21 '13 at 20:42
0

The issue was the wizard was trying to insert primary key values, which is disabled by default. And without inserting the primary keys, the relationships can't be maintained, thus the whole issue.

To resolve this issue and do a foolproof migration, ensure that the new schema maintains all the identity columns.

When selecting the source and destination tables, for the specific tables, click on "Edit Mappings" and Check the "Enable identity insert" check box to enable insertion of primary key values, which keep the structure and relations intact.

manishKungwani
  • 925
  • 1
  • 12
  • 44