Multi-user issues with SSRS2000

Hi,

At our company we are looking at implementing SQL Server 2000 Reporting Services into our internet application. Having followed your book, and produced a dozen reports, I have found a problem with calling reports in a multi-user environment.

Some of the reports that our customers want can run to over 100 pages which, whilst a large number, is a requirement. I have found that when two people call one of these large reports at the same time; one of the users gets the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection". Once the worker process begins to render the report on the first user's screen, the report may be refreshed on the second users screen and it renders quite normally. Under normal circumstances the login credentials are valid and identical for both users.

Furthermore, an error 'Server Application Unavailable' is often generated when a number of users are requesting large reports this appears to be caused by the worker process being recycled.

Our production servers are a mixture of Windows 2000 Server (SP4) and Windows 2003 Server Standard Edition (SP1), the version of SQL Server and Report services is 2000 Enterprise Edition, using the latest service packs (SQL Server is SP4, and report services is SP2).

Can you suggest how we can get around these errors.

Regards,

Martin

Because your large report is consuming so much memory during the rendering process, your worker process is probably recycling because it's hitting the max MemoryLimit as defined in machine.config.

The "Login Failed" error message that you mentioned is a symptom of your worker process recycling, coming back up, and then trying to request a report on your behalf...however, because it cycled, it lost your user context (credentials), ergo the "Null" user...

The article below discusses dealing with memory limits / issues with large reports.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;909678

sql

Multi-user errors

Hi,
At our company we are looking at implementing SQL Server 2000 Reporting
Services into our internet application. Having followed your book, and
produced a dozen reports, I have found a problem with calling reports
in a multi-user environment.
Some of the reports that our customers want can run to over 100 pages
which, whilst a large number, is a requirement. I have found that when
two people call one of these large reports at the same time; one of the
users gets the error "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection". Once the worker
process begins to render the report on the first user's screen, the
report may be refreshed on the second users screen and it renders quite
normally. Under normal circumstances the login credentials are valid
and identical for both users.
Furthermore, an error 'Server Application Unavailable' is often
generated when a number of users are requesting large reports this
appears to be caused by the worker process being recycled.
Our production servers are a mixture of Windows 2000 Server (SP4) and
Windows 2003 Server Standard Edition (SP1), the version of SQL Server
and Report services is 2000 Enterprise Edition, using the latest
service packs (SQL Server is SP4, and report services is SP2).
Can anyone suggest how we can get around these errors.
Regards,
MartinMartin,
It sounds like the problems occur during the processing/collecting of
data and that once that finishes and the reports begin rendering the
conflicts resolve. The first thing that comes to mind is rendering
these reports from snapshot data. Using a snapshot, you schedule the
query that generates your large dataset to run during off peak hours.
The reports can then be rendered immediately from the (already)
collected data rather than both collecting and rendering. This of
course assumes that you don't need up-to-the-moment i.e. realtime data
for the reports otherwise snapshots may not be an option for you.
Another option might be using subscriptions to distribute (push)
multiple copies of the reports rendered from one run of the process.
This might be the way to go if this an instance of one report being
called (pulled) by several users. Again, this assumes relatively
static data.
HTH
toolman
MartinClayton wrote:
> Hi,
> At our company we are looking at implementing SQL Server 2000 Reporting
> Services into our internet application. Having followed your book, and
> produced a dozen reports, I have found a problem with calling reports
> in a multi-user environment.
> Some of the reports that our customers want can run to over 100 pages
> which, whilst a large number, is a requirement. I have found that when
> two people call one of these large reports at the same time; one of the
> users gets the error "Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection". Once the worker
> process begins to render the report on the first user's screen, the
> report may be refreshed on the second users screen and it renders quite
> normally. Under normal circumstances the login credentials are valid
> and identical for both users.
> Furthermore, an error 'Server Application Unavailable' is often
> generated when a number of users are requesting large reports this
> appears to be caused by the worker process being recycled.
> Our production servers are a mixture of Windows 2000 Server (SP4) and
> Windows 2003 Server Standard Edition (SP1), the version of SQL Server
> and Report services is 2000 Enterprise Edition, using the latest
> service packs (SQL Server is SP4, and report services is SP2).
> Can anyone suggest how we can get around these errors.
> Regards,
> Martin

Multiple Rows into One

ISBN Parent_ISBN Title FMonth Forecast SQL TABLE 043967XXXX 043967XXXX MY BOOK 200705 13.25 043967XXXX 043967XXXX MY BOOK 200706 88 043967XXXX 043967XXXX MY BOOK 200707 53 043967XXXX 043967XXXX MY BOOK 200708 54 043967XXXX 043967XXXX MY BOOK 200709 8.08 043967XXXX 043967XXXX MY BOOK 200710 11.84 043967XXXX 043979XXXX MY BOOK2 200705 6.5 043967XXXX 043979XXXX MY BOOK2 200706 135 043967XXXX 043979XXXX MY BOOK2 200707 82 043967XXXX 043979XXXX MY BOOK2 200708 83 043967XXXX 043979XXXX MY BOOK2 200709 80.64 043967XXXX 043979XXXX MY BOOK2 200710 112.16 ISBN Parent_ISBN Title FMonth Forecast FMonth Forecast FMonth Forecast FMonth Forecast FMonth Forecast FMonth Forecast 043967XXXX 043967XXXX MY BOOK 200705 13.25 200706 88 200707 53 200708 54 200709 8.08 200710 11.84 043967XXXX 043979XXXX MY BOOK2 200705 6.5 200706 135 200707 82 200708 83 200709 80.64 200710 112.16 (Desired) RESULTS

I have a table with multiple rows per ISBN. I would like to create a table with one line per ISBN adding columns. I dont want to concatenate any columns.

My goal is to dump this into a spreadsheet through Intregration Services 2005. I am not the DBA so I can't redesign the main table. Thanks for any help.

Here you go..

Code Snippet

--Drop table #books

Create Table #books (

[ISBN] Varchar(100) ,

[Parent_ISBN] Varchar(100) ,

[Title] Varchar(100) ,

[FMonth] Varchar(100) ,

[Forecast] Varchar(100)

);

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200705','13.25');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200706','88');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200707','53');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200708','54');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200709','8.08');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200710','11.84');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200705','6.5');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200706','135');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200707','82');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200708','83');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200709','80.64');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200710','112.16');

Select

[ISBN]

,[Parent_ISBN]

,[Title]

,Max(Case [FMonth] When 200705 Then 200705 End) as FMonth

,Max(Case [FMonth] When 200705 Then Forecast End) as Forecast

,Max(Case [FMonth] When 200706 Then 200706 End) as FMonth

,Max(Case [FMonth] When 200706 Then Forecast End) as Forecast

,Max(Case [FMonth] When 200707 Then 200707 End) as FMonth

,Max(Case [FMonth] When 200707 Then Forecast End) as Forecast

,Max(Case [FMonth] When 200708 Then 200708 End) as FMonth

,Max(Case [FMonth] When 200708 Then Forecast End) as Forecast

,Max(Case [FMonth] When 200709 Then 200709 End) as FMonth

,Max(Case [FMonth] When 200709 Then Forecast End) as Forecast

,Max(Case [FMonth] When 200710 Then 200710 End) as FMonth

,Max(Case [FMonth] When 200710 Then Forecast End) as Forecast

From

#books

Group By

[ISBN]

,[Parent_ISBN]

,[Title]

|||The only problem with this is that I have several thousand ISBNs which all appear 6 times in the table. From each set of 6 rows I need to create one row. Is there anyway that I can do this by setting parameters? Thanks|||

Yes. You can . here it is..

Code Snippet

--Drop table #books

Create Table #books (

[ISBN] Varchar(100) ,

[Parent_ISBN] Varchar(100) ,

[Title] Varchar(100) ,

[FMonth] Varchar(100) ,

[Forecast] Varchar(100)

);

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200705','13.25');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200706','88');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200707','53');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200708','54');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200709','8.08');

Insert Into #books Values('043967XXXX','043967XXXX','MYBOOK','200710','11.84');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200705','6.5');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200706','135');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200707','82');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200708','83');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200709','80.64');

Insert Into #books Values('043967XXXX','043979XXXX','MYBOOK2','200710','112.16');

Declare @.Start as Varchar(10);

Set @.Start = '200707'

Select

[ISBN]

,[Parent_ISBN]

,[Title]

,@.Start as FMonth

,Max(Case [FMonth] When @.Start Then Forecast End) as Forecast

,Substring(Convert(varchar,DateAdd(MM,1,Cast(@.Start + '01' as datetime)),112),1,6) as FMonth

,Max(Case [FMonth] When Substring(Convert(varchar,DateAdd(MM,1,Cast(@.Start + '01' as datetime)),112),1,6) Then Forecast End) as Forecast

,Substring(Convert(varchar,DateAdd(MM,2,Cast(@.Start + '01' as datetime)),112),1,6) as FMonth

,Max(Case [FMonth] When Substring(Convert(varchar,DateAdd(MM,2,Cast(@.Start + '01' as datetime)),112),1,6) Then Forecast End) as Forecast

,Substring(Convert(varchar,DateAdd(MM,3,Cast(@.Start + '01' as datetime)),112),1,6) as FMonth

,Max(Case [FMonth] When Substring(Convert(varchar,DateAdd(MM,3,Cast(@.Start + '01' as datetime)),112),1,6) Then Forecast End) as Forecast

,Substring(Convert(varchar,DateAdd(MM,4,Cast(@.Start + '01' as datetime)),112),1,6) as FMonth

,Max(Case [FMonth] When Substring(Convert(varchar,DateAdd(MM,4,Cast(@.Start + '01' as datetime)),112),1,6) Then Forecast End) as Forecast

,Substring(Convert(varchar,DateAdd(MM,5,Cast(@.Start + '01' as datetime)),112),1,6) as FMonth

,Max(Case [FMonth] When Substring(Convert(varchar,DateAdd(MM,5,Cast(@.Start + '01' as datetime)),112),1,6) Then Forecast End) as Forecast

From

#books

Where

[FMonth] Between @.Start And Substring(Convert(varchar,DateAdd(MM,5,Cast(@.Start + '01' as datetime)),112),1,6)

Group By

[ISBN]

,[Parent_ISBN]

,[Title]

|||

For 2005:

Code Snippet

Droptable #books

CreateTable #books (

[ISBN] Varchar(100),

[Parent_ISBN] Varchar(100),

[Title] Varchar(100),

[FMonth] Varchar(100),

[Forecast] money

);

InsertInto #books Values('043967XXXX','043967XXXX','MYBOOK','200705','13.25');

InsertInto #books Values('043967XXXX','043967XXXX','MYBOOK','200706','88');

InsertInto #books Values('043967XXXX','043967XXXX','MYBOOK','200707','53');

InsertInto #books Values('043967XXXX','043967XXXX','MYBOOK','200708','54');

InsertInto #books Values('043967XXXX','043967XXXX','MYBOOK','200709','8.08');

InsertInto #books Values('043967XXXX','043967XXXX','MYBOOK','200710','11.84');

InsertInto #books Values('043967XXXX','043979XXXX','MYBOOK2','200705','6.5');

InsertInto #books Values('043967XXXX','043979XXXX','MYBOOK2','200706','135');

InsertInto #books Values('043967XXXX','043979XXXX','MYBOOK2','200707','82');

InsertInto #books Values('043967XXXX','043979XXXX','MYBOOK2','200708','83');

InsertInto #books Values('043967XXXX','043979XXXX','MYBOOK2','200709','80.64');

InsertInto #books Values('043967XXXX','043979XXXX','MYBOOK2','200710','112.16');

;with cte as

(

select isbn +'-'+ parent_isbn as rkey, forecast, row_number()over(partitionby isbn, parent_isbn orderby fmonth)as rn

from #books

), cte2 as

(

select rkey, [1] as F1, [2] as F2, [3] as F3, [4] as F4, [5] as F5, [6] as F6

from cte

pivot

(

sum(forecast)

FOR rn IN([1], [2], [3], [4], [5], [6])

)as p

), cte3 as

(

select isbn +'-'+ parent_isbn as rkey, fmonth, row_number()over(partitionby isbn, parent_isbn orderby fmonth)as rn

from #books

), cte4 as

(

select rkey, [1] as FM1, [2] as FM2, [3] as FM3, [4] as FM4, [5] as FM5, [6] as FM6

from cte3

pivot

(

min(fmonth)

FOR rn IN([1], [2], [3], [4], [5], [6])

)as p

)

select books.isbn, books.parent_isbn, books.title,

cte4.fm1 as FMonth, cte2.f1 as Forecast,

cte4.fm2 as FMonth, cte2.f2 as Forecast,

cte4.fm3 as FMonth, cte2.f3 as Forecast,

cte4.fm4 as FMonth, cte2.f4 as Forecast,

cte4.fm5 as FMonth, cte2.f5 as Forecast,

cte4.fm6 as FMonth, cte2.f6 as Forecast

from cte2

innerjoin cte4

on cte2.rkey = cte4.rkey

innerjoin

(selectdistinct isbn, parent_isbn, title

from #books books

)as books

onleft(cte2.rkey,charindex('-', cte2.rkey)-1)= books.isbn

andright(cte2.rkey,len(cte2.rkey)-charindex('-', cte2.rkey))= books.parent_isbn

|||This was exactly what i was looking for...thanks...the only problem was having a static date in:

Declare @.Start as Varchar(10);

Set @.Start = '200707'

I wanted to use current yyyymm.