[RESOLVED]SSRS Error converting Datatype NVARCHAR to DATETIME / Too many Arguments Specified

salkobi · January 20, 2016, 2:07pm

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"...

/EDIT: Please view my latest reply below; I startd this problem from scratch and found an underlying issue that i can't seem to resolve.

gbritton · January 20, 2016, 4:21pm

Can you post the signature of the Proc or Function SSRS is complaining about?

salkobi · January 20, 2016, 4:28pm

Hey there,
Sorry, i'm not familiar with what the Signature would be; Could you point out where i can get that?

gbritton · January 20, 2016, 4:29pm

A signature is just the first few lines:

CREATE PROCEDURE ...

AS

or

CREATE FUNCTION ...

AS

salkobi · January 20, 2016, 4:39pm

Here's the Signature from the first SP that creates the report table:

CREATE proc [dbo].[LANE_HISTORY_BY_LEG] 
(
--    @origin_state as varchar(10)
--    , @dest_state as varchar(10)
--    , @origin_city as varchar(100)
--    , @dest_city as varchar(100)
--    , @origin_country as char(4)
--    , @dest_country as char(4)
 @startdate as datetime
    , @enddate as datetime
--    , @loadtype as nvarchar(max)
--    , @trltype as nvarchar(max)
--    , @carrier as varchar(100)
--    , @currency as varchar(10)
)
as
/**
 * NAME:
* dbo.LANE_HISTORY_BY_LEG
* TYPE:
* Stored Procedure
* DESCRIPTION: 
 * SAMPLE CALL:
exec dbo.LANE_HISTORY_BY_LEG '2010-01-01', '2015-03-26'
* PARAMETERS:
* REVISION HISTORY:
*/

Here's the signature from the SP that the SSRS report executes:

CREATE proc [dbo].[LANE_HISTORY_BY_LEG_REPORT] 
(
    -- @origin_country as char(4)
    --, @dest_country as char(4)
     @origin_state as varchar(10)
    , @dest_state as varchar(10)
    , @origin_city as varchar(100)
    , @dest_city as varchar(100)
    , @startdate as datetime
    , @enddate as datetime
    , @revtype1 as nvarchar(max)
    , @trltype1 as nvarchar(max)
    , @carrier as varchar(100)
    , @currency as varchar(10)
)
as
/**
 * NAME:
* dbo.LANE_HISTORY_BY_LEG_REPORT
* TYPE:
* Stored Procedure
* DESCRIPTION:
 * SAMPLE CALL:
exec dbo.LANE_HISTORY_BY_LEG_REPORT '%','%','%','%','2015-01-01', '2015-03-26'
    , 'Truck,LTL','53v,48d','%','US$'
* PARAMETERS:
* REVISION HISTORY:       
*/

gbritton · January 20, 2016, 4:40pm

So, when you get this error message:

What are the parameters passed to the proc?

salkobi · January 20, 2016, 4:48pm

So just to clarify, i only get that error in the copy of the original report, no matter which change i do; Whether i add 1 column at a time or 1 parameter at a time.

When the report is run, the following parameters are set as default:
@Startdate=DATEADD(DD,1,GETDATE())
@EndDate=DateAdd,DD,90,GETDATE())'
@originCity=%
@DestCity=%
@OriginCity=%
@DestCity=%
@Revtype1=% -- This parameters is query defined and set to select multiple values; They're are selected by default in the parameter settings in report builder
@trltype1=% -- Same as above
@Carrier=%
@Currency= US$ OR CAN$ --( User must select 1 of them)
@origincountry='CAN','USA','MEX' --Those 3 values are defined in the Available and Default values in the parameter properties in report builder
@destCountry='CAN'USA','MEX' --same as above

In the original report, which is executing the same SP (LANE_HISTORY_LEG_REPORT) is where i get the "Error converting datatype NVARCHAR to DATETIME"

gbritton · January 20, 2016, 4:53pm

So your proc is getting these parametesr:

But that is really six parameters! Hence the error message.

salkobi · January 20, 2016, 6:07pm

Do you mean the values defined in the parameter properties count as 1 parameter for each value?

Let me rephrase this then:

@origincounty IN ('CAN','USA','MEX')
@DESTCOUNTRY IN ('CAN','USA','MEX')

Does that make a difference?

In the SP's WHERE clause looks like this:
FROM lane_history_leg
WHERE lgh_rstartdate>=@startdate
and lgh_rstartdate<@enddate
and lgh_rstartstate like (@origin_state)
and lgh_rendstate like (@dest_state)
and legstartcity like (@origin_city)
and legendcity like (@dest_city)
--and LegStartCountry in (@origin_country)
--and LegEndCountry in (@dest_country)
and ord_revtype1 in (select value from csvstringstotable_fn(@revtype1))
and trl_type1 in (select value from csvstringstotable_fn(@trltype1))
and lgh_carrier like (@carrier)
order by lgh_rstartdate desc

Let me know if this makes sense

Here's a screenshot if this makes it any clearer:
http://imgur.com/dtz6WCx

/EDIT: By the way, i get the same error if i exclude the parameters altogether and only add the origin_zip and dest_zip columns..

gbritton · January 20, 2016, 6:15pm

I mean this: If I have a stored proc called foo and I call it like this:

exec foo @myarg='abc','def','ghi'

Then I've just passed three parameters to it, not one

@myarg is set to 'abc'
the second parameter is 'def'
the third is 'ghi'

I think that this is what is happening to you. You'll probably need to pass your countries differently, perhaps like:

'"CAN","USA","MEX"'

double-quoting the counties and wrapping the whole thing in single quotes. Then in the proc you'll need to unpack the value and build dynamic sql, since if e.g.

declare @foo varchar(50) = '''abc'',''def'',''ghi'''

select ...

where foo in (@foo)

doesn't work the way you want.

salkobi · January 20, 2016, 6:16pm

Ya, i realized when you mentioned it but that's not how it's set on the report, please see my edit in the post above.
Thanks for you input on this btw, much appreciated!

gbritton · January 20, 2016, 6:20pm

It still comes down to what is passed to SQL Server for those multi-valued parameters. If they're just bare:

'a', 'b', 'c'

You'll get the error message you see.

They need to be escaped somehow

salkobi · January 20, 2016, 6:36pm

I see, that makes sense...

But why would i get the same error when i'm only adding the origin/dest zip columns and exlcuding the parameters completely ( I commented out everything relative to origin/dest country and only left the zip code columns..still got the same error)
SORRY THIS WAS TOTALLY WRONG ; View reply below...

gbritton · January 20, 2016, 6:59pm

to dig into it, fire up SQL Server Profiler then run your report. You'll get to see all the SQL sent to the server. That way you can zero in on the problem chlld

salkobi · January 20, 2016, 7:11pm

Alrighty, will do!

Thanks again for your help with this today, much appreciated!! I'll update this post as soon as i have some news.

BPphilip · January 21, 2016, 7:34am

Have you tried using the filter in the SSRS tablix properties? Instead of passing your multi value parameters to stored procedure you use the filter using the operator "IN".

salkobi · January 21, 2016, 6:27pm

ISSUE RESOLVED!!!!

It didn't even cross my mind to look at the order of the columns in the Create Table statement. I made sure they were in the same position everywhere in the SP and was able to add the Zip columns. Next i added the country columns; We're all golden up until now. I went the next step and cofigured the 2 new Origin/Dest Country parameters, A OK!

I go into SSRS, edit the report query, add my parameters and deault values. Lo and Behold, WE HAVE DATA!!!

Woopy!!

Sorry i thorugh you guys off a wild goose chase. I still learnt a lot on the way though! Thanks for your help everyone!!