Solved

SQL 7.0 DTS Process Not Copying All Records

Posted on 2001-07-21
2
343 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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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 extract information from SQL Server on Database, Connection and Server properties

792 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