Prev: [sys.dm_db_index_usage_stats].[user_lookups]
Next: SQL Server 2005 on CM 07 server gives error 2401
From: Hulicat on 11 Sep 2009 23:10
I am importing data into a table and I have worked through all of the
conversions and "no nulls" etc.

but now I am getting the following error; I am populating that field
so I am not sure why the error is generating the MSDN site error
reference wasnt too helpful.

Any insite would be great.

Copying to [dbo].[Client] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native
Client 10.0" Hresult: 0x80004005 Description: "The statement has
been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native
Client 10.0" Hresult: 0x80004005 Description: "The INSERT statement
conflicted with the FOREIGN KEY constraint
"FK_Client_ClientAccountType". The conflict occurred in database
"new", table "dbo.ClientAccountType", column 'ClientAccountTypeID'.".
(SQL Server Import and Export Wizard)
From: Sylvain Lafontaine on 11 Sep 2009 23:36
The message � Description: "The INSERT statement conflicted with the FOREIGN
KEY constraint
"FK_Client_ClientAccountType". The conflict occurred in database "new",
table "dbo.ClientAccountType", column 'ClientAccountTypeID'." � says it all;
it's hard to be more explicit than that.

The Import and Export Wizard has attempted to insert a new row in the table
new.dbo.ClientAccountType but the specified value for ClientAccountTypeID is
invalid: this column is a foreign key to the primary key of another table
but there was not corresponding row in this other table with the same value
for the primary key. If you remove this foreign key constraint, the
importation should be OK (or at least, will be OK until the next error).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"Hulicat" <dennis_A_white(a)yahoo.com> wrote in message
news:f7ee20d0-435e-4a57-ac95-2e75eeaf89eb(a)m3g2000pri.googlegroups.com...
>I am importing data into a table and I have worked through all of the
> conversions and "no nulls" etc.
>
> but now I am getting the following error; I am populating that field
> so I am not sure why the error is generating the MSDN site error
> reference wasnt too helpful.
>
> Any insite would be great.
>
> Copying to [dbo].[Client] (Error)
> Messages
> Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.
> An OLE DB error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Server Native
> Client 10.0" Hresult: 0x80004005 Description: "The statement has
> been terminated.".
> An OLE DB record is available. Source: "Microsoft SQL Server Native
> Client 10.0" Hresult: 0x80004005 Description: "The INSERT statement
> conflicted with the FOREIGN KEY constraint
> "FK_Client_ClientAccountType". The conflict occurred in database
> "new", table "dbo.ClientAccountType", column 'ClientAccountTypeID'.".
> (SQL Server Import and Export Wizard)


From: Erland Sommarskog on 12 Sep 2009 05:36
Sylvain Lafontaine (sylvainlafontaine2009(a)yahoo.ca) writes:
> The Import and Export Wizard has attempted to insert a new row in the
> table new.dbo.ClientAccountType but the specified value for
> ClientAccountTypeID is invalid: this column is a foreign key to the
> primary key of another table but there was not corresponding row in this
> other table with the same value for the primary key. If you remove this
> foreign key constraint, the importation should be OK (or at least, will
> be OK until the next error).

Or Hulicat should import his tables in order or examine the source data.
We cannot really guess the business rules for his process. Just removing
the foreign key may be to sweep something under the carpet.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: John Bell on 12 Sep 2009 05:36

"Hulicat" <dennis_A_white(a)yahoo.com> wrote in message
news:f7ee20d0-435e-4a57-ac95-2e75eeaf89eb(a)m3g2000pri.googlegroups.com...
>I am importing data into a table and I have worked through all of the
> conversions and "no nulls" etc.
>
> but now I am getting the following error; I am populating that field
> so I am not sure why the error is generating the MSDN site error
> reference wasnt too helpful.
>
> Any insite would be great.
>
> Copying to [dbo].[Client] (Error)
> Messages
> Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.
> An OLE DB error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Server Native
> Client 10.0" Hresult: 0x80004005 Description: "The statement has
> been terminated.".
> An OLE DB record is available. Source: "Microsoft SQL Server Native
> Client 10.0" Hresult: 0x80004005 Description: "The INSERT statement
> conflicted with the FOREIGN KEY constraint
> "FK_Client_ClientAccountType". The conflict occurred in database
> "new", table "dbo.ClientAccountType", column 'ClientAccountTypeID'.".
> (SQL Server Import and Export Wizard)

Hi

You have loaded data into a table that has a FK constraint on it and this
data is not in the corresponding table that is referenced by the FK. You can
either load the data into the tables in an order where any tables with
referenced columns on FK constraints are loaded first, or you can disable
the constraint and re-enable it after the load. Potentially you could get
invalid entries remaining in the table unless you use the WITH CHECK option.
for example:

CREATE TABLE FK_example_referenced
(id INT NOT NULL IDENTITY CONSTRAINT PK_FK_example_referenced PRIMARY KEY,
name VARCHAR(10) NOT NULL
);


CREATE TABLE FK_example_referencing
(id INT NOT NULL IDENTITY CONSTRAINT PK_FK_example_referencing PRIMARY KEY,
fid int not null CONSTRAINT FK_FK_example_referenced FOREIGN KEY
REFERENCES FK_example_referenced ( id )
);
GO

INSERT INTO FK_example_referencing(fid) VALUES ( 1 );
GO

/*
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_FK_example_referenced". The conflict occurred in database "master",
table "dbo.FK_example_referenced", column 'id'.
The statement has been terminated.
*/

INSERT INTO FK_example_referenced (name) VALUES ( 'One' );
GO

SELECT * FROM FK_example_referenced ;
GO

INSERT INTO FK_example_referencing(fid) VALUES ( 1 );
GO -- Now Works

SELECT * FROM FK_example_referencing ;
GO

/*
id fid
----------- -----------
2 1
3 3

(2 row(s) affected)
*/

-- Disable the constraint.
ALTER TABLE FK_example_referencing NOCHECK CONSTRAINT
FK_FK_example_referenced;
GO

INSERT INTO FK_example_referencing(fid) VALUES ( 3 );
GO

-- Reenable the constraint.
ALTER TABLE FK_example_referencing CHECK CONSTRAINT
FK_FK_example_referenced;
GO

INSERT INTO FK_example_referencing(fid) VALUES ( 3 );
GO -- Fails

/*
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_FK_example_referenced". The conflict occurred in database "master",
table "dbo.FK_example_referenced", column 'id'.
The statement has been terminated.
*/

-- Reenable the constraint WITH CHECK.
ALTER TABLE FK_example_referencing WITH CHECK CHECK CONSTRAINT
FK_FK_example_referenced;
GO

/*
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
"FK_FK_example_referenced". The conflict occurred in database "master",
table "dbo.FK_example_referenced", column 'id'.
*/

INSERT INTO FK_example_referenced (name) VALUES ( 'Two' ), ( 'Three' );
GO

-- Reenable the constraint WITH CHECK.
ALTER TABLE FK_example_referencing WITH CHECK CHECK CONSTRAINT
FK_FK_example_referenced;
GO -- Works

John

 | 
Pages: 1
Prev: [sys.dm_db_index_usage_stats].[user_lookups]
Next: SQL Server 2005 on CM 07 server gives error 2401