Solved

SQL 7.0 DTS Process Not Copying All Records

Posted on 2001-07-21
2
335 Views
Last Modified: 2013-11-30
I have a SQL Table called Consumers that contains 495,037 records.
The record size is about 500 bytes and it has one field that is a varchar (255). I am using DTS to copy this file from SQL (7.0) to Access (97). Last week this file had about 216,000 records and the DTS Process worked okay. Today, it has 495,037 records and the DTS Process appears to lose some records (about 26,000) during the copy. In the DTS Package the SQL query is just a straight SELECT statement with no WHERE condition.

During the DTS Process the SQL Consumers Table is being copied to and Access Table. The error I am getting during the DTS Process is "Error at Destination for Row # 468608".

When I open the Access Table it shows 468,608 as the actual number of rows in the Access Table and shows ConsumerID 675820 (primary key, identity field) for this particular record.

When I link to the SQL Table via Access the Record # 468,608 shows ConsumerID 643852 and the last record shows Record # 495,037 and ConsumerID 675820.

This tells me that some records are not being copied over during the DTS Process. The last record on the SQL Table is on the Access Table. So there are probably 26,429 (495,037 on SQL Table minus 468,608 on Access Table) that are not on the Access Table.

The DTS Select Query does not have a Where Condition. Do you think some records are being lost because of the size of the SQL Table?

There is an Advance Tab on the DTS Properties Page; there are some
options like 'Insert Commit Size', Fetch Buffer Size', 'Use Fast Load', 'Keep NULLS', and 'Check Constraints' that I
need to take a further look at. Have you used any of these options and do you think any particular one may help
my problem?

There is also and 'Exception File Name' option. I will give this a try next week to see if it will write the problem
records to disk.

Thanks for your help, Kevin



0
Comment
Question by:Kevin_G
2 Comments
 
LVL 1

Accepted Solution

by:
rgollakota earned 200 total points
ID: 6362420
Make sure you have the same table definition on SQL server as the MS Access. MS Access has some typical properties like Required, Allow Zero Length.
If the table definitions are exactly same, then look for Allow Zero Length property. You might have a field as

Allow Zero Length - NO

If you try to copy a zero length text field, MS Access would throw an exception.
0
 

Author Comment

by:Kevin_G
ID: 6363347
I solved this earlier in the week, but your response is accurate.  I tried two methods to get the Data into Access using Access verses using SQL (DTS).  I exported the data to a text file using SQL Server's DTS.  Then used the Append Query features in Access to load the text file.  I appended the large table using 2 Append Queries.  This generated the "Microsoft Access can't append all the records in the Append Query ... XXX record(s) due to validation rules violations."  I searched the Knowledge Base for the error message and found Article Q303414 which addresses this problem.  It states to change the AllowZeroLength property to Yes on the text fields.  I also had to change my datetime fields to text fields.  Now I am also able to Import the Data into Access from the text file.  Thanks for you help.  You deserve the points.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Creating and Managing Databases with phpMyAdmin in cPanel.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question