Boot.ini file: reporting

Showing posts with label reporting. Show all posts

Thursday, March 22, 2012

BUG? or intended that in order to move or rename reports....

...anywhere in the folder structure a person must be set up as a Content Manager at the Reporting Services ROOT (and at every level of the folder hierarchy down to the level that they are moving/renaming)?

Sure, you can remove them from the role in other folders where they should not be moving or renaming objects, but it seems you still have to have them at the root (and down the chain), which gives them far more capabilities than you might intend.

Jeff

After further examination into this problem, it appears in order for someone to move or rename a report or file, they need two things:

1) Content Manager role for the folder they are moving to/from or renaming files

2) Content Manager role for the root RS folder, but not up or down the chain between the root or the folder above.

This has really got to be a bug, as I can't imagine Microsoft doing this on purpose.

Jeff

Tuesday, March 20, 2012

Bug?

Is there a bug in SQL Server Reporting Services which produces an error like
The value expression for the query parameter â'@.homebaseâ' refers to a
non-existing report parameter â'homebase_1â'
I made some changes in the dataset (1 of them) and it starts giving me this
error for no reason although the same parameter @.homebase is referred in more
than 5 datasets...
Any help is appreciated!
ThanksI got this before and did this...
1) Copy query in dataset
2) Delete dataset
3) re-create dataset
4) run-query in data view and it will re-build columns.
This is what I always do when I do anything to the columns.
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:Asim@.discussions.microsoft.com:
> Is there a bug in SQL Server Reporting Services which produces an error
> like
> The value expression for the query parameter '@.homebase' refers to a
> non-existing report parameter 'homebase_1'
> I made some changes in the dataset (1 of them) and it starts giving me
> this
> error for no reason although the same parameter @.homebase is referred in
> more
> than 5 datasets...
> Any help is appreciated!
> Thanks|||Click on the ..., go the parameters tab and remap your query parameter to
the report parameter. Sometimes RS gets confused.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:1C6BF53F-97E6-4678-B76F-66D0BB655A3F@.microsoft.com...
> Is there a bug in SQL Server Reporting Services which produces an error
like
> The value expression for the query parameter '@.homebase' refers to a
> non-existing report parameter 'homebase_1'
> I made some changes in the dataset (1 of them) and it starts giving me
this
> error for no reason although the same parameter @.homebase is referred in
more
> than 5 datasets...
> Any help is appreciated!
> Thanks
>|||Yep - bug
Lost over 2 days development time re-creating reports
NICE ONE MICROSOFT !
From http://www.developmentnow.com/g/115_2005_1_0_0_454665/Bug.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||Another option could have been to hand edit the rdl file to correct the
problem (this is an XML file).
We have done this once to use an option that the UI didn't offered in the
first release of Reporting Services...
--
Patrice
"om" <om_optical@.hotmail.com> a écrit dans le message de news:
1df92cf4-df9e-4b38-905c-79df77ba241f@.developmentnow.com...
> Yep - bug
> Lost over 2 days development time re-creating reports
> NICE ONE MICROSOFT !
>
> From http://www.developmentnow.com/g/115_2005_1_0_0_454665/Bug.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

BUG: SET FMTONLY not working on SQL Server 2000

Hello
I have reported this bug previosly, but Microsoft has so far not responded yet, so I am reporting it again. Basicly, SET FMTONLY is NOT working correctly with SQL Server 2000. Because of this bug I still have to use SQL Server 7.0 for my campuswide student information system application, although I really would like to upgrade to SQL Server 2000 :-
Here's the script to reproduce the bug
-- create the test tables, and populate the
Create Table dbo.USERS_TES
([USER_ID] int PRIMARY KEY not null
[NAME] varchar(20) null
[SURNAME] varchar(20) null
G
Set NoCount O
Insert Into dbo.USERS_TEST Values (1,'Bulent','Biyikoglu'
Insert Into dbo.USERS_TEST Values (2,'Jane','Doe'
G
Create Table dbo.USER_DETAILS_TES
([USER_DETAIL_ID] int PRIMARY KEY not null
[USER_ID] int NOT NULL FOREIGN KEY REFERENCES dbo.USERS_TEST([USER_ID])
FATHERS_NAME varchar(20) null
MOTHERS_NAME varchar(20) null
PLACE_OF_BIRTH varchar(40) null
CITY varchar(20) null
TOWN varchar(20) null
COUNTRY varchar(20) null
G
Set NoCount O
Insert Into dbo.USER_DETAILS_TEST Values (1,1,'Father1','Mother1','Polatli','Palo Alto','Stanford','Turkey'
Insert Into dbo.USER_DETAILS_TEST Values (2,2,'Father2','Mother2','Orange County','Los Angeles','Berkeley','USA'
G
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
G
-- create the stored procedure that accesses the test table
/****** Object: Stored Procedure dbo.GetUserDetailsTest Script Date: 27.11.2002 00:57:27 ******
CREATE PROCEDURE [GetUserDetailsTest
(@.UserId int = Null
@.UserDetailId int = Null
@.ExpansionType tinyint = 0
A
Set NoCount O
If @.ExpansionType = Begi
If @.UserDetailId Is Not Nul
Begi
Select
ud.USER_DETAIL_ID
ud.USER_ID
ud.FATHERS_NAME
ud.MOTHERS_NAME
ud.PLACE_OF_BIRTH
ud.CITY
ud.TOWN
ud.COUNTR
From dbo.USER_DETAILS_TEST u
Where ud.USER_DETAIL_ID = @.UserDetailI
Return
En
If @.UserId Is Not Nul
Begi
Select
ud.USER_DETAIL_ID
ud.USER_ID
ud.FATHERS_NAME
ud.MOTHERS_NAME
ud.PLACE_OF_BIRTH
ud.CITY
ud.TOWN
ud.COUNTR
From dbo.USER_DETAILS_TEST u
Where ud.USER_ID = @.UserI
Return
En
En
If @.ExpansionType = Begi
If @.UserId Is Not Nul
Begi
Select
u.USER_ID
u.[NAME]
u.[SURNAME]
ud.USER_DETAIL_ID
ud.FATHERS_NAME
ud.MOTHERS_NAME
ud.PLACE_OF_BIRTH
ud.CITY
ud.TOWN
ud.COUNTR
From dbo.USER_DETAILS_TEST u
Inner Join dbo.USERS_TEST u On ud.USER_ID = u.USER_I
Where u.USER_ID = @.UserI
Return
En
En
G
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
G
-- use SET FMTONLY to return metadata, HERE's WHERE THE BUG OCCURS
-- the procedure is called as if @.ExpansionType parameter is set to
set fmtonly on exec GetUserDetailsTest @.UserId=1,@.ExpansionType=1 set fmtonly of
exec GetUserDetailsTest @.UserId=1,@.ExpansionType=Ignore this if you are posting from the MSDN managed groups:
This is not a formal bug reporting forum. This is a peer to peer forum,. To
report a bug formally, open a case with MS. You will not be charged if it is
a bug.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bulent Biyikoglu, MCSD" <bulent@.e-kampus.com> wrote in message
news:DDE055D6-D61D-49AF-B6F9-7615EA12E9A6@.microsoft.com...
> Hello,
> I have reported this bug previosly, but Microsoft has so far not responded
yet, so I am reporting it again. Basicly, SET FMTONLY is NOT working
correctly with SQL Server 2000. Because of this bug I still have to use SQL
Server 7.0 for my campuswide student information system application,
although I really would like to upgrade to SQL Server 2000 :-(
> Here's the script to reproduce the bug:
> -- create the test tables, and populate them
> Create Table dbo.USERS_TEST
> ([USER_ID] int PRIMARY KEY not null,
> [NAME] varchar(20) null,
> [SURNAME] varchar(20) null)
> GO
> Set NoCount On
> Insert Into dbo.USERS_TEST Values (1,'Bulent','Biyikoglu')
> Insert Into dbo.USERS_TEST Values (2,'Jane','Doe')
> GO
> Create Table dbo.USER_DETAILS_TEST
> ([USER_DETAIL_ID] int PRIMARY KEY not null,
> [USER_ID] int NOT NULL FOREIGN KEY REFERENCES dbo.USERS_TEST([USER_ID]),
> FATHERS_NAME varchar(20) null,
> MOTHERS_NAME varchar(20) null,
> PLACE_OF_BIRTH varchar(40) null,
> CITY varchar(20) null,
> TOWN varchar(20) null,
> COUNTRY varchar(20) null)
> GO
> Set NoCount On
> Insert Into dbo.USER_DETAILS_TEST Values
(1,1,'Father1','Mother1','Polatli','Palo Alto','Stanford','Turkey')
> Insert Into dbo.USER_DETAILS_TEST Values (2,2,'Father2','Mother2','Orange
County','Los Angeles','Berkeley','USA')
> GO
> SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
> GO
> -- create the stored procedure that accesses the test tables
> /****** Object: Stored Procedure dbo.GetUserDetailsTest Script Date:
27.11.2002 00:57:27 ******/
> CREATE PROCEDURE [GetUserDetailsTest]
> (@.UserId int = Null,
> @.UserDetailId int = Null,
> @.ExpansionType tinyint = 0)
> As
> Set NoCount On
> If @.ExpansionType = 0
> Begin
> If @.UserDetailId Is Not Null
> Begin
> Select
> ud.USER_DETAIL_ID,
> ud.USER_ID,
> ud.FATHERS_NAME,
> ud.MOTHERS_NAME,
> ud.PLACE_OF_BIRTH,
> ud.CITY,
> ud.TOWN,
> ud.COUNTRY
> From dbo.USER_DETAILS_TEST ud
> Where ud.USER_DETAIL_ID = @.UserDetailId
> Return 0
> End
> If @.UserId Is Not Null
> Begin
> Select
> ud.USER_DETAIL_ID,
> ud.USER_ID,
> ud.FATHERS_NAME,
> ud.MOTHERS_NAME,
> ud.PLACE_OF_BIRTH,
> ud.CITY,
> ud.TOWN,
> ud.COUNTRY
> From dbo.USER_DETAILS_TEST ud
> Where ud.USER_ID = @.UserId
> Return 0
> End
> End
> If @.ExpansionType = 1
> Begin
> If @.UserId Is Not Null
> Begin
> Select
> u.USER_ID,
> u.[NAME],
> u.[SURNAME],
> ud.USER_DETAIL_ID,
> ud.FATHERS_NAME,
> ud.MOTHERS_NAME,
> ud.PLACE_OF_BIRTH,
> ud.CITY,
> ud.TOWN,
> ud.COUNTRY
> From dbo.USER_DETAILS_TEST ud
> Inner Join dbo.USERS_TEST u On ud.USER_ID = u.USER_ID
> Where u.USER_ID = @.UserId
> Return 0
> End
> End
> GO
> SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
> GO
> -- use SET FMTONLY to return metadata, HERE's WHERE THE BUG OCCURS:
> -- the procedure is called as if @.ExpansionType parameter is set to 0
> set fmtonly on exec GetUserDetailsTest @.UserId=1,@.ExpansionType=1 set
fmtonly off
> exec GetUserDetailsTest @.UserId=1,@.ExpansionType=1
>|||Bulent Biyikoglu, MCSD wrote:
> Hello,
> I have reported this bug previosly, but Microsoft has so far not
> responded yet, so I am reporting it again. Basicly, SET FMTONLY is
> NOT working correctly with SQL Server 2000. Because of this bug I
> still have to use SQL Server 7.0 for my campuswide student
> information system application, although I really would like to
> upgrade to SQL Server 2000 :-(
> Here's the script to reproduce the bug:
> -- create the test tables, and populate them
> Create Table dbo.USERS_TEST
> ([USER_ID] int PRIMARY KEY not null,
> [NAME] varchar(20) null,
> [SURNAME] varchar(20) null)
> GO
> Set NoCount On
> Insert Into dbo.USERS_TEST Values (1,'Bulent','Biyikoglu')
> Insert Into dbo.USERS_TEST Values (2,'Jane','Doe')
> GO
> Create Table dbo.USER_DETAILS_TEST
> ([USER_DETAIL_ID] int PRIMARY KEY not null,
> [USER_ID] int NOT NULL FOREIGN KEY REFERENCES
> dbo.USERS_TEST([USER_ID]), FATHERS_NAME varchar(20) null,
> MOTHERS_NAME varchar(20) null,
> PLACE_OF_BIRTH varchar(40) null,
> CITY varchar(20) null,
> TOWN varchar(20) null,
> COUNTRY varchar(20) null)
> GO
> Set NoCount On
> Insert Into dbo.USER_DETAILS_TEST Values
> (1,1,'Father1','Mother1','Polatli','Palo Alto','Stanford','Turkey')
> Insert Into dbo.USER_DETAILS_TEST Values
> (2,2,'Father2','Mother2','Orange County','Los
> Angeles','Berkeley','USA') GO
> SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
> GO
> -- create the stored procedure that accesses the test tables
> /****** Object: Stored Procedure dbo.GetUserDetailsTest Script
> Date: 27.11.2002 00:57:27 ******/ CREATE PROCEDURE
> [GetUserDetailsTest] (@.UserId int = Null,
> @.UserDetailId int = Null,
> @.ExpansionType tinyint = 0)
> As
> Set NoCount On
> If @.ExpansionType = 0
> Begin
> If @.UserDetailId Is Not Null
> Begin
> Select
> ud.USER_DETAIL_ID,
> ud.USER_ID,
> ud.FATHERS_NAME,
> ud.MOTHERS_NAME,
> ud.PLACE_OF_BIRTH,
> ud.CITY,
> ud.TOWN,
> ud.COUNTRY
> From dbo.USER_DETAILS_TEST ud
> Where ud.USER_DETAIL_ID = @.UserDetailId
> Return 0
> End
> If @.UserId Is Not Null
> Begin
> Select
> ud.USER_DETAIL_ID,
> ud.USER_ID,
> ud.FATHERS_NAME,
> ud.MOTHERS_NAME,
> ud.PLACE_OF_BIRTH,
> ud.CITY,
> ud.TOWN,
> ud.COUNTRY
> From dbo.USER_DETAILS_TEST ud
> Where ud.USER_ID = @.UserId
> Return 0
> End
> End
> If @.ExpansionType = 1
> Begin
> If @.UserId Is Not Null
> Begin
> Select
> u.USER_ID,
> u.[NAME],
> u.[SURNAME],
> ud.USER_DETAIL_ID,
> ud.FATHERS_NAME,
> ud.MOTHERS_NAME,
> ud.PLACE_OF_BIRTH,
> ud.CITY,
> ud.TOWN,
> ud.COUNTRY
> From dbo.USER_DETAILS_TEST ud
> Inner Join dbo.USERS_TEST u On ud.USER_ID = u.USER_ID
> Where u.USER_ID = @.UserId
> Return 0
> End
> End
> GO
> SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
> GO
> -- use SET FMTONLY to return metadata, HERE's WHERE THE BUG OCCURS:
> -- the procedure is called as if @.ExpansionType parameter is set to 0
> set fmtonly on exec GetUserDetailsTest @.UserId=1,@.ExpansionType=1 set
> fmtonly off
> exec GetUserDetailsTest @.UserId=1,@.ExpansionType=1
/*Hi Bulent, while I am certainly not going to dispute that the
behaviour using this command in SQL 7 does do what you expect (since I
cant test it against 7 at the mo), I think this behaviour in 2000 is by
design.
As you know the SET FMTONLY ON setting is telling SQL to return only
the metadata of a result set. It appears that all statements that do
not return a result set and metadata are processed only but then
ignored (if that makes sense! since you have effectively told SQL that
you are only interested in the metadata) INCLUDING logic statements.
See the following*/
USE Northwind
SET NOCOUNT OFF
DECLARE @.myInt TINYINT
SET @.myInt=1
--SET FMTONLY ON
PRINT 'Display this text from print. You will see it does not print'
SELECT 'Display this text from select. You will see it is processed BUT
the result set contains no metadata'
SELECT @.myInt=2 --You will see below (from print) that this select is
processed
SET FMTONLY OFF PRINT @.myInt SET FMTONLY ON
SET @.myInt=3 --You will see below (from print) that this set is
processed
SET FMTONLY OFF PRINT @.myInt SET FMTONLY ON
select * from northwind..orders
select * from northwind..[order Details]
IF @.myInt=1 --is skipped
select * from northwind..orders --meta is returned
ELSE --is skipped
select * from northwind..[order Details] --meta is returned
ENDIF
EXEC SP_HELP --Stored procedure with multiple Selects including
variable assignments all metadata selects are returned
SELECT * FROM Northwind..Invoices --This is a view, metadata is returned
SET FMTONLY OFF
Br,
Mark Broadbent
mcdba , mcse+i
=============sql

BUG: Reporting Services Custom Security and Subscriptions?

Basically we have implemented custom security so that our logins to the
Report Server are using details in a custom database. They are not Windows
usernames/passwords. However when we use the web service api
(ListSubscriptions) to return user's subscriptions we get nothing? After
digging further with Reflector I found the following code in an RS assembly:
public ArrayList ListSubscriptions(string user, string report)
{
ArrayList list1 = new ArrayList();
string text1 = "select
S.[SubscriptionID],
S.[Report_OID],
S.[Locale],
S.[InactiveFlags],
S.[DeliveryExtension],
S.[ExtensionSettings],
SUSER_SNAME(Modified.[Sid]),
Modified.[UserName],
S.[ModifiedDate],
S.[Description],
S.[LastStatus],
S.[EventType],
S.[MatchData],
S.[Parameters],
S.[DataSettings],
A.[TotalNotifications],
A.[TotalSuccesses],
A.[TotalFailures],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
CAT.[Path],
S.[LastRunTime],
CAT.[Type],
SD.NtSecDescPrimary
from
[Subscriptions] S inner join [Catalog] CAT on S.[Report_OID] = CAT.[ItemID]
inner join [Users] Owner on S.OwnerID = Owner.UserID
inner join [Users] Modified on S.ModifiedByID = Modified.UserID
left outer join [SecData] SD on CAT.[PolicyID] = SD.[PolicyID]
AND SD.AuthType = @.AuthType
left outer join [ActiveSubscriptions] A with (NOLOCK) on
S.[SubscriptionID] = A.[SubscriptionID]";
InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
CommandType.Text, base.Connection, base.Transaction,
base.SqlCommandTimeout);
command1.Parameters.Add("@.AuthType",
WebConfigUtil.AuthenticationType);
if ((user != null) && (user != ""))
{
this.AddClause(ref text1, ref flag1, "Owner.[Sid] = @.UserSid");
command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
}
<removed code>
}
Now the interesting bit is:
this.AddClause(ref text1, ref flag1, "Owner.[Sid] = @.UserSid");
command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
Why are they trying to lookup a SID for my custom username? Of course this
fails, which results in a WHERE clause that fails to grabs our
subscriptions.
Has anyone else found this? Am I completely wrong?
BTW: I'm using Reporting Services SP 2.
James Snape (for Ryan Stevens)Hello James,
Based on my scope, if a report server user is not mapped to a Windows user
sid, it shall be NULL. I think this shall work properly in SQL query.
select * from users where sid=NULL
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| Subject: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Fri, 7 Oct 2005 09:25:13 +0100
| Lines: 75
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net 213.92.131.1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:53918
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Basically we have implemented custom security so that our logins to the
| Report Server are using details in a custom database. They are not
Windows
| usernames/passwords. However when we use the web service api
| (ListSubscriptions) to return user's subscriptions we get nothing? After
| digging further with Reflector I found the following code in an RS
assembly:
|
| public ArrayList ListSubscriptions(string user, string report)
| {
| ArrayList list1 = new ArrayList();
| string text1 = "select
| S.[SubscriptionID],
| S.[Report_OID],
| S.[Locale],
| S.[InactiveFlags],
| S.[DeliveryExtension],
| S.[ExtensionSettings],
| SUSER_SNAME(Modified.[Sid]),
| Modified.[UserName],
| S.[ModifiedDate],
| S.[Description],
| S.[LastStatus],
| S.[EventType],
| S.[MatchData],
| S.[Parameters],
| S.[DataSettings],
| A.[TotalNotifications],
| A.[TotalSuccesses],
| A.[TotalFailures],
| SUSER_SNAME(Owner.[Sid]),
| Owner.[UserName],
| CAT.[Path],
| S.[LastRunTime],
| CAT.[Type],
| SD.NtSecDescPrimary
| from
| [Subscriptions] S inner join [Catalog] CAT on S.[Report_OID]
=| CAT.[ItemID]
| inner join [Users] Owner on S.OwnerID = Owner.UserID
| inner join [Users] Modified on S.ModifiedByID =Modified.UserID
| left outer join [SecData] SD on CAT.[PolicyID] =SD.[PolicyID]
| AND SD.AuthType = @.AuthType
| left outer join [ActiveSubscriptions] A with (NOLOCK) on
| S.[SubscriptionID] = A.[SubscriptionID]";
|
| InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
| CommandType.Text, base.Connection, base.Transaction,
| base.SqlCommandTimeout);
| command1.Parameters.Add("@.AuthType",
| WebConfigUtil.AuthenticationType);
| if ((user != null) && (user != ""))
| {
| this.AddClause(ref text1, ref flag1, "Owner.[Sid] =@.UserSid");
| command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
| }
|
| <removed code>
| }
|
| Now the interesting bit is:
|
| this.AddClause(ref text1, ref flag1, "Owner.[Sid] =@.UserSid");
| command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
|
|
| Why are they trying to lookup a SID for my custom username? Of course
this
| fails, which results in a WHERE clause that fails to grabs our
| subscriptions.
|
| Has anyone else found this? Am I completely wrong?
|
| BTW: I'm using Reporting Services SP 2.
|
| James Snape (for Ryan Stevens)
|
|
||||Hi Peter,
A comparison against NULL is always false so your query below never returns
any results.
Regards,
James Snape
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> Hello James,
> Based on my scope, if a report server user is not mapped to a Windows user
> sid, it shall be NULL. I think this shall work properly in SQL query.
> select * from users where sid=NULL
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
> | Subject: BUG: Reporting Services Custom Security and Subscriptions?
> | Date: Fri, 7 Oct 2005 09:25:13 +0100
> | Lines: 75
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
> 213.92.131.1
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:53918
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Basically we have implemented custom security so that our logins to the
> | Report Server are using details in a custom database. They are not
> Windows
> | usernames/passwords. However when we use the web service api
> | (ListSubscriptions) to return user's subscriptions we get nothing? After
> | digging further with Reflector I found the following code in an RS
> assembly:
> |
> | public ArrayList ListSubscriptions(string user, string report)
> | {
> | ArrayList list1 = new ArrayList();
> | string text1 = "select
> | S.[SubscriptionID],
> | S.[Report_OID],
> | S.[Locale],
> | S.[InactiveFlags],
> | S.[DeliveryExtension],
> | S.[ExtensionSettings],
> | SUSER_SNAME(Modified.[Sid]),
> | Modified.[UserName],
> | S.[ModifiedDate],
> | S.[Description],
> | S.[LastStatus],
> | S.[EventType],
> | S.[MatchData],
> | S.[Parameters],
> | S.[DataSettings],
> | A.[TotalNotifications],
> | A.[TotalSuccesses],
> | A.[TotalFailures],
> | SUSER_SNAME(Owner.[Sid]),
> | Owner.[UserName],
> | CAT.[Path],
> | S.[LastRunTime],
> | CAT.[Type],
> | SD.NtSecDescPrimary
> | from
> | [Subscriptions] S inner join [Catalog] CAT on
> S.[Report_OID]
> => | CAT.[ItemID]
> | inner join [Users] Owner on S.OwnerID = Owner.UserID
> | inner join [Users] Modified on S.ModifiedByID => Modified.UserID
> | left outer join [SecData] SD on CAT.[PolicyID] => SD.[PolicyID]
> | AND SD.AuthType = @.AuthType
> | left outer join [ActiveSubscriptions] A with (NOLOCK) on
> | S.[SubscriptionID] = A.[SubscriptionID]";
> |
> | InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
> | CommandType.Text, base.Connection, base.Transaction,
> | base.SqlCommandTimeout);
> | command1.Parameters.Add("@.AuthType",
> | WebConfigUtil.AuthenticationType);
> | if ((user != null) && (user != ""))
> | {
> | this.AddClause(ref text1, ref flag1, "Owner.[Sid] => @.UserSid");
> | command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
> | }
> |
> | <removed code>
> | }
> |
> | Now the interesting bit is:
> |
> | this.AddClause(ref text1, ref flag1, "Owner.[Sid] => @.UserSid");
> | command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
> |
> |
> | Why are they trying to lookup a SID for my custom username? Of course
> this
> | fails, which results in a WHERE clause that fails to grabs our
> | subscriptions.
> |
> | Has anyone else found this? Am I completely wrong?
> |
> | BTW: I'm using Reporting Services SP 2.
> |
> | James Snape (for Ryan Stevens)
> |
> |
> |
>|||Hello James,
If ANSI_NULLS is off, it shall return all users with NULL SID. I have
reported this issue to the proper channel but there is no feedback yet. If
we have any update on this, we will let you know.
Also, since the issue relates to source code evaluation, I recommend that
you open a Support incident with Microsoft Product Support Services so that
a dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| References: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
<te#MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl>
| Subject: Re: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Fri, 7 Oct 2005 12:23:31 +0100
| Lines: 140
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net 213.92.131.1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:53926
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi Peter,
|
| A comparison against NULL is always false so your query below never
returns
| any results.
|
| Regards,
| James Snape
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
| > Hello James,
| >
| > Based on my scope, if a report server user is not mapped to a Windows
user
| > sid, it shall be NULL. I think this shall work properly in SQL query.
| >
| > select * from users where sid=NULL
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| > --
| > | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| > | Subject: BUG: Reporting Services Custom Security and Subscriptions?
| > | Date: Fri, 7 Oct 2005 09:25:13 +0100
| > | Lines: 75
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
| > 213.92.131.1
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:53918
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Basically we have implemented custom security so that our logins to
the
| > | Report Server are using details in a custom database. They are not
| > Windows
| > | usernames/passwords. However when we use the web service api
| > | (ListSubscriptions) to return user's subscriptions we get nothing?
After
| > | digging further with Reflector I found the following code in an RS
| > assembly:
| > |
| > | public ArrayList ListSubscriptions(string user, string report)
| > | {
| > | ArrayList list1 = new ArrayList();
| > | string text1 = "select
| > | S.[SubscriptionID],
| > | S.[Report_OID],
| > | S.[Locale],
| > | S.[InactiveFlags],
| > | S.[DeliveryExtension],
| > | S.[ExtensionSettings],
| > | SUSER_SNAME(Modified.[Sid]),
| > | Modified.[UserName],
| > | S.[ModifiedDate],
| > | S.[Description],
| > | S.[LastStatus],
| > | S.[EventType],
| > | S.[MatchData],
| > | S.[Parameters],
| > | S.[DataSettings],
| > | A.[TotalNotifications],
| > | A.[TotalSuccesses],
| > | A.[TotalFailures],
| > | SUSER_SNAME(Owner.[Sid]),
| > | Owner.[UserName],
| > | CAT.[Path],
| > | S.[LastRunTime],
| > | CAT.[Type],
| > | SD.NtSecDescPrimary
| > | from
| > | [Subscriptions] S inner join [Catalog] CAT on
| > S.[Report_OID]
| > =| > | CAT.[ItemID]
| > | inner join [Users] Owner on S.OwnerID = Owner.UserID
| > | inner join [Users] Modified on S.ModifiedByID =| > Modified.UserID
| > | left outer join [SecData] SD on CAT.[PolicyID] =| > SD.[PolicyID]
| > | AND SD.AuthType = @.AuthType
| > | left outer join [ActiveSubscriptions] A with (NOLOCK) on
| > | S.[SubscriptionID] = A.[SubscriptionID]";
| > |
| > | InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
| > | CommandType.Text, base.Connection, base.Transaction,
| > | base.SqlCommandTimeout);
| > | command1.Parameters.Add("@.AuthType",
| > | WebConfigUtil.AuthenticationType);
| > | if ((user != null) && (user != ""))
| > | {
| > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =| > @.UserSid");
| > | command1.Parameters.Add("@.UserSid",
Native.NameToSid(user));
| > | }
| > |
| > | <removed code>
| > | }
| > |
| > | Now the interesting bit is:
| > |
| > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =| > @.UserSid");
| > | command1.Parameters.Add("@.UserSid",
Native.NameToSid(user));
| > |
| > |
| > | Why are they trying to lookup a SID for my custom username? Of course
| > this
| > | fails, which results in a WHERE clause that fails to grabs our
| > | subscriptions.
| > |
| > | Has anyone else found this? Am I completely wrong?
| > |
| > | BTW: I'm using Reporting Services SP 2.
| > |
| > | James Snape (for Ryan Stevens)
| > |
| > |
| > |
| >
|
|
||||It's OK Peter, this issue is not troubling us because the workaround we are
using is to list all subscriptions and filter in our app server. Not great
but it works. I just thought you would like to know of the bug for inclusion
in your next SP.
Regards,
James Snape
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl...
> Hello James,
> If ANSI_NULLS is off, it shall return all users with NULL SID. I have
> reported this issue to the proper channel but there is no feedback yet. If
> we have any update on this, we will let you know.
> Also, since the issue relates to source code evaluation, I recommend that
> you open a Support incident with Microsoft Product Support Services so
> that
> a dedicated Support Professional can assist with this case. If you need
> any
> help in this regard, please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
> | References: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
> <te#MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl>
> | Subject: Re: BUG: Reporting Services Custom Security and Subscriptions?
> | Date: Fri, 7 Oct 2005 12:23:31 +0100
> | Lines: 140
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | X-RFC2646: Format=Flowed; Original
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | Message-ID: <uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
> 213.92.131.1
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:53926
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi Peter,
> |
> | A comparison against NULL is always false so your query below never
> returns
> | any results.
> |
> | Regards,
> | James Snape
> |
> | "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> | > Hello James,
> | >
> | > Based on my scope, if a report server user is not mapped to a Windows
> user
> | > sid, it shall be NULL. I think this shall work properly in SQL query.
> | >
> | > select * from users where sid=NULL
> | >
> | > Regards,
> | >
> | > Peter Yang
> | > MCSE2000/2003, MCSA, MCDBA
> | > Microsoft Online Partner Support
> | >
> | > When responding to posts, please "Reply to Group" via your newsreader
> so
> | > that others may learn and benefit from your issue.
> | >
> | > =====================================================> | >
> | >
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> | > rights.
> | >
> | > --
> | > | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
> | > | Subject: BUG: Reporting Services Custom Security and Subscriptions?
> | > | Date: Fri, 7 Oct 2005 09:25:13 +0100
> | > | Lines: 75
> | > | X-Priority: 3
> | > | X-MSMail-Priority: Normal
> | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | > | X-RFC2646: Format=Flowed; Original
> | > | Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
> | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
> | > 213.92.131.1
> | > | Path:
> TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | > | Xref: TK2MSFTNGXA01.phx.gbl
> | > microsoft.public.sqlserver.reportingsvcs:53918
> | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > |
> | > | Basically we have implemented custom security so that our logins to
> the
> | > | Report Server are using details in a custom database. They are not
> | > Windows
> | > | usernames/passwords. However when we use the web service api
> | > | (ListSubscriptions) to return user's subscriptions we get nothing?
> After
> | > | digging further with Reflector I found the following code in an RS
> | > assembly:
> | > |
> | > | public ArrayList ListSubscriptions(string user, string report)
> | > | {
> | > | ArrayList list1 = new ArrayList();
> | > | string text1 = "select
> | > | S.[SubscriptionID],
> | > | S.[Report_OID],
> | > | S.[Locale],
> | > | S.[InactiveFlags],
> | > | S.[DeliveryExtension],
> | > | S.[ExtensionSettings],
> | > | SUSER_SNAME(Modified.[Sid]),
> | > | Modified.[UserName],
> | > | S.[ModifiedDate],
> | > | S.[Description],
> | > | S.[LastStatus],
> | > | S.[EventType],
> | > | S.[MatchData],
> | > | S.[Parameters],
> | > | S.[DataSettings],
> | > | A.[TotalNotifications],
> | > | A.[TotalSuccesses],
> | > | A.[TotalFailures],
> | > | SUSER_SNAME(Owner.[Sid]),
> | > | Owner.[UserName],
> | > | CAT.[Path],
> | > | S.[LastRunTime],
> | > | CAT.[Type],
> | > | SD.NtSecDescPrimary
> | > | from
> | > | [Subscriptions] S inner join [Catalog] CAT on
> | > S.[Report_OID]
> | > => | > | CAT.[ItemID]
> | > | inner join [Users] Owner on S.OwnerID = Owner.UserID
> | > | inner join [Users] Modified on S.ModifiedByID => | > Modified.UserID
> | > | left outer join [SecData] SD on CAT.[PolicyID] => | > SD.[PolicyID]
> | > | AND SD.AuthType = @.AuthType
> | > | left outer join [ActiveSubscriptions] A with (NOLOCK)
> on
> | > | S.[SubscriptionID] = A.[SubscriptionID]";
> | > |
> | > | InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
> | > | CommandType.Text, base.Connection, base.Transaction,
> | > | base.SqlCommandTimeout);
> | > | command1.Parameters.Add("@.AuthType",
> | > | WebConfigUtil.AuthenticationType);
> | > | if ((user != null) && (user != ""))
> | > | {
> | > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] => | > @.UserSid");
> | > | command1.Parameters.Add("@.UserSid",
> Native.NameToSid(user));
> | > | }
> | > |
> | > | <removed code>
> | > | }
> | > |
> | > | Now the interesting bit is:
> | > |
> | > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] => | > @.UserSid");
> | > | command1.Parameters.Add("@.UserSid",
> Native.NameToSid(user));
> | > |
> | > |
> | > | Why are they trying to lookup a SID for my custom username? Of
> course
> | > this
> | > | fails, which results in a WHERE clause that fails to grabs our
> | > | subscriptions.
> | > |
> | > | Has anyone else found this? Am I completely wrong?
> | > |
> | > | BTW: I'm using Reporting Services SP 2.
> | > |
> | > | James Snape (for Ryan Stevens)
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>|||Hello James,
Please rest assured that your feedback on this issue is routed to the
proper channel. Again, thank you for taking time to report this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| References: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
<te#MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl>
<uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
<r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl>
| Subject: Re: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Tue, 11 Oct 2005 15:54:54 +0100
| Lines: 219
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <upkv$OnzFHA.1264@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: exony-ltd-02.altohiway.com 213.83.66.226
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp1
3.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:61002
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| It's OK Peter, this issue is not troubling us because the workaround we
are
| using is to list all subscriptions and filter in our app server. Not
great
| but it works. I just thought you would like to know of the bug for
inclusion
| in your next SP.
|
| Regards,
| James Snape
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl...
| > Hello James,
| >
| > If ANSI_NULLS is off, it shall return all users with NULL SID. I have
| > reported this issue to the proper channel but there is no feedback yet.
If
| > we have any update on this, we will let you know.
| >
| > Also, since the issue relates to source code evaluation, I recommend
that
| > you open a Support incident with Microsoft Product Support Services so
| > that
| > a dedicated Support Professional can assist with this case. If you need
| > any
| > help in this regard, please let me know.
| >
| > For a complete list of Microsoft Product Support Services phone numbers,
| > please go to the following address on the World Wide Web:
| > http://support.microsoft.com/directory/overview.asp
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| >
| > --
| > | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| > | References: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
| > <te#MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl>
| > | Subject: Re: BUG: Reporting Services Custom Security and
Subscriptions?
| > | Date: Fri, 7 Oct 2005 12:23:31 +0100
| > | Lines: 140
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | Message-ID: <uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
| > 213.92.131.1
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:53926
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Hi Peter,
| > |
| > | A comparison against NULL is always false so your query below never
| > returns
| > | any results.
| > |
| > | Regards,
| > | James Snape
| > |
| > | "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > | news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
| > | > Hello James,
| > | >
| > | > Based on my scope, if a report server user is not mapped to a
Windows
| > user
| > | > sid, it shall be NULL. I think this shall work properly in SQL
query.
| > | >
| > | > select * from users where sid=NULL
| > | >
| > | > Regards,
| > | >
| > | > Peter Yang
| > | > MCSE2000/2003, MCSA, MCDBA
| > | > Microsoft Online Partner Support
| > | >
| > | > When responding to posts, please "Reply to Group" via your
newsreader
| > so
| > | > that others may learn and benefit from your issue.
| > | >
| > | > =====================================================| > | >
| > | >
| > | >
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > | > rights.
| > | >
| > | > --
| > | > | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| > | > | Subject: BUG: Reporting Services Custom Security and
Subscriptions?
| > | > | Date: Fri, 7 Oct 2005 09:25:13 +0100
| > | > | Lines: 75
| > | > | X-Priority: 3
| > | > | X-MSMail-Priority: Normal
| > | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | > | X-RFC2646: Format=Flowed; Original
| > | > | Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
| > | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
| > | > 213.92.131.1
| > | > | Path:
| > TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | > | Xref: TK2MSFTNGXA01.phx.gbl
| > | > microsoft.public.sqlserver.reportingsvcs:53918
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > | > |
| > | > | Basically we have implemented custom security so that our logins
to
| > the
| > | > | Report Server are using details in a custom database. They are not
| > | > Windows
| > | > | usernames/passwords. However when we use the web service api
| > | > | (ListSubscriptions) to return user's subscriptions we get nothing?
| > After
| > | > | digging further with Reflector I found the following code in an RS
| > | > assembly:
| > | > |
| > | > | public ArrayList ListSubscriptions(string user, string report)
| > | > | {
| > | > | ArrayList list1 = new ArrayList();
| > | > | string text1 = "select
| > | > | S.[SubscriptionID],
| > | > | S.[Report_OID],
| > | > | S.[Locale],
| > | > | S.[InactiveFlags],
| > | > | S.[DeliveryExtension],
| > | > | S.[ExtensionSettings],
| > | > | SUSER_SNAME(Modified.[Sid]),
| > | > | Modified.[UserName],
| > | > | S.[ModifiedDate],
| > | > | S.[Description],
| > | > | S.[LastStatus],
| > | > | S.[EventType],
| > | > | S.[MatchData],
| > | > | S.[Parameters],
| > | > | S.[DataSettings],
| > | > | A.[TotalNotifications],
| > | > | A.[TotalSuccesses],
| > | > | A.[TotalFailures],
| > | > | SUSER_SNAME(Owner.[Sid]),
| > | > | Owner.[UserName],
| > | > | CAT.[Path],
| > | > | S.[LastRunTime],
| > | > | CAT.[Type],
| > | > | SD.NtSecDescPrimary
| > | > | from
| > | > | [Subscriptions] S inner join [Catalog] CAT on
| > | > S.[Report_OID]
| > | > =| > | > | CAT.[ItemID]
| > | > | inner join [Users] Owner on S.OwnerID = Owner.UserID
| > | > | inner join [Users] Modified on S.ModifiedByID =| > | > Modified.UserID
| > | > | left outer join [SecData] SD on CAT.[PolicyID] =| > | > SD.[PolicyID]
| > | > | AND SD.AuthType = @.AuthType
| > | > | left outer join [ActiveSubscriptions] A with
(NOLOCK)
| > on
| > | > | S.[SubscriptionID] = A.[SubscriptionID]";
| > | > |
| > | > | InstrumentedSqlCommand command1 =Storage.NewSqlCommand(text1,
| > | > | CommandType.Text, base.Connection, base.Transaction,
| > | > | base.SqlCommandTimeout);
| > | > | command1.Parameters.Add("@.AuthType",
| > | > | WebConfigUtil.AuthenticationType);
| > | > | if ((user != null) && (user != ""))
| > | > | {
| > | > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =| > | > @.UserSid");
| > | > | command1.Parameters.Add("@.UserSid",
| > Native.NameToSid(user));
| > | > | }
| > | > |
| > | > | <removed code>
| > | > | }
| > | > |
| > | > | Now the interesting bit is:
| > | > |
| > | > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =| > | > @.UserSid");
| > | > | command1.Parameters.Add("@.UserSid",
| > Native.NameToSid(user));
| > | > |
| > | > |
| > | > | Why are they trying to lookup a SID for my custom username? Of
| > course
| > | > this
| > | > | fails, which results in a WHERE clause that fails to grabs our
| > | > | subscriptions.
| > | > |
| > | > | Has anyone else found this? Am I completely wrong?
| > | > |
| > | > | BTW: I'm using Reporting Services SP 2.
| > | > |
| > | > | James Snape (for Ryan Stevens)
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|

BUG: Cannot create subscription as NT AUTHORITY\NETWORK SERVICE

Platform:
Windows 2003 Server Enterprise Edition
SQL Server 2000 Developer Edition SP 3
Reporting Services 2000 Developer Edition SP 2
Hi,
I believe I have encountered a bug in Reporting Services and SQL Server.
To reproduce:
Set the Reporting Service Web Service to run as NT AUTHORITY\NETWORK SERVICE
(I believe this is the default on Windows 2003 server as opposed to the
local ASPNET account) using rsconfig.exe, et al.
Make sure NT AUTHORITY\NETWORK SERVICE has RSExecRole privilege on the
Report Server database, Report Server temp database, master, and msdb.
Then try and create a report subscription. I receive this error:
An internal error occurred on the report server. See the error log for more
details. (rsInternalError) The specified '@.owner_login_name' is invalid
(valid values are returned by sp_helplogins[excluding Windows NT groups]).
My guess is that when the Report Server tries to create a subscription it
does so by invoking sp_add_job/sp_verify_job, this then fails because NT
AUTHORITY\NETWORK SERVICE is a Windows Group. I believe the actual error
above is thrown by the stored procedure, sp_verify_job:
Server: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 198
The specified '@.owner_login_name' is invalid (valid values are returned by
sp_helplogins [excluding Windows NT groups]).
or
From sp_verify_job:
-- Now just check that the login id is valid (ie. it exists and isn't an NT
group)
IF (@.owner_sid IS NULL) OR (EXISTS (SELECT *
FROM master.dbo.syslogins
WHERE (sid = @.owner_sid)
AND (isntgroup <> 0)))
BEGIN
-- NOTE: In the following message we quote @.owner_login_name instead of
@.owner_sid
-- since this is the parameter the user passed to the calling SP
(ie. either
-- sp_add_job or sp_update_job)
SELECT @.res_valid_range = FORMATMESSAGE(14203)
RAISERROR(14234, -1, -1, '@.owner_login_name', @.res_valid_range)
RETURN(1) -- Failure
END
Thanks,
Bryan"BDB" <bdb@.reply.to.group.com> wrote in message
news:OJi2X7QnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> Platform:
> Windows 2003 Server Enterprise Edition
> SQL Server 2000 Developer Edition SP 3
> Reporting Services 2000 Developer Edition SP 2
> Hi,
> I believe I have encountered a bug in Reporting Services and SQL Server.
> To reproduce:
> Set the Reporting Service Web Service to run as NT AUTHORITY\NETWORK
> SERVICE (I believe this is the default on Windows 2003 server as opposed
> to the local ASPNET account) using rsconfig.exe, et al.
> Make sure NT AUTHORITY\NETWORK SERVICE has RSExecRole privilege on the
> Report Server database, Report Server temp database, master, and msdb.
> Then try and create a report subscription. I receive this error:
> An internal error occurred on the report server. See the error log for
> more details. (rsInternalError) The specified '@.owner_login_name' is
> invalid (valid values are returned by sp_helplogins[excluding Windows NT
> groups]).
>
> My guess is that when the Report Server tries to create a subscription it
> does so by invoking sp_add_job/sp_verify_job, this then fails because NT
> AUTHORITY\NETWORK SERVICE is a Windows Group. I believe the actual error
> above is thrown by the stored procedure, sp_verify_job:
> Server: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 198
> The specified '@.owner_login_name' is invalid (valid values are returned by
> sp_helplogins [excluding Windows NT groups]).
> or
> From sp_verify_job:
> -- Now just check that the login id is valid (ie. it exists and isn't an
> NT group)
> IF (@.owner_sid IS NULL) OR (EXISTS (SELECT *
> FROM master.dbo.syslogins
> WHERE (sid = @.owner_sid)
> AND (isntgroup <> 0)))
> BEGIN
> -- NOTE: In the following message we quote @.owner_login_name instead of
> @.owner_sid
> -- since this is the parameter the user passed to the calling SP
> (ie. either
> -- sp_add_job or sp_update_job)
> SELECT @.res_valid_range = FORMATMESSAGE(14203)
> RAISERROR(14234, -1, -1, '@.owner_login_name', @.res_valid_range)
> RETURN(1) -- Failure
> END
>
> Thanks,
> Bryan
>
This bug was fixed (hacked, IMO) in SQL Server 2000 SP4. I have no idea if
a KB article was ever published.|||We've applied SP4 for SQL and still have the error. How did you "fix" it?
--
Thanks,
CGW
"BDB" wrote:
> "BDB" <bdb@.reply.to.group.com> wrote in message
> news:OJi2X7QnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> > Platform:
> > Windows 2003 Server Enterprise Edition
> > SQL Server 2000 Developer Edition SP 3
> > Reporting Services 2000 Developer Edition SP 2
> >
> > Hi,
> >
> > I believe I have encountered a bug in Reporting Services and SQL Server.
> >
> > To reproduce:
> >
> > Set the Reporting Service Web Service to run as NT AUTHORITY\NETWORK
> > SERVICE (I believe this is the default on Windows 2003 server as opposed
> > to the local ASPNET account) using rsconfig.exe, et al.
> >
> > Make sure NT AUTHORITY\NETWORK SERVICE has RSExecRole privilege on the
> > Report Server database, Report Server temp database, master, and msdb.
> >
> > Then try and create a report subscription. I receive this error:
> >
> > An internal error occurred on the report server. See the error log for
> > more details. (rsInternalError) The specified '@.owner_login_name' is
> > invalid (valid values are returned by sp_helplogins[excluding Windows NT
> > groups]).
> >
> >
> >
> > My guess is that when the Report Server tries to create a subscription it
> > does so by invoking sp_add_job/sp_verify_job, this then fails because NT
> > AUTHORITY\NETWORK SERVICE is a Windows Group. I believe the actual error
> > above is thrown by the stored procedure, sp_verify_job:
> >
> > Server: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 198
> > The specified '@.owner_login_name' is invalid (valid values are returned by
> > sp_helplogins [excluding Windows NT groups]).
> >
> > or
> >
> > From sp_verify_job:
> >
> > -- Now just check that the login id is valid (ie. it exists and isn't an
> > NT group)
> > IF (@.owner_sid IS NULL) OR (EXISTS (SELECT *
> > FROM master.dbo.syslogins
> > WHERE (sid = @.owner_sid)
> > AND (isntgroup <> 0)))
> > BEGIN
> > -- NOTE: In the following message we quote @.owner_login_name instead of
> > @.owner_sid
> > -- since this is the parameter the user passed to the calling SP
> > (ie. either
> > -- sp_add_job or sp_update_job)
> > SELECT @.res_valid_range = FORMATMESSAGE(14203)
> > RAISERROR(14234, -1, -1, '@.owner_login_name', @.res_valid_range)
> > RETURN(1) -- Failure
> > END
> >
> >
> > Thanks,
> > Bryan
> >
> This bug was fixed (hacked, IMO) in SQL Server 2000 SP4. I have no idea if
> a KB article was ever published.
>
>

Monday, March 19, 2012

Bug report

Hi.

Does any body know how to report a bug about reporting service?

Is there a mail for the developers or something like that?

Thanks.

http://support.microsoft.com/gp/contactbug

Or you can try leaving the bug details here.

Sql 2005 Service Pack 2 has been released.

cheers,

Andrew

|||Thank.

I hoped to find an email for reporting bug.
I didn't find any.

Any way - I heared from others on microsoft forums That there isn't any thing you can do with bugs.

Well - I don't really care, Its there problem.

Just for farther noticed, If you are working with reporting services with hebrew settings there is a bug with the dateTime Picker.
It sometimes select the time in different format (02/20/2007 instead of 20/02/2007) which will crash the report.|||

You can report bugs at http//connect.microsoft.com/. It will go directly into the internal defect tracking system used by the product teams inside MSFT.

Hope that helps!

Sunday, March 11, 2012

Bug in SQL Server Reporting Services?

I've come across what appears to be a bug in SSRS. If after reading this post you feel otherwise, please reply to this thread...

I am trying to use SSRS to print to a Dymo LabelWriter 400 printer. This is a specialized label printer that prints one label to a page. The label paper I am using 2-1/2 inches by 1-1/2 inches. The Dymo printer driver prints portait layout as being the 2-1/2 inches width and 1-1/2 inches height. I assume that is because the paper rolls through the printer that way and labels normally are printed with more width than height.

Other software programs I use to print to this printer handles it properly (Microsoft Word, Microsoft Office Document Imaging, Dymo Label software). However, SSRS seems to assume that portrait layout must mean paper height > width, as there is no explicity setting in SSRS to for page layout. The result is that no matter how I layout my SSRS report (e.g., switch width-height values, use tb-rl to rotate text), SSRS makes the final decision on whether it is portrait or lanscape. And in this case of the Dymo printer, it always sets it to the opposite of what it should be. Changing the printer settings (whether default or whether in print dialog) does not correct it.

Although assuming that portrait layout must mean paper height > width may seem to make logical sense, not every printer works that way; and other software programs (including Microsoft Office) do not make that assumption. It seems that SSRS and/or RDL spec should allow one to explicitly set portrait/landscape setting and not care about width-height ratio. It should be left to the printer and printer driver to decide.

Have other people come across this issue, either with Dymo printer or in other context? Were they able to find a workaround?

I have the same exact issue. Is there any way to force the print orientation (horizontal or vertical) on SSRS?|||In the printers and faxes folder, select properties of tour dymo label printer, select the advance tab. Setup your printer default, label size and orientation and apply. Select the advance tab again in the label properties and select "print directly to printer. This seemed to work for me.

Bug in SQL Server Reporting Services?

I've come across what appears to be a bug in SSRS. If after reading this post you feel otherwise, please reply to this thread...

Have other people come across this issue, either with Dymo printer or in other context? Were they able to find a workaround?

Bug in SQL Server Reporting Services?

I've come across what appears to be a bug in SSRS. If after reading this post you feel otherwise, please reply to this thread...

Have other people come across this issue, either with Dymo printer or in other context? Were they able to find a workaround?

Thursday, March 8, 2012

Bug in NS 2005?

We have rolled out a solution on SQL 2005 and NS for 2005. As we have been looking into reporting notifications from our system I came across that a column that is created out of the box does not make sense.

In the generated table NS<NotificationClassName>Notifications there is a column called SubscriptionID, it is set as nvarchar(255), but in the NS<SubscriptionClassName>Subscriptions the SubscriptionID is BIGINT. Why would then be different when the refer to the same thing?

What we have done for our solution is not read from the NS table directly, since we do have vacuuming enabled, but we trigger all notifications to another table so we can build reports off the data.

It would be more efficient in our queries to have the data as BIGINT, but I guess it was not an intention to use the tables for reporting inside of NS. We can change our table we are dumping into, but we just did not catch this prior to going live.

I believe this is a bug and thought I would throw it out there.

Hi Andy,

I know this doesn't answer your question, but I would suggest that using triggers or any mechanism that depends on the proprietary implementation schema of Notification Services is unwise and dangerous.

Instead, perhaps you could write a custom delivery protocol to do your reporting. This approach would eliminate any dependence on Microsoft's implentation detail that is subject to change without notice.

Marcus

|||I agree that is a solution, but we plan to test everything before upgrading in the future. We are doing it there so we can collect information about the default protocols, SMTP. Our custom protocol would be easy to populate the table, but then we would have to also make a change for SMTP to use our own process instead of what comes out of the box.

Our app was stood up very quickly, no excuse, but we did not have any past experience with the product to know what issues we might have.|||

I believe you can use the Microsoft SMTP delivery protocol alongside a custom protocol. Admittedly, this would be more of a hassle, but it might free you from your MS schema dependency.

|||

In NS 2005, we provide a set of views you should use rather than using tables directly.

For the inventory Tracker sample, the views are:

NS_InventoryTrackerApplication.InventoryTrackerEvents

NS_InventoryTrackerApplication.InventoryTrackerSubscriptions

NS_InventoryTrackerApplication.InventoryTrackerNotifications

These have the correct data types.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

The problem is, we have developed a history of notifications database, so we can go back later to see what kind of volume we have sent and who we sent it to.

Since we assume the vacuuming process will delete out old notifications, we need to move those off to somewhere. Our solution was to add a trigger on the table itself, we felt more comfortable with that than altering the view you created, since we were not sure how an UPDATE to the ADF would affect your trigger. We can test that too I guess, but our table that we buit schema from was based on the notification table, it still seems like a bug that your are changing datatypes between them, or was that by design?

Did we meet when I was out in Redmond in October? Your last name seems familiar.

|||What about SSIS'ing the notifications periodically - before vacuuming?

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com|||

Yes, I think we did meet - if I recall Shyam covered debugging/diagnosing NS applications on the whiteboard.

What do we mean by ‘bug’? Typically, it’s something that impairs intended functionality. Right now, since we don't expect users to work directly against the tables (they are not documented), and since no actual functionality seems to be impaired by this, it seems like an implementation decision. Whether it was the right decision is unknown - there might have been a compelling reason for making this choice. On the face of it, it seems wrong to have done the conversion, but I don’t know the history of this implementation. Now whether we'd change the data type in future – two views on that: 1) if it ain't broken... 2) it would probably be cleaned up if we were to do some work on code that touches this area.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Andy,

Double-check the notification schema specification in your application definition file. Notification Services does not automatically generate a "SubscriptionID" field in the notifications table, so it sounds like a custom field has been defined in your application.

For a list of fields that are generated by NS (some optionally) in the notifications table, see:

http://msdn2.microsoft.com/en-us/library/ms146568.aspx

-Colin

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

My appologies. I did have it defined in my <NotificationClassName> schema fields. It was a slip on our part. We want to track where received notifications and missed the data type error when we created it.

Thank you for the reminder of what to look at. This was not a NS problem, but our own.

|||

Our solution is using MS Speech server as well and we had to build an engine to handle retries to speech if the phone was busy for instance. This engine is logging all call attempts back to the original notification, which we are not relying on NS table, but rather our copy of the data via the trigger.

Just thought I would give you a little more background.

Thanks for the post though.

Bug in NS 2005?

What we have done for our solution is not read from the NS table directly, since we do have vacuuming enabled, but we trigger all notifications to another table so we can build reports off the data.

I believe this is a bug and thought I would throw it out there.

Hi Andy,

Marcus

Our app was stood up very quickly, no excuse, but we did not have any past experience with the product to know what issues we might have.|||

I believe you can use the Microsoft SMTP delivery protocol alongside a custom protocol. Admittedly, this would be more of a hassle, but it might free you from your MS schema dependency.

|||

In NS 2005, we provide a set of views you should use rather than using tables directly.

For the inventory Tracker sample, the views are:

NS_InventoryTrackerApplication.InventoryTrackerEvents

NS_InventoryTrackerApplication.InventoryTrackerSubscriptions

NS_InventoryTrackerApplication.InventoryTrackerNotifications

These have the correct data types.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

The problem is, we have developed a history of notifications database, so we can go back later to see what kind of volume we have sent and who we sent it to.

Did we meet when I was out in Redmond in October? Your last name seems familiar.

|||What about SSIS'ing the notifications periodically - before vacuuming?

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com|||

Yes, I think we did meet - if I recall Shyam covered debugging/diagnosing NS applications on the whiteboard.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Andy,

For a list of fields that are generated by NS (some optionally) in the notifications table, see:

http://msdn2.microsoft.com/en-us/library/ms146568.aspx

-Colin

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you for the reminder of what to look at. This was not a NS problem, but our own.

|||

Just thought I would give you a little more background.

Thanks for the post though.

Bug in NS 2005?

What we have done for our solution is not read from the NS table directly, since we do have vacuuming enabled, but we trigger all notifications to another table so we can build reports off the data.

I believe this is a bug and thought I would throw it out there.

Hi Andy,

Marcus

Our app was stood up very quickly, no excuse, but we did not have any past experience with the product to know what issues we might have.|||

I believe you can use the Microsoft SMTP delivery protocol alongside a custom protocol. Admittedly, this would be more of a hassle, but it might free you from your MS schema dependency.

|||

In NS 2005, we provide a set of views you should use rather than using tables directly.

For the inventory Tracker sample, the views are:

NS_InventoryTrackerApplication.InventoryTrackerEvents

NS_InventoryTrackerApplication.InventoryTrackerSubscriptions

NS_InventoryTrackerApplication.InventoryTrackerNotifications

These have the correct data types.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

The problem is, we have developed a history of notifications database, so we can go back later to see what kind of volume we have sent and who we sent it to.

Did we meet when I was out in Redmond in October? Your last name seems familiar.

|||What about SSIS'ing the notifications periodically - before vacuuming?

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com|||

Yes, I think we did meet - if I recall Shyam covered debugging/diagnosing NS applications on the whiteboard.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Andy,

For a list of fields that are generated by NS (some optionally) in the notifications table, see:

http://msdn2.microsoft.com/en-us/library/ms146568.aspx

-Colin

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you for the reminder of what to look at. This was not a NS problem, but our own.

|||

Just thought I would give you a little more background.

Thanks for the post though.

Saturday, February 25, 2012

Buffer Cache Hit Ration on a Reporting DB

Hi all.
First off, I'm a sys admin, not a DBA.
I'm trying to improve buffer cache hit ratio on a reporting database.
Database is approximately 130GB. Current server is a Proliant ML530 G2,
2-way 3.6Ghz, 8GB RAM. Multiple datafiles spread out across 4 separate
raid 5 arrays. Disk queuing is pretty good. Sometimes we hit a rough
day.
I've read over and over how BCHR needs to be above 90%, optimally
around 98-99%. On the live OLTP database, BCHR is fine with 8GB in the
system. On the reporting DB, it often drops to 80% or below.
My question is, should a reporting database have this large of a
difference in the hit ratio?
I'm working with our developer & DBA to improve the reports, but I also
need to make sure that the hardware is performing as good as it can.
I've got a DL585, 4-core Opteron in the wings with 16GB running Win2K3
x64. We're waiting to make sure SP4 is completely kosher with our
application before upgrading.
Thoughts, ideas? Much appreciated!
-AJ<ajohnson@.echecktrac.com> wrote in message
news:1144442893.581038.299300@.e56g2000cwe.googlegroups.com...
> Hi all.
> First off, I'm a sys admin, not a DBA.
> I'm trying to improve buffer cache hit ratio on a reporting database.
> Database is approximately 130GB. Current server is a Proliant ML530 G2,
> 2-way 3.6Ghz, 8GB RAM. Multiple datafiles spread out across 4 separate
> raid 5 arrays. Disk queuing is pretty good. Sometimes we hit a rough
> day.
> I've read over and over how BCHR needs to be above 90%, optimally
> around 98-99%. On the live OLTP database, BCHR is fine with 8GB in the
> system.
Unfortunatly BCHR on OLTP databases is an irrelevant metric. A high BCHR
often results from inefficient queries reading lots and lots of pages in the
cache. And the memory size/database size ratio of an OLTP system often
doesn't even permit a low cache hit ratio.

>On the reporting DB, it often drops to 80% or below.
> My question is, should a reporting database have this large of a
> difference in the hit ratio?
Yes, it will quite likely be lower. It's a matter of the ratio between the
cache (<8GB) and the data (130GB), and the predictablility of the queries
and acess paths. Better indexing can help improve performance, although
since indexing reduces total IO, not just physical IO, you might not see a
better BCHR.

> I'm working with our developer & DBA to improve the reports, but I also
> need to make sure that the hardware is performing as good as it can.
> I've got a DL585, 4-core Opteron in the wings with 16GB running Win2K3
> x64. We're waiting to make sure SP4 is completely kosher with our
> application before upgrading.
>
Going all the way to SQL 2005 64-bit will help alot more. You can use all
that memory much more efficiently, plus it's easier to monitor the
performance and find the expensive queries.
David

Buffer Cache Hit Ration on a Reporting DB

Hi all.
First off, I'm a sys admin, not a DBA.
I'm trying to improve buffer cache hit ratio on a reporting database.
Database is approximately 130GB. Current server is a Proliant ML530 G2,
2-way 3.6Ghz, 8GB RAM. Multiple datafiles spread out across 4 separate
raid 5 arrays. Disk queuing is pretty good. Sometimes we hit a rough
day.
I've read over and over how BCHR needs to be above 90%, optimally
around 98-99%. On the live OLTP database, BCHR is fine with 8GB in the
system. On the reporting DB, it often drops to 80% or below.
My question is, should a reporting database have this large of a
difference in the hit ratio?
I'm working with our developer & DBA to improve the reports, but I also
need to make sure that the hardware is performing as good as it can.
I've got a DL585, 4-core Opteron in the wings with 16GB running Win2K3
x64. We're waiting to make sure SP4 is completely kosher with our
application before upgrading.
Thoughts, ideas? Much appreciated!
-AJ<ajohnson@.echecktrac.com> wrote in message
news:1144442893.581038.299300@.e56g2000cwe.googlegroups.com...
> Hi all.
> First off, I'm a sys admin, not a DBA.
> I'm trying to improve buffer cache hit ratio on a reporting database.
> Database is approximately 130GB. Current server is a Proliant ML530 G2,
> 2-way 3.6Ghz, 8GB RAM. Multiple datafiles spread out across 4 separate
> raid 5 arrays. Disk queuing is pretty good. Sometimes we hit a rough
> day.
> I've read over and over how BCHR needs to be above 90%, optimally
> around 98-99%. On the live OLTP database, BCHR is fine with 8GB in the
> system.
Unfortunatly BCHR on OLTP databases is an irrelevant metric. A high BCHR
often results from inefficient queries reading lots and lots of pages in the
cache. And the memory size/database size ratio of an OLTP system often
doesn't even permit a low cache hit ratio.
>On the reporting DB, it often drops to 80% or below.
> My question is, should a reporting database have this large of a
> difference in the hit ratio?
Yes, it will quite likely be lower. It's a matter of the ratio between the
cache (<8GB) and the data (130GB), and the predictablility of the queries
and acess paths. Better indexing can help improve performance, although
since indexing reduces total IO, not just physical IO, you might not see a
better BCHR.
> I'm working with our developer & DBA to improve the reports, but I also
> need to make sure that the hardware is performing as good as it can.
> I've got a DL585, 4-core Opteron in the wings with 16GB running Win2K3
> x64. We're waiting to make sure SP4 is completely kosher with our
> application before upgrading.
>
Going all the way to SQL 2005 64-bit will help alot more. You can use all
that memory much more efficiently, plus it's easier to monitor the
performance and find the expensive queries.
David

Friday, February 24, 2012

Browsing a cube in ssrs Matrix

When we browse the cube in management studio we are able to see the correct aggregates where as in the report from reporting services we are just getting the sums instead of running aggregates especially in the intersection cells of columns and rows. ( where ever the aggregrate function is difference/division. )

Is there a setting in reporting services which would make the report return the running aggregates like in the management studio.

thanks in advance

The brower is diferent in Reporting Services, but if you use the MATRIX in reporting Services, you all the cells for columns, rows and data!

Try it... if you never did that, use the wizard!

Helped?

Regards!

|||

PedroCGD wrote:

The brower is diferent in Reporting Services, but if you use the MATRIX in reporting Services, you all the cells for columns, rows and data!

Try it... if you never did that, use the wizard!

Helped?

Regards!

Can you eloborate please.

Even when I use the matrix and the wizard still it dosent give me the running aggegates it is just giving me the sum in the intersections cells.

Any thots ?

Browser Role get Admin Tools... why?

Hi there,
We're trying to set up a Browser Role on a Server for Reporting Services. We
have created the NT User, and then added a Browser role for them in RS. But
for some reason, even though when we access Reports and type in the Browser's
Credentials, all of the Admin Tools are still there and usable.
I've done the exact same thing previously and had no complications like
this. What could be causing this to happen?
Thanks
AndreThe Answer is...
the report web apps in IIS has anom access selected!
"Arkiliknam" wrote:
> Hi there,
> We're trying to set up a Browser Role on a Server for Reporting Services. We
> have created the NT User, and then added a Browser role for them in RS. But
> for some reason, even though when we access Reports and type in the Browser's
> Credentials, all of the Admin Tools are still there and usable.
> I've done the exact same thing previously and had no complications like
> this. What could be causing this to happen?
> Thanks
> Andre

Browser realignment

Howdy all,
I was curious, we have built a reporting services backend for DotNetNuke which displays reports in an IFRAME, but as you drill through the report the browser and iframe realign down to the top of the item clicked, here i show you,... This is before i click on a toggle item,...

Everything seems normal, browser and iframe all at the top,...
Then, as you can see the browser needlessly dives down the page, and the report rolls down as well,

Anyone know of a remedy for this, is there a property which can be set in RS or is it all in my frame configuration??
It is annoying enough for me to scroll back up, so i know the customer wont be jazzed about it either.
I thank in advance anyone for their help,...

Good Morning:

There may be a number of issues (postback's, IIS, etc.) and I have deployed DNN in a similar way to your above example. This "thread" from the DNN forum may lead you in a direction that may help.

http://forums.asp.net/781505/ShowPost.aspx - Persistent Scrolling!
Best Reagrds,
Joe

|||Good Morning:
This link may also help as well - From DNN
http://forums.asp.net/search/SearchResults.aspx?q=IFrame%20&f=OTA=&u=
Best Regards,
Joe

Browser compatibility for Reporting Services

We are considering using Reporting Services along with Dundas charts for our
web-based reporting application. However, we hear that using Reporting
Services will LIMIT us to only I.E. browser. Is this true? Can Reporting
Services be used with other web browsers besides I.E.?
Thank you,
Morgan StricklandJust my 2 cents. I use IE and FireFox. The Report Manager and Report
Server webs and the web service rendering work just fine in IE. It doesn't
work correctly in Firefox 1.0.4. I would be curious to test Mozilla and
some of the others...
--
Adrian M.
MCP
"Morgan Strickland" <Morgan Strickland@.discussions.microsoft.com> wrote in
message news:20A0483F-2BBD-422B-926D-69DF36B26643@.microsoft.com...
> We are considering using Reporting Services along with Dundas charts for
> our
> web-based reporting application. However, we hear that using Reporting
> Services will LIMIT us to only I.E. browser. Is this true? Can Reporting
> Services be used with other web browsers besides I.E.?
> Thank you,
> Morgan Strickland
>|||I just disable the top toolbar with anything but IE.
The actual reports do work for me, but not all the functionality.
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:%23sLt3y$VFHA.3532@.TK2MSFTNGP09.phx.gbl...
> Just my 2 cents. I use IE and FireFox. The Report Manager and Report
> Server webs and the web service rendering work just fine in IE. It
doesn't
> work correctly in Firefox 1.0.4. I would be curious to test Mozilla and
> some of the others...
> --
> Adrian M.
> MCP
>
> "Morgan Strickland" <Morgan Strickland@.discussions.microsoft.com> wrote in
> message news:20A0483F-2BBD-422B-926D-69DF36B26643@.microsoft.com...
> > We are considering using Reporting Services along with Dundas charts for
> > our
> > web-based reporting application. However, we hear that using Reporting
> > Services will LIMIT us to only I.E. browser. Is this true? Can Reporting
> > Services be used with other web browsers besides I.E.?
> >
> > Thank you,
> > Morgan Strickland
> >
>|||There were several bugs in the SQL 2000 version of Reporting Services around
support for other browsers. We are working on addressing these for SQL 2005
Reporting Services. We plan to support Firefox and Safari in addition to IE.
Every single feature might not work, but most things should.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Morgan Strickland" <Morgan Strickland@.discussions.microsoft.com> wrote in
message news:20A0483F-2BBD-422B-926D-69DF36B26643@.microsoft.com...
> We are considering using Reporting Services along with Dundas charts for
> our
> web-based reporting application. However, we hear that using Reporting
> Services will LIMIT us to only I.E. browser. Is this true? Can Reporting
> Services be used with other web browsers besides I.E.?
> Thank you,
> Morgan Strickland
>|||Brian,
We are just now planning on upgrading from SQL 2000 to SQL 2003 (and begin
using Reporting Services). Does SQL 2003 support browsers other than IE? If
not, when is SQL 2005 due for release?
Thank you
Morgan Strickland
"Brian Welcker [MS]" wrote:
> There were several bugs in the SQL 2000 version of Reporting Services around
> support for other browsers. We are working on addressing these for SQL 2005
> Reporting Services. We plan to support Firefox and Safari in addition to IE.
> Every single feature might not work, but most things should.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Morgan Strickland" <Morgan Strickland@.discussions.microsoft.com> wrote in
> message news:20A0483F-2BBD-422B-926D-69DF36B26643@.microsoft.com...
> > We are considering using Reporting Services along with Dundas charts for
> > our
> > web-based reporting application. However, we hear that using Reporting
> > Services will LIMIT us to only I.E. browser. Is this true? Can Reporting
> > Services be used with other web browsers besides I.E.?
> >
> > Thank you,
> > Morgan Strickland
> >
>
>|||There is not a SQL Server 2003. There is a Service Pack 3a but there is not
a SQL Server 2003. MS has said that SQL Server 2005 will be released this
year.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Morgan Strickland" <Morgan Strickland@.discussions.microsoft.com> wrote in
message news:A9A29692-0499-40FC-8EC0-7A594BA5797A@.microsoft.com...
> Brian,
> We are just now planning on upgrading from SQL 2000 to SQL 2003 (and begin
> using Reporting Services). Does SQL 2003 support browsers other than IE?
> If
> not, when is SQL 2005 due for release?
> Thank you
> Morgan Strickland
> "Brian Welcker [MS]" wrote:
>> There were several bugs in the SQL 2000 version of Reporting Services
>> around
>> support for other browsers. We are working on addressing these for SQL
>> 2005
>> Reporting Services. We plan to support Firefox and Safari in addition to
>> IE.
>> Every single feature might not work, but most things should.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Morgan Strickland" <Morgan Strickland@.discussions.microsoft.com> wrote
>> in
>> message news:20A0483F-2BBD-422B-926D-69DF36B26643@.microsoft.com...
>> > We are considering using Reporting Services along with Dundas charts
>> > for
>> > our
>> > web-based reporting application. However, we hear that using Reporting
>> > Services will LIMIT us to only I.E. browser. Is this true? Can
>> > Reporting
>> > Services be used with other web browsers besides I.E.?
>> >
>> > Thank you,
>> > Morgan Strickland
>> >
>>|||wow...new browsers popping up all the time. Never even heard of Safari...
--
Adrian M.
MCP
"Brian Welcker [MS]" <bwelcker@.online.microsoft.com> wrote in message
news:OOoKP5KWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> There were several bugs in the SQL 2000 version of Reporting Services
> around support for other browsers. We are working on addressing these for
> SQL 2005 Reporting Services. We plan to support Firefox and Safari in
> addition to IE. Every single feature might not work, but most things
> should.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Morgan Strickland" <Morgan Strickland@.discussions.microsoft.com> wrote in
> message news:20A0483F-2BBD-422B-926D-69DF36B26643@.microsoft.com...
>> We are considering using Reporting Services along with Dundas charts for
>> our
>> web-based reporting application. However, we hear that using Reporting
>> Services will LIMIT us to only I.E. browser. Is this true? Can Reporting
>> Services be used with other web browsers besides I.E.?
>> Thank you,
>> Morgan Strickland
>|||As far as you are not related with Mac, you won´t get in touch with that.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Adrian M." <absolutelynospam@.nodomain_.com> schrieb im Newsbeitrag
news:O6h6Z%23iWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> wow...new browsers popping up all the time. Never even heard of Safari...
> --
> Adrian M.
> MCP
>
> "Brian Welcker [MS]" <bwelcker@.online.microsoft.com> wrote in message
> news:OOoKP5KWFHA.1796@.TK2MSFTNGP15.phx.gbl...
>> There were several bugs in the SQL 2000 version of Reporting Services
>> around support for other browsers. We are working on addressing these for
>> SQL 2005 Reporting Services. We plan to support Firefox and Safari in
>> addition to IE. Every single feature might not work, but most things
>> should.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Morgan Strickland" <Morgan Strickland@.discussions.microsoft.com> wrote
>> in message news:20A0483F-2BBD-422B-926D-69DF36B26643@.microsoft.com...
>> We are considering using Reporting Services along with Dundas charts for
>> our
>> web-based reporting application. However, we hear that using Reporting
>> Services will LIMIT us to only I.E. browser. Is this true? Can Reporting
>> Services be used with other web browsers besides I.E.?
>> Thank you,
>> Morgan Strickland
>>
>

Thursday, March 22, 2012

BUG? or intended that in order to move or rename reports....

Tuesday, March 20, 2012

Bug?

BUG: SET FMTONLY not working on SQL Server 2000

BUG: Reporting Services Custom Security and Subscriptions?

BUG: Cannot create subscription as NT AUTHORITY\NETWORK SERVICE

Monday, March 19, 2012

Bug report

Sunday, March 11, 2012

Bug in SQL Server Reporting Services?

Bug in SQL Server Reporting Services?

Bug in SQL Server Reporting Services?

Thursday, March 8, 2012

Bug in NS 2005?

Bug in NS 2005?

Bug in NS 2005?

Saturday, February 25, 2012

Buffer Cache Hit Ration on a Reporting DB

Buffer Cache Hit Ration on a Reporting DB

Friday, February 24, 2012

Browsing a cube in ssrs Matrix

Browser Role get Admin Tools... why?

Browser realignment

Browser compatibility for Reporting Services

Boot.ini file

Blog Archive

About Me