Crystal "Failed to retrieve data from the database" error


For a Crystal XI report, I get the error "Failed to retrieve data from the database" only when I scroll to the very last page of the report.  Now, this report is somewhat unique in that I am pulling most of the data from a SQL database but some of the data from an Excel spreadsheet.  Specifically, each column of the spreadssheet represents a month of the year and the parameter used by Crystal is based on month of the year.

And, this error only happens if the prior month (in this case March) is retrieved by the parameter.

After clicking "OK" to the error, I subsequently get the error "Details: 22007 [Microsoft] [SQL Native Client] [SQL Server] Conversion failed when converting datetime from character string [Database Vendor Code: 241]".

So, as a continued means of troubleshooting, I decided to pull the Excel data onto a separate blank report.  Upon trying to do so, I got the following error:  "The Microsoft Jet database engine cannot open the file.......It is already opened exclusively by another user, or you need permission to view its data".

Any ideas?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You almost certainly have an invalid date in the Excel spreadsheet.  Excel does significantly less validation of dates than SQL Server.  SQL Server requires dates be after 1/1/1753.  If you have an Excel date before that, the error will occur.  Check to make sure all of your dates in Excel will convert to real dates in SQL Server.

As for the "already opened" error, you must close the file in Excel before you can open it in the Jet engine.  It may also be open by the previous report, so close that one also.
I have a couple of reports like this at my work right now. It is a battle. I would try to figure out a way to load the excel into the database. Otherwise here is a couple of issues that I have run into.

Excel - The locking error that you are getting may or may not be true. At some point the excel spreadsheet was opened but the file system doesnt recognize that it was closed. If you have the report open you can not be in the excel spreadsheet. Next, if it does get locked only time or the systems guys can unlock it. Its a pain. As kenwagers said, data integrity goes right out the window in excel because each cell can contain a different datatype. You will need to verify that each column is correct.

Now as far as the other error Details: 22007 [Microsoft] [SQL Native Client] [SQL Server] Conversion failed when converting datetime from character string [Database Vendor Code: 241]. This to me says that in the database you have a string (even though it looks like a date) and you are trying to convert it to a date in the report, but the report has run into a row that is not date worthy.
I agree with wykabryan - your best bet might be to try and load the data from Excel using the Import option inside SQL Server.  If there are invalid dates, you'll find them during the import process.  

To do the import, right click on the database name in SQL Server Management Studio, select Tasks, then Import Data.

One way I've found invalid dates in Excel is to sort the worksheet by ascending or descending order, and look for invalid values at the top and bottom of the sort.
apitechAuthor Commented:
I figured this one out.  The solution was to go into the database and remove the spaces between the date and time values of the field from within the view that the report is pulling from.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.