Post reply

Am I missing something??

  • October 23, 2007 at 11:55 pm

    #179526

    I seem to be missing something in using DTS for data import. I am using SQL Server 2000 to load an Access 2000 database. When I first ran the import, I copied 47 tables and had 22 table errors. I re-ran it for the 22 errors and this time had 13 tables successful and 9 errors. After the fourth run, all were successful.

    The reason was foreign key errors. The table containing the PK in a relationship had not been loaded at the point the foreign key table was loaded. Consequently there was a foreign key in the foreign key table without a matching primary key in the primary key table.

    Is this normal, or, because I am just learning SQL Server, am I missing something.

  • October 24, 2007 at 12:09 am

    #744780

    Does your access db has been normalised. Does your design of the sql db has been related properly. If your sql db has been normalised with proper relationships then chances for dts import data mechanisms should'nt be failures.

    Better take the script of the Data using database publishing wizard & run through the same with sql query analyzer

  • October 24, 2007 at 1:09 am

    #744788

    I think that the problem is the sequence of importing tables.

    Referenced tables need to be imported before referencing tables can be.

  • October 24, 2007 at 1:14 am

    #744790

    Hi,

    You seem to be able to identify which tables have the foreign keys ... Try to divide the job into several copies: First copy the tables that have NO FOREIGN KEYS because they will probably copy fine. Next, copy the next group of tables that HAVE FOREIGN KEYS of ALREADY COPIED tables.

    This way, as you do the copy, you are assured that with a given number of steps, copying each table once only, the whole operation is a success.

    For example: If I am copying 3 tables: STUDENT, COURSE and STUDENT COURSE (This table contains the foreign keys of STUDENT AND COURSE), I would have 2 steps:

    1. Copy STUDENT and COURSE

    2. Copy STUDENT COURSE

    I hope that helps,

    Osama

  • October 24, 2007 at 1:34 am

    #744795

    Thanks for the feedback. Seems the approach mentioned will solve the problem.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply