List:MySQL ODBC« Previous MessageNext Message »
From:Mike Harknett Date:November 20 2006 6:36pm
Subject:Re: DTS Sometimes gets 0 rows
View as plain text  
Another possibility would be to add both MySQL databases as linked
servers and just use a stored procedure to select from one and insert
into the other using openquery . Then schedule the procedure as a SQL
Server Agent job.

This has worked for me in the past.



Daniel Kasak wrote:
> 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.

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