SQLTeam.com | Weblogs | Forums

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

tsql
sql2008r2

#1

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.


#2

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


#3

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


#4

A signature is just the first few lines:

CREATE PROCEDURE ...

AS

or

CREATE FUNCTION ...

AS


#5

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:       
*/

#6

So, when you get this error message:

What are the parameters passed to the proc?


#7

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"


#8

So your proc is getting these parametesr:

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


#9

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


#10

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.


#11

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!


#12

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


#13

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...:disappointed_relieved::disappointed_relieved::disappointed_relieved::disappointed_relieved:


#14

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


#15

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.


#17

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


#18

ISSUE RESOLVED!!!! :smile:

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!!