List:MySQL ODBC« Previous MessageNext Message »
From:Daniel Kasak Date:November 16 2006 9:09pm
Subject:Re: DTS Sometimes gets 0 rows
View as plain text  
Thomas Johnson wrote:

> I have MS DTS package that pulls records from one MySQL server and puts them into an
> identical table on another MySQL server.  The Query is like the following:
> Select * from scheme.table where modify between @a and @b;
> Modify is a TimeStamp field that has default and on Update set to CurrentTimeStamp. 
> We always pull records from the last pull (@a) to the present (@b = Now()) which are set
> at the beginning of the DTS package and stored in a local table on the source DB.  We pull
> 6 tables in this package.  Initially we have done all 6 in parallel but we have rewritten
> them to be sequential.  When we run them together, 90% of the time, one DATA PUMP gets
> zero rows even when there is data.  The Data Pump does not report an error.  When we go
> back into the design and execute the Data Pump manually it works.  Is there a MySQL
> setting we should have set that may be causing this?  We are using ODBC 3.51.12 with MySQL
> 5.0.24.  It seems to fail for the same table consistently and then after few attempts will
> switch and start to fail for a different table, but always work when we execute just that
> ONE Data Pump manually through the designer.

If it *always* works from the DTS designer, but sometimes fails when 
simply executed, then this sounds like a DTS problem to me. Try turning 
on ODBC logging and triggering a failed execution. Then see if you can 
get an ODBC log for a successful execution.

Personally I've found DTS to be pretty flaky - especially when dealing 
with non-Microsoft products. Maybe you'd be better off doing the 
transfer some other way? You could always use mysqldump to dump your 
table from one server and then import it into another server. You could 
do a really simple script that does the mysqldump, then hits a web page 
on the destination server, which triggers the import.

Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped

DTS Sometimes gets 0 rowsThomas Johnson16 Nov
  • Re: DTS Sometimes gets 0 rowsDaniel Kasak16 Nov
    • Re: DTS Sometimes gets 0 rowsMike Harknett20 Nov
  • RE: DTS Sometimes gets 0 rowsAl McNicoll20 Nov