Solved

SQL 7.0 DTS Process Not Copying All Records

Posted on 2001-07-21
2
309 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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now