Advertisement

07.02.2007 at 05:28PM PDT, ID: 22671915
[x]
Attachment Details

sql server 2005 - Import / Export Wizard

Asked by Fracture in SQL Server 2005, Databases Miscellaneous, MS SQL DTS

Tags: sql, import, server, 2005, export

Hi All,

I'm trying to copy data from my local database to the online server, using SQL Server 2005 Management Studio. Im using the Import/Export Wizard which i have used many times before. Theres quite a bit of data - The Data Source Table contains 52,878 records and the Destination Table contains 51,372 records - but i dont think this makes a difference.

I'm enabling identity insert, and appending rows. When i execute the package, it runs and gets through all tasks except when it gets through a few thousand records it errors giving a primary key violation error -


- Copying to [Holidays_Hotels_Dev].[dbo].[Hot_Item] (Error)
Messages
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
 (SQL Server Import and Export Wizard)
 
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK_Hot_Item'. Cannot insert duplicate key in object 'dbo.Hot_Item'.".
 (SQL Server Import and Export Wizard)
 
Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - Hot_Item" (64) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009.
 (SQL Server Import and Export Wizard)




I know the error is pretty self explanatory, but im not sure why this is happening. I've checked for duplicate rows on my source and destination tables and there are none. Is the identity insert not updating on the matching row and instead trying to add a new row with the same ID? If thats the case why does it get through a few thousand records?

Thanks for any help.

Kind Regards,
Fracture


Start Free Trial
[+][-]07.02.2007 at 07:19PM PDT, ID: 19408294

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.02.2007 at 07:25PM PDT, ID: 19408311

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.02.2007 at 07:30PM PDT, ID: 19408327

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.02.2007 at 07:57PM PDT, ID: 19408427

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.02.2007 at 08:07PM PDT, ID: 19408459

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.03.2007 at 06:16PM PDT, ID: 19416057

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.03.2007 at 06:20PM PDT, ID: 19416064

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: SQL Server 2005, Databases Miscellaneous, MS SQL DTS
Tags: sql, import, server, 2005, export
Sign Up Now!
Solution Provided By: dtodd
Participating Experts: 2
Solution Grade: A
 
 
[+][-]01.22.2008 at 08:33AM PST, ID: 20715725

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32