cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Reports 2008 - C-ISAM/ODBC Error 42000 Failed to retrieve data

Former Member
0 Kudos

I'm hoping someone out there has a solution to this problem.

I have a bunch of reports which I've converted from CR8.5 to CR2008 (SP2 + HotFixPack 2.7 Installed)

Our server is currently running SCO UNIX OpenServer 5.0.7 with a C-ISAM Database serving our accounting software.

Some of the more basic reports have converted with no problem (daily sales figures etc...), but some reports such as customised purchase orders, and complex sales histories etc... continue to have this problem.

Note, all my reports functioned perfectly under CR8.5.

This error occurs within seconds of executing the report.

FAILED TO RETRIEVE DATA FROM THE DATABASE

Details: 42000:ERROR Syntax Error - Database Vendor Code: -1

After which, the winsock comms need to be reset.

One example SQL query is as follows (on a report re-designing my purchase order for imported products.

SELECT purchase_order.po_order_no, purchase_order_line.stock_code, purchase_order.po_backorder_flag,
purchase_order_line.po_line_description, stock_master.stk_desc_line_3, stock_master.stk_description,
stock_master.stk_desc_line_2
 FROM   (purchase_order_line purchase_order_line INNER JOIN purchase_order purchase_order ON 
(purchase_order_line.po_order_no=purchase_order.po_order_no) AND (purchase_order_line.po_backorder_flag=purchase_order.po_backorder_flag)) 
   INNER JOIN stock_master stock_master ON purchase_order_line.stock_code=stock_master.stock_code

Does anyone have any idea??? Really driving me insane... Is it the order of my links, type of links, grouping??????

Strangely, some other similarly linked reports work perfectly.

Cheers in advance,

Steve

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Don,

Thanks for your reply.

The accounting software (Pronto Software in Australia) supplier havent updated their ODBC driver (PRODBC.DLL 3.02.02.00 17-JUN-04) since 2004, and I have no idea if it is v2 or v3. The current database is C-ISAM.

They maintain that they have current clients using the old driver with CR2008.

The ODBC driver works fine with MS Excel drawing data from ODBC as a MS Query command.

This error occurs on some new reports i've written fresh in CR2008. Verifying database etc... as per your suggestion has made no difference. And the same SQL used in a new report results in the same error as well.

Is there a more up-to-date Windows Vista/7 ODBC driver you can recommend that will hook into the C-ISAM database?

Cheers

Steve

0 Kudos

Hi Steve,

Download a program called SQLCON32 from our download page and then connect to the DSN. It should tell you what their driver version is.

You may want to ask them if they support Windows 7/Vista. It may be the version of MDAC you have installed. Could be some backward compatibility issue.

If it is ODBC 2 then search notes ( Kbase ) for info on how to set a registry key for CR to support ODBC ANSII 89 standards.

Thanks again

Don

0 Kudos

Hi Steve,

If you open the Report and then click on DataBase, Log on and connect first. Then open your report, then Database, Set Location and once it's done then Verify the Database. Fix any errors or warnings that may pop up.

Lots of changes between 8.5 and 2008. CR detects that the report is the old format and it then goes through an series of internal report upgrade routines to convert all functions to the current format.

Also, all of the database drivers were re-written to take advantage of the updated functionality and Client enhancements. As well as we removed any "custom hacks" that we did back then to fix "issues" int he DB client drivers at the time. All of those "fixes" were removed, to much work to maintain them all, now if there are issues it's usually due to the DB Client not following ANSII 92 Standards.

One issue that does show up from time to time is that the order you inserted the tables can have an affect on how CR generates the SQL, may not be the case for your issue though, just something to watch for.

What happens if you create a new report and use that same SQL, save it, close all, re-open the report and run it. Do you get the same errors?

Also note: you did not say if you also updated the ODBC driver? As of CR 9 we are UNICODE compliant, may be your client needs to be updated also. Also verify it's using ODBC 3 and not ODBC 2 standards.

Thank you

Don

Edited by: Don Williams on May 6, 2010 7:21 AM