Hi
I am trying to import of data from my Access 2003 tables in to SQL Server
2005, via SQL Server Management Studio (Import and
export wizard). All accesstables have been imported with no errors except for
this last one "Transaktionsrader"
I have tried to run some different queries to find Unmatched data in this
tables foreign key, inside Access, found some (removed it from the table)
then tried the same import of table again, but still keep getting this error.
I receive this error below:
Regards
Mattias
- Copying to [MbaseMuseumServerNetSQL].[dbo].[Transaktionsrader] (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: "The INSERT statement conflicted with the
FOREIGN KEY constraint "ITransaktionsrader11". The conflict occurred in
database "MbaseMuseumServerNetSQL", table "dbo.Transaktionhuvud", column
'TransaktionhuvudTransaktionsnr'.".
(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 - Transaktionsrader" (115) 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)
It appears that there is a Foreign Key issue.
(I'm only guessing about your table and column names since you didn't include any DDL.)
Try a query somewhat like this to find the 'orphan' rows -and then correct them.
SELECT th.TransaktionhuvudTransaktionsnr
FROM Transaktionsrader tr
RIGHT JOIN MbaseMuseumServerNetSQL.dbo.Transaktionhuvud th
ON tr.TransaktionsraderTransaktionsnr = th.TransaktionhuvudTransaktionsnr
WHERE TransaktionsraderTransaktionsnr IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Mattias" <Mattias@.discussions.microsoft.com> wrote in message news:8178B69D-4227-4C49-95A5-D7C67490CD7F@.microsoft.com...
> Hi
> I am trying to import of data from my Access 2003 tables in to SQL Server
> 2005, via SQL Server Management Studio (Import and
> export wizard). All accesstables have been imported with no errors except for
> this last one "Transaktionsrader"
> I have tried to run some different queries to find Unmatched data in this
> tables foreign key, inside Access, found some (removed it from the table)
> then tried the same import of table again, but still keep getting this error.
> I receive this error below:
> Regards
> Mattias
> - Copying to [MbaseMuseumServerNetSQL].[dbo].[Transaktionsrader] (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: "The INSERT statement conflicted with the
> FOREIGN KEY constraint "ITransaktionsrader11". The conflict occurred in
> database "MbaseMuseumServerNetSQL", table "dbo.Transaktionhuvud", column
> 'TransaktionhuvudTransaktionsnr'.".
> (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 - Transaktionsrader" (115) 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)
>
>
>
|||Hi
Thank you for your reply.
I have already transfered the Transaktionshuvud table from Access to SQL
Server.
Transaktionsrader in SQL Server is an empty table right now.
How do I run your query here?
The best would be if I could run it based on the Transaktionshuvud and
Transaktionsrader in the Access db.
Is it possible?
Mattias
"Arnie Rowland" wrote:
[vbcol=seagreen]
> It appears that there is a Foreign Key issue.
> (I'm only guessing about your table and column names since you didn't include any DDL.)
> Try a query somewhat like this to find the 'orphan' rows -and then correct them.
> SELECT th.TransaktionhuvudTransaktionsnr
> FROM Transaktionsrader tr
> RIGHT JOIN MbaseMuseumServerNetSQL.dbo.Transaktionhuvud th
> ON tr.TransaktionsraderTransaktionsnr = th.TransaktionhuvudTransaktionsnr
> WHERE TransaktionsraderTransaktionsnr IS NULL
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
> "Mattias" <Mattias@.discussions.microsoft.com> wrote in message news:8178B69D-4227-4C49-95A5-D7C67490CD7F@.microsoft.com...
|||What I've done in the past is use the access query wizard to create an
'unmatched' query on the child table using the FK>PK in parent table to find
the orphaned records.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Mattias" wrote:
> Hi
> Thank you for your reply.
> I have already transfered the Transaktionshuvud table from Access to SQL
> Server.
> Transaktionsrader in SQL Server is an empty table right now.
> How do I run your query here?
> The best would be if I could run it based on the Transaktionshuvud and
> Transaktionsrader in the Access db.
> Is it possible?
> Mattias
|||Hi
I did excactly what you suggested, but still getting this error when
importing the Transaktionsrader table
Mattias
"Dave Patrick" wrote:
> What I've done in the past is use the access query wizard to create an
> 'unmatched' query on the child table using the FK>PK in parent table to find
> the orphaned records.
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Mattias" wrote:
>
|||Mattias (Mattias@.discussions.microsoft.com) writes:
> I did excactly what you suggested, but still getting this error when
> importing the Transaktionsrader table
Well, that is sort of expected. Dave suggested that you would create a
third table without foreign keys, import into this table to track
down the suspect rows. From this follows that the import into the
original table would still fail as long as you don't repair the data.
But it appears to akward to import to SQL Server to track down problems
in your access data. What does this query return in Access:
SELECT *
FROM Transaktionsrader R
WHERE NOT EXISTS (SELECT *
FROM Transaktionshuvud H
WHERE H.TransaktionhuvudTransaktionsnr =
R.TransaktionsraderTransaktionsnr)
(I'm making a guess on the last column name here.)
If foreign keys are properly implemented in your Access database,
this query should not return any rows. But in such case the import
would not fail.
Once you have tracked down the wacko rows, insert/delete/updated and
re-import.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Sorry, I should have mentioned and as Erland pointed out I meant to use the
result as a guide to what records need repaired or deleted.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Mattias" wrote:
> Hi
> I did excactly what you suggested, but still getting this error when
> importing the Transaktionsrader table
> Mattias
|||Hi
I tried your Select in Access but no rows was to find, unfortunatly.
Do you have any more ideas?
Mattias
"Erland Sommarskog" wrote:
> Mattias (Mattias@.discussions.microsoft.com) writes:
> Well, that is sort of expected. Dave suggested that you would create a
> third table without foreign keys, import into this table to track
> down the suspect rows. From this follows that the import into the
> original table would still fail as long as you don't repair the data.
> But it appears to akward to import to SQL Server to track down problems
> in your access data. What does this query return in Access:
> SELECT *
> FROM Transaktionsrader R
> WHERE NOT EXISTS (SELECT *
> FROM Transaktionshuvud H
> WHERE H.TransaktionhuvudTransaktionsnr =
> R.TransaktionsraderTransaktionsnr)
> (I'm making a guess on the last column name here.)
> If foreign keys are properly implemented in your Access database,
> this query should not return any rows. But in such case the import
> would not fail.
> Once you have tracked down the wacko rows, insert/delete/updated and
> re-import.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Mattias (Mattias@.discussions.microsoft.com) writes:
> I tried your Select in Access but no rows was to find, unfortunatly.
> Do you have any more ideas?
One possibility is that comparison is case-insensitive on the Access
side, but you have a case-sensitive collation on the SQL Server side, and
the usage of case in the data is inconsistent. That presume that the columns
are character data.
Or else something goes wrong during import, for instance the tables being
imported in the wrong order.
I would disable the foreign-key constraint with:
ALTER TABLE Transaktionsrader NOCHECK CONSTRAINT ITransaktionsrader11
and then run the import. Then use the query from my previous post, to see
if there are bad rows.
To enable the constraint again, do:
ALTER TABLE Transaktionsrader WITH CHECK CHECK
CONSTRAINT ITransaktionsrader11
(Yes, it should really be CHECK CHECK.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hi
Thank you for your effort your idea worked fine imported it and could
remove 16 rows
All the best
Mattias
"Erland Sommarskog" wrote:
> Mattias (Mattias@.discussions.microsoft.com) writes:
> One possibility is that comparison is case-insensitive on the Access
> side, but you have a case-sensitive collation on the SQL Server side, and
> the usage of case in the data is inconsistent. That presume that the columns
> are character data.
> Or else something goes wrong during import, for instance the tables being
> imported in the wrong order.
> I would disable the foreign-key constraint with:
> ALTER TABLE Transaktionsrader NOCHECK CONSTRAINT ITransaktionsrader11
> and then run the import. Then use the query from my previous post, to see
> if there are bad rows.
> To enable the constraint again, do:
> ALTER TABLE Transaktionsrader WITH CHECK CHECK
> CONSTRAINT ITransaktionsrader11
> (Yes, it should really be CHECK CHECK.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
