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

[#1]

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]

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