Is it possible to stop SSRS from converting parameters to unicode?

Question

Is it possible to stop SSRS from converting parameters to unicode?

Adam Bean

One Orange Chip

Points: 26631
More actions
May 11, 2009 at 11:29 am

#209170

Hey all, I've had a question running in the SSRS forums for a week and haven't heard anything ... so sorry for double posting, but I'm in a bind right now.
http://www.sqlservercentral.com/Forums/Topic711010-150-1.aspx#bm714348
Long story short, I populate a parameter based on a dataset which pulls from a VARCHAR column. Total it's about 6600 characters in length. My input parameter on the procedure is a VARCHAR(8000), but does not work because I get a "Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query." Changed to an NVARCHAR on the proc, works just fine ... but as stated, the users are looking to pass more into the parameter and 4000 will not suffice (SQL2000, yes, I know).
So, after tracing, I found that SSRS is wrapping all my parameters with a 'N. Can I stop this? If not, any possible work arounds for this scenario?
Thanks all

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

Ok after understanding the data a little more ... the whole unicode thing doesn't really matter ... as the user can exceed 8000 when passing in this multi-value parameter.

So now I need to find a way to not exceed that on the parameter or a different way to handle it. Working on a couple crazy ideas now ...

Mahesh Hello Mr or Mrs. 500 Points: 580 More actions · Answer 2

Did you ever find a solution/workaround for SSRS multi-value parameter converting to unicode - issue? Changing to stored procedures is not an option for me now.

Mahesh Hello Mr or Mrs. 500 Points: 580 More actions · Answer 3

Work around to overcome SSRS report performance due to UNICODE conversion issue:

I have used a new parameter (of type Internal) which collects/duplicates the parameter values as comma separated in string from orig parameter.

In the report Dataset query parse the comma separated string into list using XML trick and load into a variable table.

Use the variable table in WHERE IN clause

Steps:

Create a new Internal parameter (call it InternalParameter1)

Under Default Values -> Specify values : Add Exp : =join( Parameters!OrigParameter1.Value,",")

Pass/Use the InternalParameter1 in your dataset query.

/*

Example code

DECLARE @InternalParameter1 NVARCHAR(MAX)

SET @InternalParameter1 = '100167600,

100167601,

4302853605,

4030753556,

4026938411

'

*/

--- Load comma separated string to a temp variable table ---

SET ARITHABORT ON

DECLARE @T1 AS TABLE (PARALIST VARCHAR(100))

INSERT @T1 SELECT Split.a.value('.', 'VARCHAR(100)') AS CVS FROM

( SELECT CAST ('<M>' + REPLACE(@InternalParameter1, ',', '</M><M>') + '</M>' AS XML) AS CVS ) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)

--- Report Dataset query ---

SELECT CONTRACT_NO, report fields… FROM mytable

WHERE CONTRACT_NO IN (SELECT PARALIST FROM @T1) -- Use temp variable table in where clause