DTS Sometimes gets 0 rows

------_=_NextPart_001_01C709BC.0F3887A5
Content-Type: text/plain;
charset="windows-1250"
Content-Transfer-Encoding: quoted-printable

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:

=20

Select * from scheme.table where modify between @a and @b;

=20

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 =3D 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.

=20

------------------------------------------------------------ -------------=
-----------------------------------------

THOMAS A. JOHNSON | DIRECTOR OF IT APPLICATIONS | PARCELite SOLUTIONS a =
division of TMSi

------------------------------------------------------------ -------------=
-----------------------------------------

1315 West 22nd Street

Suite 225

Oak Brook, IL 60523

=20

Phone: 630.214-1057 x206

Mobile: 630.441-8300

Web: HYPERLINK "http://www.PARCELite.com"www.PARCELite.com

Email: HYPERLINK "mailto:tjohnson@PARCELite.com"tjohnson@PARCELite.com

=20

The information in this email and in any attachments is confidential and =
may be privileged. If you are not the intended recipient, please destroy =
this message, delete any copies held on your systems and notify the =
sender immediately. You should not retain copy or use this email for any =
purpose, nor disclose all or any part of its content to any other =
person.

=20


--=20
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.430 / Virus Database: 268.14.6/535 - Release Date: =
11/15/2006 3:47 PM
=20

------_=_NextPart_001_01C709BC.0F3887A5--

Re: DTS Sometimes gets 0 rows

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:
>
> =20
>
> Select * from scheme.table where modify between @a and @b;
>
> =20
>
> Modify is a TimeStamp field that has default and on Update set to Curre=
ntTimeStamp. We always pull records from the last pull (@a) to the prese=
nt (@b =3D Now()) which are set at the beginning of the DTS package and s=
tored in a local table on the source DB. We pull 6 tables in this packag=
e. Initially we have done all 6 in parallel but we have rewritten them t=
o be sequential. When we run them together, 90% of the time, one DATA PU=
MP 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 man=
ually 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 swi=
tch and start to fail for a different table, but always work when we exec=
ute just that ONE Data Pump manually through the designer.
> =20

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

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

--=20
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@nusconsulting.com.au
website: http://www.nusconsulting.com.au


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

RE: DTS Sometimes gets 0 rows

I have no experience with DTS, but it sounds like it could be a locking
issue - it might sound simple, but are you locking the tables before trying
to read out all the rows? The lock options you use probably depend on
whether you're using InnoDB or MyISAM too. If you look in the Create Backup
section of the MySQL Adminstrator GUI tool, under the "Advanced Options"
tab, you'll see the recommended options (including their SQL statements) for
consistent backups of the various table types...

Al McNicoll
Integritec Limited



-----Original Message-----
From: Thomas Johnson [mailto:tjohnson@PARCELITE.COM]
Sent: 16 November 2006 20:15
To: myodbc@lists.mysql.com
Cc: Pedram Soheil
Subject: DTS Sometimes gets 0 rows

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.


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

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/myodbc?unsub=gcdmo-myodbc@m.gmane.org