Crystal "Failed to retrieve data from the database" error

Hello:

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?
LVL 1
apitechAsked:
Who is Participating?
 
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.
0
 
kenwagersCommented:
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.
0
 
wykabryanCommented:
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.
0
 
kenwagersCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.