Re: DTS Sometimes gets 0 rows

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.

cheers

mike

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.
>


-- 
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/[email protected]