Problems reading all records of a table via ODBC LINKED SERVER SQL 2008 R2

[MMARCHI]

Hi to all members of this useful Forum.

I am new of PROGRESSTALK.COM and i work in an italian company that uses PROGRESS Software from 2005.
This company has a PROGRESS DB stored in a UNIX SERVER with AIX operating system.
Version DB PROGRESS OpenEdge Release 10.2B05
This company has also a Window Server with SSAS 2008 R2 , to work with DataWare House.

i DECIDED to reading tables of the PROGRESS DB using a LINKED SERVER of SQL SERVER 2008 R2.
I need to do this , to fill the tables of SQL SERVER DB for ANALISYS Services.
I use Microsoft SSIS 2008 R2 to import data from my ERP to SQL SERVER DB.

I installed :

ODBC MERANT Progress on the SQL SERVER
I created successfully also my DSN and LINKED SERVER on the same server SQL SERVER.
Connection test and tests reading were okay.


PROBLEM:

All it is fine , if I read records from little tables o medium large tables (From 50 to maximum 500.000 records).


BUT WHEN I TRY TO READ A LARGE SIZE TABLE (in my real situation a table of 1.300.000 records) , VISUAL STUDIO 2008 SSIS but also DATABASE ENGINE 2008 ,
read only 556.000 about records !!!!!!!!!!!

It means NO ALL RECORDS but only a part of that table...
why ????????????????

I checked also :

Log file of PROGRESS DB , no warning no errors...
SQL SERVER and VISUAL STUDIO 2008 log errors, but they tell me "QUERY SUCCESSFULLY" ...no warning no errors, neither query timeout...
I checked my DSN to connect DB PROGRESS, i put READ UNCOMMITTED and i set
query timeout = 0....is it all correct ??????


Now i am a bit confused and i am not able to solve my problem.

Regards to everyone, and forst of all HAPPY CHRISTMAS to You and your families.

I hope in a response, to understand and to learn every day, new things..

Mauro Marchi.

 

[TomBascom]

1) Why are you using the Merant drivers rather than the Data Direct drivers?

2) You say that the query completes without warnings or errors. So why do you think that it is not reading all of the records that it should? What evidence do you have that there is more data to be read?

3) What happens if you take SQL Server out of the equation and just use sqlexp (or your favorite SQL tool...) to directly query the Progress database?

 

[MMARCHI]

Good evening Mr. Tom Bascom.
I am very happy you read my first message in this FORUM adn i thank you very much.

I try to answer your kindly questions :

1) Like PROGRESS ODBC driver i installed what PROGRESS ITALY offers , that is , inside OPENEDGE there is a version of ODBC (I think it is ClientAccess_10.2B )
If it is important to install another driver ODBC please tell me where can i find it... (I have no experience about ODBC...sorry)
2) I know there are over 1,000,000 million of rows in my table, because i did a query directly with 4GL opening PROCEDURE EDITOR connected on company Db.
3) I did query also with OPENEDGE using SQL editor...no problem... i can try another kind of ODBC view such as slk odbc view ???

Thanks for your possibile next answer...

Have a good day. Thanks again.

 

[TomBascom]

I'm not in a position to mess around with 10.2B on Windows at the moment but, as I recall, the supplied drivers are from Data Direct. Not Merant. (Data Direct is a subsidiary of Progress Software -- one would hope that their drivers work best...) So I would look a bit more carefully at the drivers and try to find and use the DD drivers.

If both the 4gl and sqlexp return all of the expected rows then it would seem that the issue must be somewhere in the SQL Server config. I know next to nothing about SQL Server so I'm not going to be much help with that. I suggest searching SQL Server forums for any row limit settings and such.

On the other hand... what are you planning to do with 1,000,000 rows? Perhaps the better solution is to refine your query.

 

[MMARCHI]

I'm not in a position to mess around with 10.2B on Windows at the moment but, as I recall, the supplied drivers are from Data Direct. Not Merant. (Data Direct is a subsidiary of Progress Software -- one would hope that their drivers work best...) So I would look a bit more carefully at the drivers and try to find and use the DD drivers.

If both the 4gl and sqlexp return all of the expected rows then it would seem that the issue must be somewhere in the SQL Server config. I know next to nothing about SQL Server so I'm not going to be much help with that. I suggest searching SQL Server forums for any row limit settings and such.

On the other hand... what are you planning to do with 1,000,000 rows? Perhaps the better solution is to refine your query.

Thanks very much for your advices Tom.

First of all , i will inform better , which is the best driver ODBC that can i use...and i will try with another one.
It really seems a problem of 'NUMBER OF ROWS' , i just searched a lot about in SQL SERVER Forum...but none could help me.

Me too i do not think it is any parameter in PROGRESS DB Configuration...
I need to read over 1 million of rows because Table of Customers in my company are this ...

I could use any LOOP in SQL to divide 1,000,000 million of rows in any equal part...

Thank you vry much the same, i will inform , if i will discover any good news.

Ciao Tom thanks a lot again.