ODBC read failed during LOAD, but succeeds during Debug.

[#1]

Hi,

I have a situation when I debug the application, there is no error. But when I run the application for full load it shows unknown error:

From Debug Message:

----------------------------------

Connecting to GP_PROD_64
Connected
TempCalendar << AUTOGENERATE(1) 10 lines fetched
MasterCalendar << TempCalendar 10 lines fetched
Monthend << MasterCalendar 10 lines fetched
Rollforward_Book << foa_ar_book 10 lines fetched
Rollforward_Book << foa_ar_book 20 lines fetched
Rollforward_Book << foa_ar_book 30 lines fetched
Rollforward_Book << Rollforward_Book.Aug2014 10 lines fetched

--- Script Finished ---

From Log of full run:

-----------------------------

9/19/2014 1:56:30 AM:          19 fields found: CalenderMonthend, locator_code, package_locator_code, agency_cd, currency_code, product_type, business_model, package_ind, event_source_code, book_date, book_gl_date, book_sell_rate, book_coupon_amt, book_loyalty_amt, book_service_fee, book_absorption_amt, book_amount, payment_type, partner_code, 

9/19/2014 1:58:13 AM:         Unknown error

9/19/2014 1:58:14 AM:         Error: ODBC read failed

Please help.

Thanks,

Abhishek

[#2]

Hi,

Could you please check data fromat in database.Even I had faced the same type of error.The mistake was with the database.Some of the date fields had incorrect date values like '00-00-0000'.The first 10 lines would not have any data error so your debbuging works but may be the values of data for the other rows after first 10 lines are incorrect.

We figured out the wrong values of data and ignored such values while loading data by writing appropriate scripts.

[ajaykumar1]

Hi QV_Learner,

AS Jemimah said try to reload the data with only DATE field and check whether it has data like Jemimah mentioned.

Hi Jemimah,

Could you please confirm how we will get the error even if the data is wrong.As per my understanding if the connection are proper we will get the data(even if its wrong in source) without error.

Please guide me its very helpfull to me.

Thanks,

Ajay

[#4]

Hi Ajay,

I had faced the same issue.The first 10000 records were loaded successfully,then I used to get the error "ODBC read failed".Finally after a lot of discussions with the database team we figured out the error. QlikView does not laod wrong values of data for example values like '00-00-0000'.

We figured out the values by checking the values in the database by running the SQL query in the SQL developer.Then we tried to avoid them using appropriate scripts.

[#5]

try OLEDB connection instead of ODBC..

[ajaykumar1]

Thanks Jemimah for your clarifications.Then did you find any solution to avoid this cases in future.

Lets say if the DB team entered some wrong data frequently and so that will impact on our QV as usual.

Thanks,

Ajay

[#7]

Hi Ajay,

We reported this to the DB team and asked them to take care while loading date values.Because such cases should not be handled in QlikView scripts.But as we had to deliver some reports within deadlines we had to edit our scripts.We had created logs for the invalid data and shared with the team.This was done in consultation with the DB team for that particular  time being.

Thanks,

Jemimah

[ajaykumar1]

Once again thanks.Your right.Ofcorse we can't expect what will happen in future

[#9]

Hi Jemimah,

Thank you so much for your response. The only point where I beg to differ with you at the simple Extraction from Database. If we don't involve any transformation in our script and it is a simple select (extract), I don't think that an error would arise for garbage data. As per my understanding, for a simple extract QlikView treats each field of DB as a simple string unless we externally format them in transformation step.

Indeed I checked the dates and other fields they look good. You know, now I am only selecting '1' in my select statement from the DB, it gets executed and then in the end the same error surfaces.

[#10 · Accepted solution]

Hi ,

This issue is resolved now and by itself. I worked with DBAs to understand what went wrong and waited for a day to replicate the similar situation. Eventually, we found the root cause. Since my extract involved Millions of data, and I was running that query at the time which is scheduled for several batch jobs. Hence, the Cursor Area required by my query (for millions of rows with several columns), was not sufficient and the result was rolled back throwing an "unknown error".

I ran the query at the point when DB-load was less and query succeeded.

Thank you.