Conversion failed when converting the nvarchar value '%' to data type int?

[Former Member]

I have a crystal report designed in 2008.  This report does not contains any groups or subreports.  I need to Verify Database so I can pick up 2 new parameters in our stored procedure that this RPT pulls data from.  The second I click Verify Database I get the error "Conversion failed when converting the nvarchar value '%' to data type int".  The ONLY spot we are handling a percent symbol is in the formatting options for a couple of fields.  We have looked extensively at the SQL and the data being pulled and nowhere are we using a % symbol.  This has happened to several of our reports and in order to get around it we simply created a new RPT because the formatting was rather simple.  Yes, a new RPT will connect to the stored procedure just fine.

After I click OK on the error I am prompt as usual with filling in our parameters.  After I fill them in I then get another error "Invalid object name 'LBI_Reporting.dbo.sp_PLN_StyleDemandRanking;1'.  I believe this is a result of my initial error but I included it anyway.

The conversion error I mentioned above also happens when I try to set a new data source location so that is not an option either.

Now that I have run into this problem all over again with another RPT that will take an incredibly long time to recreate, and this issue has cost us a lot of time and money, I need a solution.  

[Former Member · Accepted solution]

I think it's becoming more clear to me why my question can't be answered.  My stored procedure is not the issue because as I initially said, the report has been working.  If the stored procedure was the problem, the report never would have worked.  The two additional parameters we added were reviewed extensively and do not affect any data and are not relevant to the problem since I can't even get the parameter window to verify the database before seeing the error.

I was able to fix the problem with the report.  I was able to pull the report into CR2011, with many issues.  I HAD to set a new data source in order for this to work and then verify database, again, we ran into a lot of strange behavior from crystal.  Since our business does not yet operate with a version of crystal later than 2008, I had to again pull the RPT into CR2008 to make sure we could manipulate it if a support issue came up.

When all is said and done I had to report the cause of our issue to management.  Our VP said in order to remove problems you have to remove the source of those problems.  I don't think we will be using crystal reports any longer.

[Former Member]

What is the patch level of CR 2008, connectivity and database driver being used.

A google search on this error indicates that probably some type-casting changes have happened in the stored procedure that are no longer acceptable to the fields present in the existing report.

Do you have a copy of the original stored procedure in order to test the same workflow.Can you test this using different driver and connectivity options like OLEDB, native etc.

-Prathamesh

[Former Member]

Thank you for your response.  I attached the SQL as a text file because it's rather long but isn't too complicated of a procedure.  I have included some more details in my response to Aggarwal Himanshu above. 

I didn't feel like it was the stored procedure but if it is then I will certainly learn something new so your help is greatly appreciated.

To answer your question, I currently am not able to test with a different driver but it will be a possibility this weekend.

EDIT:  I forgot to mention that my current Crystal version is 12.1.0.892

StoredProcedure.txt.zip

[Former Member]

Hi Ryan

This problem occurs due to some invalid type conversion logic written in the stored procedure. If possible could you please attach the script of your store procedure or only the those code snippets where you would have written some string concatenation or type conversion logic.

-- Himanshu

[Former Member]

Thank you for your response.  I attached the SQL as it's a rather long stored procedure but it's not overly complicated. 

This report has been working just fine with the stored procedure, it runs daily and sends out an RPT to several people.  When we open the RPT in crystal and try to Verify Database or set the data source location with the same stored procedure, we get the error.  If the SQL is the problem then why does the report still run and why would we be able to create a new RPT and connect to the stored procedure just fine?

Again our objective is to add two parameters to the current stored procedure (these parameters are included in the SQL I attached and added very little to the overall procedure) and we do not need to modify the format of the RPT, we simply need to be able to successfully verify the database and move on.

If it is our stored procedure this will teach me something so I really appreciate the help.  Thank you.

StoredProcedure.txt.zip