RS2005's own SPs to add users to RS

Dear Anyone,

Is it possible to use Reporting Services' own stored procedure to add users directly into RS's security roles?

Thanks,

Joseph

Direct access to the RS catalog is not supported. Is there a reason why SOAP APIs are not enough?

Thanks

Tudor

sql

RS2005's own SPs to add users to RS

Dear Anyone,

Is it possible to use Reporting Services' own stored procedure to add users directly into RS's security roles?

Thanks,

Joseph

Direct access to the RS catalog is not supported. Is there a reason why SOAP APIs are not enough?

Thanks

Tudor

RS2005 multi-select parameter

How do I use the mult-select parameter option on a report and pass it
to the stored procedure?
ie. if I have a multi-select list of employees and I pass in the
employeeid to the proc
would I?
create procedure show_employees(@.empid int)
as
select * from employees where empid in (@.empid)
would that work?
Regards,
Tom OlthoffNope. The stored procedure won't work (note that SQL will work in RS).
Why it 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
<OTSolutions@.shaw.ca> wrote in message
news:1144854681.805431.214920@.i40g2000cwc.googlegroups.com...
> How do I use the mult-select parameter option on a report and pass it
> to the stored procedure?
> ie. if I have a multi-select list of employees and I pass in the
> employeeid to the proc
> would I?
> create procedure show_employees(@.empid int)
> as
> select * from employees where empid in (@.empid)
> would that work?
> Regards,
> Tom Olthoff
>|||You can do this using XML I believe.
Pass an XML string that looks like this:
<root>
<param1>
<multiParam>value1</multiParam>
<multiParam>value2</multiParam>
<multiParam>value3</multiParam>
</param1>
</root>
And then use SQL's readXML function to read multiple values from the
XML.|||The one problem with that approach is its not really a list of
parameters its one paramter that is a string of XML.
regards,
Stas K.|||You can read about this approach with the sp_XML_PrepareDocument
function:
http://msdn2.microsoft.com/en-us/library/ms187367(SQL.90).aspx
as for the actual access to the xml:
OPENXML( @.XMLString, N'/root/param1/multiparam/*')
http://msdn2.microsoft.com/en-us/library/ms186918(SQL.90).aspx
regards,
Stas K.

RS: multiple SP more performant?

Hi,
If I use for one given Report (Reporting Services) multiple Stored
Procedures as DataSource instead of 1 big Stored Procedure, will this be
more performant or not? Are these multiple sotred procedures executed
simultaneously, of one after the other?
Thanks a lot in advance,
PieterHey Lot of things involved. Stored procedures are executed sequencial if you
have one processor, if you have multiple then it may go as a parallel
execution. Again
it depends if you have complex calculation in single stored procedure then
it may take some time. Otherwise it should not make much of a difference if
it is optimized.
Amarnath
"Pieter" wrote:
> Hi,
> If I use for one given Report (Reporting Services) multiple Stored
> Procedures as DataSource instead of 1 big Stored Procedure, will this be
> more performant or not? Are these multiple sotred procedures executed
> simultaneously, of one after the other?
> Thanks a lot in advance,
> Pieter
>
>

RS: multiple SP more performant?

Hi,
If I use for one given Report (Reporting Services) multiple Stored
Procedures as DataSource instead of 1 big Stored Procedure, will this be
more performant or not? Are these multiple sotred procedures executed
simultaneously, of one after the other?
Thanks a lot in advance,
PieterHey Lot of things involved. Stored procedures are executed sequencial if you
have one processor, if you have multiple then it may go as a parallel
execution. Again
it depends if you have complex calculation in single stored procedure then
it may take some time. Otherwise it should not make much of a difference if
it is optimized.
Amarnath
"Pieter" wrote:

> Hi,
> If I use for one given Report (Reporting Services) multiple Stored
> Procedures as DataSource instead of 1 big Stored Procedure, will this be
> more performant or not? Are these multiple sotred procedures executed
> simultaneously, of one after the other?
> Thanks a lot in advance,
> Pieter
>
>

RS: multiple SP more performant?

Hi,
If I use for one given Report (Reporting Services) multiple Stored
Procedures as DataSource instead of 1 big Stored Procedure, will this be
more performant or not? Are these multiple sotred procedures executed
simultaneously, of one after the other?
Thanks a lot in advance,
PieterHey Lot of things involved. Stored procedures are executed sequencial if you
have one processor, if you have multiple then it may go as a parallel
execution. Again
it depends if you have complex calculation in single stored procedure then
it may take some time. Otherwise it should not make much of a difference if
it is optimized.
Amarnath
"Pieter" wrote:
> Hi,
> If I use for one given Report (Reporting Services) multiple Stored
> Procedures as DataSource instead of 1 big Stored Procedure, will this be
> more performant or not? Are these multiple sotred procedures executed
> simultaneously, of one after the other?
> Thanks a lot in advance,
> Pieter
>
>

rs drops first column in data set

I started having this problem some time ago when trying to build a report using a stored procedure. Existing reports work fine, it is only new reports that result in this error:

The data set ‘WeeklyManagement’ contains a definition for the field ‘BrewNumber’. This field is missing from the returned result set from the data source.

I get this error when I try to preview the report. The missing field is always the first column in the dataset. I have tried everything I can think of, including repeating the column, rewriting the sproc, rewriting the report, everything. I cannot find any reason why this should be happening. Can anyone help? BTW, when I create the dataset and run the sproc, all fields are returned fine. When I examine the xml, all the fields are there, correctly defined.

Help, please! TIA. D. Lewis

Can you publish the sproc? Print statements did this to us in the past.|||

Hmmm. Very interesting. Following is the sproc, which I altered to comment out the 'Print' statements (yes, I did have some in there for debugging purposes). I also as an experiment repeated the first column to see if it would work, and RS began dropping that new column; error message as below:
Build complete -- 0 errors, 0 warnings

The data set ‘WeeklyManagement’ contains a definition for the field ‘B’. This field is missing from the returned result set from the data source.

Preview complete -- 0 errors, 1 warnings
The sproc is a bit messy, and uses a temp table, but for completeness' sake here it is:

use snb01
go

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Alter PROCEDURE spBrewhouseOverview
AS
Declare @.BeginDate smalldatetime
Declare @.EndDate smalldatetime
Set @.BeginDate = DATEADD(dd,-45,Getdate())--'9/1/05'
set @.EndDate = Getdate()--'9/15/05'

--Print convert(varchar,@.begindate)
--Print convert(varchar,@.enddate)

--spBrewhouseOverview '8/2/05','8/3/05'
--5/20/03
--Have to convert the dates to char then to date again
--to strip out the time portion that crystall passes
--also added one day to the end time to
--allow for the fact that the end day would be midnight
--at the beginning of the ending day, which would otherwise
--effectively eliminate all brews on that day from showing up.
BEGIN
CREATE TABLE [#tblOverview] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ProBrewID] [int] NULL,
[BrewNumber] [int] NULL ,
[Kettle] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartTimeInKettle] [smalldatetime] NULL ,
[StartTimeBoil] [smalldatetime] NULL ,
[EndTimeBoil] [smalldatetime] NULL ,
[Hop1Time] [smalldatetime] NULL ,
[Hop2Time] [smalldatetime] NULL ,
[MossTime] [smalldatetime] NULL ,
[Hop3Time] [smalldatetime] NULL ,
[Hop4Time] [smalldatetime] NULL ,
/*[StartTimeMash] [smalldatetime] NULL ,
[SaccReqTime] [smalldatetime] NULL ,
[SaccTime] [smalldatetime] NULL ,
[MashOutTime] [smalldatetime] NULL ,
[StartTimeInLauter] [smalldatetime] NULL ,
[StartTimeVorlauf] [smalldatetime] NULL ,
[StartTimeLauter] [smalldatetime] NULL ,
[EndTimeLauter] [smalldatetime] NULL ,
[GravityVorlauf] [int] NULL ,
[GravityFirstRunnings] [int] NULL ,
[GravityLastRunnings] [int] NULL ,
[GravityLauter] [int] NULL ,
[HazeFirstRunnings] [int] NULL ,
[HazeLauterVorlauf] [int] NULL ,
[HazeLauterLauter] [int] NULL ,*/
[StartTimeRest] [smalldatetime] NULL ,
[StartTimeWhirlWaterVor] [smalldatetime] NULL,
/*[MashHour] [int] NULL,
[LauterHour] [int] NULL,
[KettleHour] [int] NULL,
[WhirlpoolHour] [int] NULL,
[BrewerMash] [Varchar] (20) NULL,
[BrewerLauter] [Varchar] (20) NULL,*/
[BrewerKettle] [Varchar] (20) NULL,
[BrewerWhirlpool] [Varchar] (20) NULL,
CONSTRAINT [PK_tblOverview] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
END

SET NOCOUNT ON
INSERT #tblOverview
(Probrewid
, Brewnumber
, Kettle
, StarttimeInKettle
, StartTimeBoil
, EndTimeBoil
, Hop1Time
, Hop2Time
, MossTime
, Hop3Time
, Hop4Time/*
, StartTimeMash
, SaccReqTime
, SaccTime
, MashOutTime
, StartTimeInLauter
, StartTimeVorlauf
, StartTimeLauter
, EndTimeLauter
, GravityVorlauf
, GravityFirstRunnings
, GravityLastRunnings
, GravityLauter
, HazeFirstRunnings
, HazeLauterVorlauf
, HazeLauterLauter*/
, StartTimeRest
, StartTimeWhirlWaterVor)
(
Select a.probrewid
, a.Brewnumber
, a.Kettle
, a.StarttimeInKettle
, a.StartTimeBoil
, a.EndTimeBoil
, a.Hop1Time
, a.Hop2Time
, a.MossTime
, a.Hop3Time
, a.Hop4Time
/*, b.StartTimeMash
, b.SaccReqTime
, b.SaccTime
, b.MashOutTime
, c.StartTimeInLauter
, c.StartTimeVorlauf
, c.StartTimeLauter
, c.EndTimeLauter
, c.GravityVorlauf
, c.GravityFirstRunnings
, c.GravityLastRunnings
, c.GravityLauter
, c.HazeFirstRunnings
, c.HazeLauterVorlauf
, c.HazeLauterLauter*/
, d.StartTimeRest
, d.StartTimeWhirlWaterVor
FROM Proleit.dbo.tblRptKettleAll a
INNER JOIN Proleit.dbo.tblRptMashingAll b on a.Probrewid=b.probrewid
INNER JOIN Proleit.dbo.tblRptLauterAll c on a.Probrewid=c.Probrewid
INNER JOIN Proleit.dbo.tblRptWhirlpoolAll d on a.Probrewid=d.probrewid
WHERE a.StartTimeBoil>=CONVERT(datetime,(CONVERT(varchar,@.BeginDate,112)))
AND a.StartTimeBoil<= DATEADD(day,1,CONVERT(datetime,(CONVERT(varchar,@.EndDate,112))))
)

--Comment out for troubleshooting
--declare a cursor for the table, loop through
--and update each brewer

DECLARE CurC CURSOR FOR SELECT Probrewid,MossTime,StartTimeRest
FROM #tblOverview
DECLARE @.Probrewid int
DECLARE @.TimeMoss datetime
DECLARE @.TimeWhirl datetime
OPEN CurC

FETCH NEXT FROM CurC INTO @.probrewid,@.TimeMoss,@.TimeWhirl
BEGIN
WHILE @.@.FETCH_STATUS=0
BEGIN

--Print convert(varchar,@.probrewid) + ' is probrewid'
--Print convert(varchar,@.TimeMoss) + ' is @.TimeMoss'
--Exec spBrewhouseOverviewSacc @.probrewid,@.timeSacc
--Exec spBrewhouseOverviewVorlauf @.probrewid,@.timeVorlauf
Exec Proleit.dbo.spBrewhouseOverviewKettle @.probrewid,@.timeMoss
Exec Proleit.dbo.spBrewhouseOverviewWhirlpool @.probrewid,@.timeWhirl

FETCH NEXT FROM CurC INTO @.probrewid,@.TimeMoss,@.TimeWhirl
END
END
CLOSE CurC
DEALLOCATE CurC

SET NOCOUNT ON
UPDATE #tblOverview
SET BrewerKettle='None Recorded'
WHERE BrewerKettle is null

UPDATE #tblOverview
SET BrewerWhirlpool='None Recorded'
WHERE BrewerWhirlpool is null

SELECT
a.BrewNumber as B
, a.BrewNumber
, a.Kettle
, Convert(datetime,Convert(varchar,a.StartTimeInKettle,110)) as BrewDate
, DATEDIFF(n,MossTime,Hop3Time) as Minutes
, BrewerKettle as Brewer
, 'Hop3Time' as Type
FROM #tblOverview a
WHERE DATEDIFF(n,MossTime,Hop3Time)<10
OR DATEDIFF(n,MossTime,Hop3Time)>13

UNION ALL

SELECT
a.BrewNumber as B
, a.BrewNumber
, a.Kettle
, Convert(datetime,Convert(varchar,a.StartTimeInKettle,110)) as BrewDate
, DATEDIFF(n,Hop3Time,Hop4Time) as Minutes
, BrewerKettle as Brewer
, 'Hop4Time' as Type
FROM #tblOverview a
WHERE DATEDIFF(n,Hop3Time,Hop4Time)<10
OR DATEDIFF(n,Hop3Time,Hop4Time) > 13

UNION ALL

SELECT
a.BrewNumber as B
, a.BrewNumber
, a.Kettle
, Convert(datetime,Convert(varchar,a.StartTimeInKettle,110)) as BrewDate
, DATEDIFF(n,StartTimeRest,StartTimeWhirlWaterVor) as Minutes
, BrewerWhirlpool
, 'WhirlpoolTime' as Type
FROM #tblOverview a
WHERE DATEDIFF(n,StartTimeRest,StartTimeWhirlWaterVor)>21

Drop Table #tblOverview

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||If you're not working with a whole lot of data, an @.Table variable instead of a temp table may be more efficient and you won't need the drop table statement at the end.
Try adding to your UNION ALL statements with a select statement at the top that sets a default value.... eg. SELECT 0 as B, 0 as BrewNumber, 0 as Kettle, 0 as BrewDate, etc.... to see if it is a problem with the naming of the fields and the first result set not returning a value.
Maybe dump that above dummy SELECT statement in various places within the stored procedure, so that you can find out where it is breaking. (not returning the right result set)
It could have something to do with the 2 execs you have going there... It would be interesting to see the SQL messages pane.|||I will set to work on those suggestions, thanks.

Just to clarify, the result set returned in Query Analyzer is fine, with no errors and no messages. When I refresh the dataset from within visual studio while designing the report, I also get the entire set with no issues. It is only when I try to preview the report that the thing bonks -- i.e., the first column is dropped and VS tells me that it is not defined.

Also, I went back and examined the two 'exec ....' statements, and they are simple update statements, with no 'Print....' or anything else. Very innocuous, actually. I placed them in separate sprocs because they are reused elsewhere and are long.