CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Geo
Geo

Reputation: 3200

SQL Server 2008 R2 Data Export Problems

I am trying to export data from my production DB to my development DB but I am getting this error:

Messages Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "id". (SQL Server Import and Export Wizard)

Is there a way to check which column is that because I have 20 tables that all of them use the column name id or at least get a better error report?

Upvotes: 11

Views: 13588

Answers (5)

Pavel Nazarov
Pavel Nazarov

Reputation: 733

Just click on Edit mappings when you choose tables to import from and to. and check on the Enable identity insert checkbox. And you should do that for each choosen table. Then you can complete your import. It was helped me in the same situation.

Upvotes: 43

Baz Guvenkaya
Baz Guvenkaya

Reputation: 1562

Well, the answer is a bit late but I'm posting it anyway for the sake of providing to community.

I assume that you are trying to copy tables (not partial data). My answer is based on this assumption.

  1. Connect to the source db via Object Explorer in SSMS.
  2. Right click the db that you want its tables to be copied.
  3. Go to 'Tasks' --> 'Export'.
  4. Choose your source and destination server and if you don't want additional script to be run before hands just press next ('Copy data from one or more tables or views'). Now you may select tables you want to be copied to the destination.

Select tables that you want to be completely copied to the destination. And click on 'edit mappings'. Here you may 'enable identity insert'. You will also have other options so check the screen before pressing OK.

If you do not use an express edition of SSMS here you may create an SSIS package to create jobs and to automatize this process in the future. You may check 'run immediately' and follow the screen about how it goes with all the warnings and errors. Hopefully you will get none. Good luck!

Tested with:
(
    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) 
    Jun 17 2011 00:57:23 
    Copyright (c) Microsoft Corporation
    Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
)

Upvotes: 0

Geo
Geo

Reputation: 3200

All I had to do was:

  • Right-click on the column (in this case 'ID')
  • Select Modify
  • Inside the Column Properties scroll down until you see Identity Specification
  • Expand the view and select NO from the drop down menu.

If anyone knows a different (faster) way of doing this please share

Upvotes: 3

David W
David W

Reputation: 10184

Sounds to me as though you might be trying to insert values into a column that has been declared as an IDENTITY field.

Upvotes: 0

CJR
CJR

Reputation: 200

If the column ID is an identity column, it's likely set up to generate automatically when a row is inserted, and is not able to be directly written to.

To preserve linking from the production environment to the test, use:

SET Identity_Insert <TableName> ON

To allow you to write identity values.

Upvotes: 5

Related Questions