Data feeding a report is cached between different calls for a session?

Microsoft SQL Server 2000 - 8.00.818 + Reporting Services SP2
We have created a report (for invoices) that is usually called to render pdf
files (for quality) but can also be rendered into tif files (lower quality).
The front end program for users is written in Access 2003 with a SQL Server
back end for storing the data (client-server approach). Our problem is
described as follows:
1. We create a new invoice (insert new record for invoices table and some
new records for invoicesdetails table)
2. User wants to preview the pdf file for the invoice and click on a button
(MS Access) that internally connects to reporting services (via https)
downloads the pdf file, stores it locally (deleting the original local file
if exists) and opens using acrobat reader.
3. User needs to modify something in the invoice or details and updates the
record (s). Updates the records from tables that are used in the report.
4. Click again on the button to generate the pdf again and to see the new
revision of the invoice but they still receive the old data!!!
Simplifying the scenario, if the data feeding a report is changed between 2
subsecuent calls from the same user (session), reporting services generates
the report with oudated data. It seems that reporting services sees the
request coming from the same user asking for the same data with same
parameters and within the same session and instead of asking SQL server for
the data again (that might have changed), serves the report with the same
old data that it has stored somehow.
To reproduce this behaviour:
1. Create a simple report MyTestReport accepting a parameter InvoiceId
(integer) based on a simple table TInvoices (InvoiceId int, Amount money).
The report just shows the InvoiceId and the Amount of it on a single page.
It uses a shared datasource, type: Microsoft SQL Server, Windows
authentication. Upload this report to Reporting Services.
2. Execute the following SQL command 'INSERT INTO TInvoices (InvoiceId,
Amount) VALUES (0,0)' to ensure that an invoice with Id=0 exists with a
amount of 0.
3. Copy this URL and paste it into your web browser:
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=0&rs:Command=Render&rs:Format=HTML4.0
You will see a reporting services page with InvoiceId field on top filled in
with a 0 and the contents of the report showing 0 0 on it.
4. From the dropdown list of 'Select a format' select 'Acrobat PDF File' and
click on 'export', then click 'open'. You see a pdf file with 0 0 on it.
This is correct. Close acrobat reader. Do not close IE window.
5. Go back to your SQL Server and execute 'UPDATE TInvoices SET Amount=1000
WHERE InvoiceId = 0'
6. Up to this poing, the 0 0 on IE window is outdated, but we have not
refreshed yet. If we click on IE Refresh button to retrieve the same
original URL that we had on our clipboard a 0 0 still appears!!!!! The data
is not fetched again from SQL server, reporting services is caching it
somehow and returning it to me again. To be sure that it is not a problem
with IE cache, go Tools | Internet options | General tab and click the
buttons 'Delete cookies' and 'Delete files'. Click on Accept button. Now
refresh the page again (F5). The page is still showing 0 0!!!!!
7. If we select 'Adobe PDF File' from 'Select a format' dropdownlist and
click again on 'export', then 'open', a new PDF file is downloaded and open,
but there is a 0 0 on it yet!!!!
Of course, if you click the button 'View report' on top right side of the
page, the report is shown (requeried) with updated data. The report reads 0
1000 which is correct. However in our scenario, our users are not browsing
through reporting services site interface and do not have that 'View report'
button at hand. When they need a report (Invoices or other) with whatever
the filter and parameters, a MS Access Form is shown to them to select from
dropdown lists, dates, codes, etc. Then our front end in MS Access
constructs the apropriate URL considering the parameters the user has just
entered in the form and opens (or downloads) the report acordingly.
The only way to retrieve the updated data without clicking that 'View
report' button is having the user asking for another unwanted InvoiceId
(InvoiceId=1 for instance) before asking again for what he/she needs:
UPDATE TInvoices (InvoiceId, Amount) VALUES (0,0)
INSERT TInvoices (InvoiceId, Amount) VALUES (1,10)
Paste
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=0&rs:Command=Render&rs:Format=HTML4.0
in IE
0 0 is shown
UPDATE TInvoices (InvoiceId, Amount) VALUES (0,1000)
Paste
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=0&rs:Command=Render&rs:Format=HTML4.0
in IE or click refresh
0 0 is shown wich is incorrect
Paste
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=1&rs:Command=Render&rs:Format=HTML4.0
in IE
1 10 is shown (unwanted, but needed for reporting services flushing its
internal outdated data)
Paste
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=0&rs:Command=Render&rs:Format=HTML4.0
in IE
0 1000 is shown which is correct.
It seems that unless the user asks reporting services to generate the report
with other parameters, it returns the old dataset without asking again to
SQL server, in this case generating a report with outdated data.
Note: We have also checked that 'execution properties' for the report has
'Show this report with the most recent data' and 'Do not save cache copies
of this report' enabled.
Is this a known issue? Is there a fix/workaround for it? Any registry tweaks
for forcing reporting services to always ask the datasource instead of
caching?David,
This is correct behavior. Reporting Services maintains report session,
which is needed for certain things like switching between pages or drilling
down.
For your situation, I think there are two easy things you can do. First,
you can add "rs:ClearSession=true" to your URL. This will treat the request
as a new session, and not reuse the data from the previous report view. You
can find more information if you search BOL or this newsgroup for
ClearSession.
I think you can also resolve this problem by deallocating your web request
object in Access after each report view. I haven't done this myself, but I
think that would cause each request to start with a new session.
(FYI, the caching option allows you to store/share dataset results across
users. This is a big performance boost for data that doesn't change, e.g.
last month's balance sheet. Your problem has to do with session for the same
user.)
HTH,
Ted
"David Lightman Robles" wrote:
> It seems that unless the user asks reporting services to generate the report
> with other parameters, it returns the old dataset without asking again to
> SQL server, in this case generating a report with outdated data.
> Note: We have also checked that 'execution properties' for the report has
> 'Show this report with the most recent data' and 'Do not save cache copies
> of this report' enabled.
> Is this a known issue? Is there a fix/workaround for it? Any registry tweaks
> for forcing reporting services to always ask the datasource instead of
> caching?|||Thanks for the information. That solved all our problems.
"Ted K" <tedk@.nospam.nospam> escribió en el mensaje
news:F5ABBB95-AE6C-482B-B1B6-1A26859B0C0B@.microsoft.com...
> David,
> This is correct behavior. Reporting Services maintains report session,
> which is needed for certain things like switching between pages or
> drilling
> down.
> For your situation, I think there are two easy things you can do. First,
> you can add "rs:ClearSession=true" to your URL. This will treat the
> request
> as a new session, and not reuse the data from the previous report view.
> You
> can find more information if you search BOL or this newsgroup for
> ClearSession.
> I think you can also resolve this problem by deallocating your web request
> object in Access after each report view. I haven't done this myself, but
> I
> think that would cause each request to start with a new session.
> (FYI, the caching option allows you to store/share dataset results across
> users. This is a big performance boost for data that doesn't change, e.g.
> last month's balance sheet. Your problem has to do with session for the
> same
> user.)
> HTH,
> Ted
> "David Lightman Robles" wrote:
>> It seems that unless the user asks reporting services to generate the
>> report
>> with other parameters, it returns the old dataset without asking again to
>> SQL server, in this case generating a report with outdated data.
>> Note: We have also checked that 'execution properties' for the report has
>> 'Show this report with the most recent data' and 'Do not save cache
>> copies
>> of this report' enabled.
>> Is this a known issue? Is there a fix/workaround for it? Any registry
>> tweaks
>> for forcing reporting services to always ask the datasource instead of
>> caching?
>sql