Parameter multi-value problem when using a stored procedure

Hi folks,
I am describing the issue as below:
1. create a stored procedure as below
....where age in (@.p_age)
note: age is the table coumn of table table1 with datatype tinyint
2. .... and create a second dataset for parameter @.p_age
select distinct age from table1
3. associate the parameter...run it
4. There is no problem with single value. But when two ages are selected,
I got error message, "...Erro convert data type nvarchar to tinyint"
Please advise. PeterYou cannot pass and use multi-value parameters to a stored procedure and use
it in a query as you have. If that query was in RS itself then it would
work. This is not a RS thing, it is a SQL Server stored procedure issue.
Just try it from Query Analyzer and you will see what I mean. I do the
following, I create
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:74D9A9F0-B1F9-4925-8080-87FC99215462@.microsoft.com...
> Hi folks,
> I am describing the issue as below:
> 1. create a stored procedure as below
> ....where age in (@.p_age)
> note: age is the table coumn of table table1 with datatype tinyint
> 2. .... and create a second dataset for parameter @.p_age
> select distinct age from table1
> 3. associate the parameter...run it
> 4. There is no problem with single value. But when two ages are selected,
> I got error message, "...Erro convert data type nvarchar to tinyint"
> Please advise. Peter|||Try again, sent before done:
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:74D9A9F0-B1F9-4925-8080-87FC99215462@.microsoft.com...
> Hi folks,
> I am describing the issue as below:
> 1. create a stored procedure as below
> ....where age in (@.p_age)
> note: age is the table coumn of table table1 with datatype tinyint
> 2. .... and create a second dataset for parameter @.p_age
> select distinct age from table1
> 3. associate the parameter...run it
> 4. There is no problem with single value. But when two ages are selected,
> I got error message, "...Erro convert data type nvarchar to tinyint"
> Please advise. Peter

Page Not Found, Sort Of

System: Win2k3, SQL2k5, Sharepoint (all on same box)
Note: default website port is 8000 (I mentioned this because it's non
standard)
I had this system all setup and running properly then all of the sudden
some brilliant support person at our data center decided to change the
I.P. of the server. This wreaked havoc on most of the code on that box
as I had to change all of the config files, DNS, and HTTP headers.
I finally got most of the pieces running again, but I have a couple an
issue. Currently there are two instances of SSRS running on the box. I
have the "old" one's service stopped, so it is not running, but when
browsing to http://localhost:8000/reports it is still trying to go to
the stopped instance. How can I change that? I've tried the rsconfig
utility, but I may not have the correct parameters.
thanks,
.britOh, one more thing. The "Page Not Found" error mentioned in the subject
refers to a very strange problem that I feel is resulting from having
two instances of SSRS running.
I have a domain name pointing to the default IP for the box. If I use
[domain url]/reports everything works great in Report Manager until I
actually go to view a report and then I get a Page Not Found error
within Report Manager. The error does not take over the entire screen,
just the portion in Rpt Mgr that would show the report.
If I go straight to the report using [domain url]/reportserver/[path of
report] they work great.
What gives?
thanks,
.brit