passing multivalue parameter in a URL action

How can I pass a multivalue parameter via a URL action on a report item?

I've tried using Split and Join but it doesnt work.

Any ideas?

Multivalue paramters are accessed in URL within the following schema:

Report_Param_Name=A&Report_Param_Name=B&Report_Param_Name=C

so AFAIK you would have to write some own sophisticated code to do this.

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de|||

What if we have to pass select all as a parameter?

I am trying to pass select all as parameter because I have a lot of items and IE won't let me pass such a big url.

Please help.

|||

There is no special "select all" value.

If you have many values, maybe you should redesign your query/report so that you add a special value to the valid value list which has the implicit semantics of "select all" in your dataset.

-- Robert

Passing a subquery as a parameter to a user defined function

I have a function which accepts a string as a parameter and returns a table.
It is a bit like a split function. It works when I pass the string as a
variable. When I try to pass in the string variable as the result of a
subquery I get an error.
This works
declare @.test varchar(50)
set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
This doesn't
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
NS_REPORT_SAVE where savereportid = 8),',')
I get
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a table.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.|||The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>> I have a function which accepts a string as a parameter and returns a
>> table.
>> It is a bit like a split function. It works when I pass the string as a
>> variable. When I try to pass in the string variable as the result of a
>> subquery I get an error.
>> This works
>> declare @.test varchar(50)
>> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
>> This doesn't
>> declare @.test varchar(50)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
>> NS_REPORT_SAVE where savereportid = 8),',')
>> I get
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near '('.
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near ','.
>

passing \ as a parameter to a function

I have a UDF for splitting delimiter strings:
CREATE FUNCTION Split
(@.Source varchar (5000)
,@.Delimiter varchar (10) = ','
)
RETURNS @.T table (F1 varchar (100))
AS
--Accepts a source string @.Source and parses it to break it up into single
units
--delineated by @.Delimiter.
--Returns a Single Column Table with each row containing one of the split
chunks
--e.g. @.Source = 'SP,AQ,YD'
-- Returns @.T with three rows:
-- SP
-- AQ
-- YD
-- or @.Source = 'P1=V1, P2=V2'
-- Returns @.T with two rows:
-- P1=V1
-- P2=V2
BEGIN
DECLARE @.w varchar (5000)
DECLARE @.inte int
SET @.W = @.Source + @.Delimiter
WHILE len(@.W) > 0
BEGIN
SET @.inte = patindex('%,%',@.w) - 1
INSERT @.T (F1) VALUES (substring(@.W, 1, @.inte))
SET @.W = substring(@.W,@.inte+2,len(@.W)-(@.inte+1))
END
RETURN
END
A typical use of this would be:
DECLARE @.Reps table (RepIn varchar (20))
INSERT @.Reps (RepIn) SELECT * FROM Split(@.RepSelect,',')
Assuming that a parameter @.RepSelect is passed, containing 'Fred,Joe,Andy,
the @.Reps table would have three records with one of the names in each.
e.g. Fred
Joe
Andy
It can also be called "inline":
SELECT s.* FROM tblSales s
INNER JOIN (SELECT * FROM split(@.Reps,',') r
ON s.Rep = r.F1
This all works fine until I try to call it using '\' as the delimiter
parameter, then I just get an error that says "Invalid length parameter
passed to the substring function"
Here is sample code to run this:
DECLARE @.NewPath varchar (100)
--Use this pair and it works
-- SET @.NewPath = 'c:,MSSQL,Data,MSSQL,DBFile.mdf'
-- SELECT * FROM split(@.NewPath, ',')
--Use this pair and it fails
SET @.NewPath = 'c:\MSSQL\Data\MSSQL\DBFile.mdf'
SELECT * FROM split(@.NewPath, '\')
Sorry to be so long winded, but does anyone have any ideas?
Regards,
-Rob
--
Robert Marmion
ITBridges Inc
609 844 0949
"Connecting your Business with your Software"Hi
I have modified a little bit the function written by Dejan Sarka.
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000),@.delim VARCHAR(2))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(@.delim,@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(@.d
elim),8000)
END
RETURN
END
GO
--A typical use of this would be:
DECLARE @.Reps table (RepIn varchar (20))
declare @.RepSelect varchar(50)
set @.RepSelect='Fred\Joe\Andy'
INSERT @.Reps (RepIn) SELECT * FROM TsqlSplit(@.RepSelect,'')
select * from @.Reps
"RMarmion" <RMarmion@.Discussions.Microsoft.com> wrote in message
news:76AF1578-99B3-45FE-A24E-4D82B5435CF8@.microsoft.com...
>I have a UDF for splitting delimiter strings:
> CREATE FUNCTION Split
> (@.Source varchar (5000)
> ,@.Delimiter varchar (10) = ','
> )
> RETURNS @.T table (F1 varchar (100))
> AS
> --Accepts a source string @.Source and parses it to break it up into
> single
> units
> --delineated by @.Delimiter.
> --Returns a Single Column Table with each row containing one of the split
> chunks
> --e.g. @.Source = 'SP,AQ,YD'
> -- Returns @.T with three rows:
> -- SP
> -- AQ
> -- YD
> -- or @.Source = 'P1=V1, P2=V2'
> -- Returns @.T with two rows:
> -- P1=V1
> -- P2=V2
> BEGIN
> DECLARE @.w varchar (5000)
> DECLARE @.inte int
> SET @.W = @.Source + @.Delimiter
> WHILE len(@.W) > 0
> BEGIN
> SET @.inte = patindex('%,%',@.w) - 1
> INSERT @.T (F1) VALUES (substring(@.W, 1, @.inte))
> SET @.W = substring(@.W,@.inte+2,len(@.W)-(@.inte+1))
> END
> RETURN
> END
> A typical use of this would be:
> DECLARE @.Reps table (RepIn varchar (20))
> INSERT @.Reps (RepIn) SELECT * FROM Split(@.RepSelect,',')
> Assuming that a parameter @.RepSelect is passed, containing 'Fred,Joe,Andy,
> the @.Reps table would have three records with one of the names in each.
> e.g. Fred
> Joe
> Andy
> It can also be called "inline":
> SELECT s.* FROM tblSales s
> INNER JOIN (SELECT * FROM split(@.Reps,',') r
> ON s.Rep = r.F1
> This all works fine until I try to call it using '' as the delimiter
> parameter, then I just get an error that says "Invalid length parameter
> passed to the substring function"
> Here is sample code to run this:
> DECLARE @.NewPath varchar (100)
> --Use this pair and it works
> -- SET @.NewPath = 'c:,MSSQL,Data,MSSQL,DBFile.mdf'
> -- SELECT * FROM split(@.NewPath, ',')
> --Use this pair and it fails
> SET @.NewPath = 'c:\MSSQL\Data\MSSQL\DBFile.mdf'
> SELECT * FROM split(@.NewPath, '')
> Sorry to be so long winded, but does anyone have any ideas?
> Regards,
> -Rob
> --
> Robert Marmion
> ITBridges Inc
> 609 844 0949
> "Connecting your Business with your Software"|||Here lies the pain:
> SET @.inte = patindex('%,%',@.w) - 1
You're still looking for the comma. And, BTW, you could just as well use
CHARINDEX.
ML|||Duh!
Thank you both so much. What a stupid error!!
-Rob
--
Robert Marmion
ITBridges Inc
609 844 0949
"Connecting your Business with your Software"
"ML" wrote:

> Here lies the pain:
> You're still looking for the comma. And, BTW, you could just as well use
> CHARINDEX.
>
> ML

Partitions on RAID Array

We have a 12 disk 2.4TB Raid 10 Array, where we have 3 large database data
files, theres been a suggestion that we split the physical drive into 3
partitions so if we have to grow the files in future the files will stay
contiguous on the disk.. ..I'm not worried about splitting the array into
three from a size point of view we have bags of space for future growth..
...I'm aware the databases should be sized properly so they don't autogrow and
that minimal fragmentation at the os level will have little to no impact on
the performance... ...however for sake of argument will splitting the RAID
array into the 3 partitions keep the database files contiguous on the disks,
and would there be any performance hit on the array by splitting it into 3
volumes?
Thanks in advance
Ben
I'd be inclined to not create three logical partitions. I don't have any
current empirical data to back me up on this. But I do remember a Compaq
study a few years ago on using a single partition vs. multiple logical
partitions on a physical device, and the single partition configuration came
out better performance wise. I'm not claiming that the Compaq study is still
relevant. But then I don't see any substantial benefit of creating three
logical partitions.
The practice I follow has always been: single physical device (as presented
to the OS) -> single partition -> single NTFS volume.
Do note that if you have files on the same volume, moving a file to another
place on that same volume is a filesystem metadata operation. Moving a file
across volumes, however, must move data. If you don't plan to move your
files, this is not an issue.
Linchi
"Ben UK" wrote:

> We have a 12 disk 2.4TB Raid 10 Array, where we have 3 large database data
> files, theres been a suggestion that we split the physical drive into 3
> partitions so if we have to grow the files in future the files will stay
> contiguous on the disk.. ..I'm not worried about splitting the array into
> three from a size point of view we have bags of space for future growth..
> ..I'm aware the databases should be sized properly so they don't autogrow and
> that minimal fragmentation at the os level will have little to no impact on
> the performance... ...however for sake of argument will splitting the RAID
> array into the 3 partitions keep the database files contiguous on the disks,
> and would there be any performance hit on the array by splitting it into 3
> volumes?
> Thanks in advance
> Ben

Partitions on RAID Array

We have a 12 disk 2.4TB Raid 10 Array, where we have 3 large database data
files, theres been a suggestion that we split the physical drive into 3
partitions so if we have to grow the files in future the files will stay
contiguous on the disk.. ..I'm not worried about splitting the array into
three from a size point of view we have bags of space for future growth..
..I'm aware the databases should be sized properly so they don't autogrow a
nd
that minimal fragmentation at the os level will have little to no impact on
the performance... ...however for sake of argument will splitting the RAID
array into the 3 partitions keep the database files contiguous on the disks,
and would there be any performance hit on the array by splitting it into 3
volumes?
Thanks in advance
BenI'd be inclined to not create three logical partitions. I don't have any
current empirical data to back me up on this. But I do remember a Compaq
study a few years ago on using a single partition vs. multiple logical
partitions on a physical device, and the single partition configuration came
out better performance wise. I'm not claiming that the Compaq study is still
relevant. But then I don't see any substantial benefit of creating three
logical partitions.
The practice I follow has always been: single physical device (as presented
to the OS) -> single partition -> single NTFS volume.
Do note that if you have files on the same volume, moving a file to another
place on that same volume is a filesystem metadata operation. Moving a file
across volumes, however, must move data. If you don't plan to move your
files, this is not an issue.
Linchi
"Ben UK" wrote:

> We have a 12 disk 2.4TB Raid 10 Array, where we have 3 large database data
> files, theres been a suggestion that we split the physical drive into 3
> partitions so if we have to grow the files in future the files will stay
> contiguous on the disk.. ..I'm not worried about splitting the array into
> three from a size point of view we have bags of space for future growth..
> ..I'm aware the databases should be sized properly so they don't autogrow
and
> that minimal fragmentation at the os level will have little to no impact o
n
> the performance... ...however for sake of argument will splitting the RAID
> array into the 3 partitions keep the database files contiguous on the disk
s,
> and would there be any performance hit on the array by splitting it into 3
> volumes?
> Thanks in advance
> Ben|||Hi Ben,
I tend to agree with Linchi, that splitting it up might not give you
much in term of performance. If you should gain from splitting the files
up, you should split them up on physical different spindles and best of
all on seperate disk controllers. If you go down that route, you should
at the same time add some more spindles to each array - otherwise I
don't think you'll gain anything from the change.
You can also try to look at www.storageperformance.org to see if you can
find some usefull info in there.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Ben UK wrote:
> We have a 12 disk 2.4TB Raid 10 Array, where we have 3 large database data
> files, theres been a suggestion that we split the physical drive into 3
> partitions so if we have to grow the files in future the files will stay
> contiguous on the disk.. ..I'm not worried about splitting the array into
> three from a size point of view we have bags of space for future growth..
> ..I'm aware the databases should be sized properly so they don't autogrow
and
> that minimal fragmentation at the os level will have little to no impact o
n
> the performance... ...however for sake of argument will splitting the RAID
> array into the 3 partitions keep the database files contiguous on the disk
s,
> and would there be any performance hit on the array by splitting it into 3
> volumes?
> Thanks in advance
> Ben