apitech
asked on
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?
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?
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.