| Author |
Topic |
|
aoriju
Posting Yak Master
156 Posts |
 Posted - 2009-03-31 : 01:07:02
|
| Hai Experts What is the Maximum Size of a SSRS String Parameter ? I have a String Parameter that contain 2000 items .Each Items has 4 Characters.Each Items separated with Comma Separated One.I am Sending this item ids into the procedure.Procedure not Exeuting.....Whats the Reason help me. |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-31 : 12:55:35
|
quote:
Originally posted by aoriju
Hai Experts What is the Maximum Size of a SSRS String Parameter ? I have a String Parameter that contain 2000 items .Each Items has 4 Characters.Each Items separated with Comma Separated One.I am Sending this item ids into the procedure.Procedure not Exeuting.....Whats the Reason help me.
what is the length of parameter used in procedure? |
 |
|
|
WabbitSlayer
Starting Member
6 Posts |
Posted - 2009-04-28 : 07:44:21
|
| Having the same problem. Only - my procedure execute, but doesn't return all the data. If I execute it in SQL Server Management Studio I get the answer I'm expecting. So somewhere there is a implicit conversion ... Any ideas on how to break this string, and maybe pass it as two or three agruments? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-04-29 : 10:56:29
|
| how are you using the comma seperated string in procedure for filteration? whats the length of parameter used? |
|
|
|
WabbitSlayer
Starting Member
6 Posts |
Posted - 2009-04-29 : 11:11:57
|
| Hi, Yeah, I end up with a comma delimited string ('Descr,descr,descr,descr,descr,descr,...')My procedure breaks up this string again and use the values to determain the outcome. If I take the string (+/- 560 bytes) and execute it in SQL Management Studio, I get the desired results. With my report, it behaves like it's not getting the entire string, thus not giving me the results expected. It's as if there's a limit to SSRS string length - I just can find anything that mention this.Any help would be appreciated!Thanks in advance |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-04-29 : 13:28:11
|
| if it works fine in mgmnt studio i dont think there will be any problem in report. what you need in procedure is either to use string parsing function to parse the comma seperated values into individual values or use something likeWHERE ',' + @YourParam + ',' LIKE '%,' + YourField + ',%' |
|
|
|
WabbitSlayer
Starting Member
6 Posts |
Posted - 2009-04-30 : 03:09:50
|
| Exactly my point. It's supposed to work fine, but it doesn't ... as if there's a implicit conversion or something. The Stored Proc's parameter is a varchar(1000), since the actual string is only 560 bytes, it should work. I do parse the comma seperated values with (below) by using this function in the stored procedure.... CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000) )RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )ASBEGIN DECLARE @String VARCHAR(10) WHILE LEN(@StringInput) > 0 BEGIN SET @String = LEFT(@StringInput, ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1), LEN(@StringInput))) SET @StringInput = SUBSTRING(@StringInput, ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0), LEN(@StringInput)) + 1, LEN(@StringInput)) INSERT INTO @OutputTable ( [String] ) VALUES ( ltrim(rtrim(@String)) ) END RETURNENDAny more ideas? |
|
|
|
|
Maximum Size of a SSRS String Parameter