SSRS Error converting datatype NVARCHAR to DATETIME / Too many Arguments Specified

• salkobi

  Ten Centuries

  Points: 1090

  More actions

  January 20, 2016 at 7:16 am

  #399592

  Hey all,

  I'm trying to modify this existing SSRS report that our users run on a need-basis ( Link is posted on our SharePoint, they run it whenever they need to pull some order data).

  The report is built using a couple SPs and an Agent job. The first SP, let's call it Order_SP, builds the table for the report. It pulls data using basic select statements and 1 #temp table and inserts it into the table it creates called Order_Report_Table ( Like your typical SP that creates a table, it checks if it exists first and drops the table before creating it.)

  Next, we have a nightly agent job that executes the above SP with Start and End date parameters and inserts the records into ORder_Report_Table. I have to run this Job anytime i make changes to the first SP. IT inserts about ~580k records.

  Next, the second SP, let's call it Order_Report_SP, declares all the report parameters and runs the select statement from Order_report_Table.

  The SSRS report simply runs the above SP, Order_Report_SP, with all the parameters listed.

  The changes i'm doing is simply adding 4 new columns to the report table, Origin & Destination Zip code ( VARCHAR(10) ) and Origin & Destination Country ( CHAR(4), ex: CAN, USA, MEX...). I'm using existing joins in the report so i'm not changing anything there either.

  I made a copy of the original report then made the changes in there. If i run the report in the copy version ( Added the 4 columns and configured 2 Origin/Dest country parameters) i get the error "Procedure or Function has too many arguments specified". If i run the original report without the added columns or parameters, i get the error "Unable to convert datatype NVARCHAR to DATETIME"...

  I tried splitting the problem into 2 parts: undid all my changes in the SPs and re-tested original report to make sure it was working again. So then i tried only adding the Origin/Dest zip codes and would get the same errors. I comment the changes out, switch them with the country codes ( Just the columns, leaving the parameters out for now) and would still get the same error. I even tried adding 1 at a time and still got the same error.

  It's not making much sense to me since it's giving 2 different errors... If i run a select * from the report's source table, the new columns are there and it looks fine in SSMS...