Failed to retrieve data from the database error

[Former Member]

Details: ADO Error Code: 0x80004005

Source: Microsoft OLE DB Provider for Visual FoxPro

Description: SQL: Column 'DATETIME' is not found.

Native Error: 806 [Database Vendor Code: 806]

I get this error when trying to refresh a report. I cannot do any datetime record selection on this report or i get this error, i do not get the error if I don't use record selection for a datetime field. Please help. We are running Crystal XI and 2008 on different client computers with a Visual Foxpro backend through an OLE DB connection.

Edited by: Nick Cirksena on Jul 31, 2008 4:15 PM

Edited by: Nick Cirksena on Jul 31, 2008 4:16 PM

[Former Member]

Simple fix (much more simple than I suggested).

Look at post

So, for me the filter is modified to be:

Date({cust.entrydate}) >= Date (2010, 01, 01)

[Former Member]

Was there ever a solution to this problem?

To recap, it seems that Visual FoxPro v.9 database files can only be accessed via OLE DB (ADO). Microsoft seems to have last supported this in 2008 with the OLE DB download cited as a replacement for the ODBC VFP driver.

So, yes, you can easily see VFP9 tables once you have configured OLE DB.

However, there continues to be a problem with OLE DB trying to filter VFP9 records in a datetime formatted field. I am curious if this is an OLE DB problem and has nothing to do with Crystal. Regardless, not being able to filter records based on date is quite limiting.

I think I saw another post where one defined the field type as DATE (only) and was able to get around this. Is this a possible fix? Could another fix be (I'm terrible at Crystal formulas): define a report variable called "new_cust.entrydate" and populate that with the extracted date-only valaue from cust.entrydate. Then have the filter formula filter on that.

(So {cust.entrydate} >= DateTime (2010, 01, 01, 00, 00, 00) would instead be written:

define a new report field

{new_cust.entrydate} = Date(cust.entrydate)

then filter by

new_cust.entrydate >= Date (2010,01,01)

My only current work-around has been to pull the report with all data (no filtering), then apply a filter and chose to NOT refresh the data.

Can anyone comment?

Thank you

John Howard

CI3 Partners

Dallas TX USA

[former_member11696]

Please re-post if this is still an issue to the Data Connectivity - Crystal Reports Forum or purchase a case and have a dedicated support engineer work with you directly

[Former Member]

I am still facing the same issue while filtering over a set of available dates, the datatype in database is datetime. and the genetated crystal report query is:

{goodsrvc.dtcreated} = DateTime (2009, 09, 25, 12, 00, 05)

The datetime entry in the database is '9/25/2009 12:00:05 PM'

Error:

Failed to retrieve data from the database.

Details: ADO Error Code: 0x

Source: Microsoft OLE DB Provider for Visual FoxPro

Description: SQL: Column 'DATETIME' is not found.

Native Error: [Database Vendor Code: 806]

I am succesfully able to filter over the other datatypes, but fails for datetime.

Kindly leave your suggestions on the issue.

[Former Member]

I am still facing the same issue while filtering over a set of available dates, the datatype in database is datetime. and the genetated crystal report query is:

{goodsrvc.dtcreated} = DateTime (2009, 09, 25, 12, 00, 05)

The datetime entry in the database is '9/25/2009 12:00:05 PM'

Error:

Failed to retrieve data from the database.

Details: ADO Error Code: 0x

Source: Microsoft OLE DB Provider for Visual FoxPro

Description: SQL: Column 'DATETIME' is not found.

Native Error: [Database Vendor Code: 806]

I am succesfully able to filter over the other datatypes, but fails for datetime.

Kindly leave your suggestions on the issue.

[former_member11696]

Please re-post if this is still an issue to the Data Connectivity - Crystal Reports Forum or purchase a case and have a dedicated support engineer work with you directly

[Former Member]

I have this same issue in the same environment (although I am using Crystal XI) and I have not been able to resolve it. I get this error when trying to refresh the report. I cannot do any date record selections on any report.

Details: ADO Error Code: 0x80004005

Source: Microsoft OLE DB Provider for Visual FoxPro

Description: SQL: Column 'DATETIME' is not found.

Native Error: 806 Database Vendor Code: 806

I am a new user - and not a developer. I need a simple, straightforward solution pls.

Thank you.

[Former Member]

Hi,

Second Alternative, you can choose different driver for DNS such as ODBC or Native Driver.

Good Luck.

-titanium0203

[Former Member]

I had the exact same problem as described, same environment. I was able to work around the error. I had 2 parameter fields in the report set to field type Date. Even though the database really has a field type of date, changing it to Date Time resolved the issue. The data in the database has no time associated with it, but it still accepted it.

Were you able to resolve this and if so, what did you do?

[Former Member]

Hello Nick,

Can you please try to filter using the following in record selection and check still you are getting said error.

year({Orders.Order Date}) =2003 and month({Orders.Order Date}) = 12 and day({Orders.Order Date}) = 02

Thanks,

Sastry

[Former Member]

When I enter that formula I get a boolean, that is not what I want.

[Former Member]

I forgot to mention one thing, this only started happening after I changed from a ODBC connection to an OLE DB....

[Former Member]

Hi Nick!!

I would suggest you to contact your DBA .

The error message ADO Error Code: 0x80004005 is returned when there is not enough spool space for your database.

Please get in touch with your DBA and let us also know the steps if it works.

Hope it helps!!

Regards

Sourashree Ghosh

[Former Member]

I am using a FOXPRO database, My DBA had no idea what spool space was.

[Former Member]

Hi Nick

You are using Visual Foxpro, and you have mentioned that you are getting this particular error since you have switched to OLE DB from ODBC.

Try creating new reports using the same OLE DB connection and see if it works.

If the new reports work with the same OLE DB connection, then please check if there have been any changes made to the database.

Hope it helps!!

Sourashree

[Former Member]

It has the same error with new reports as well. I am still stuck here.

[Former Member]

Hi Nick,

Can you try this small step mentioned below?

Please write a SQL query and run it against your database which retrives the date time filed from your database. once that is done, copy the query and 'Add command' in crystal reports and paste that query. If even this does not work, then please please check the version of the OLE DB driver being used and upgrade it to the latest one.

Also, you have mentioned that the report worked fine with ODBC connection. Please check the date time field in that report and copy the "Show SQL Query" from there and paste it in the OLEDB Connection "Add command".

This should fix the issue.

Please let us know if this helps!

Regards,

Abhishek.

[Former Member]

Also, you have mentioned that the report worked fine with ODBC connection. Please check the date time field in that report and copy the "Show SQL Query" from there and paste it in the OLEDB Connection "Add command".

This didn't work. Also, this is a Foxpro Database. How do I write a SQL query for VFP?

[Former Member]

I cannot run any reports from the Crystal Server that are built using ODBC, does anybody know where I can get one on one support with a submitted ticket or the like?

[Former Member]

Nobody knows where to get support??

[Former Member]

Hi Nick,

Have a look to this thread:

[;

Hope that helps!!

Regards,

Shweta

[Former Member]

Hi Nick !!

If the issue is happening with all the versions of Crystal Reports and on all the machines, I would suggest you to check the exact data type of the field (that you are using for record selection) in the database first.

If you are using a record selection formula, then please make sure you are using the correct function and the data type matches the database.

Please let me know after you try this.

Regards

Sourashree Ghosh