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

Data driven subscriptions and scale-out?

Say I have a scheduled data-driven reports that will product pdf files (a lot). Will this benefit
from scale-out?

I.e., having one SQL Server with the reporting services databases and several
servers with the reporting services windows service and web services.

I have this feeling that when the Agent job starts, it will do this in a matter so that all the
reports for this job will be processes on only one RS server? Or perhaps I can partition this
myself, where I somehow have several jobs, each job connecting to a different server generating a
subset of the reports?

No replies so I tested it myself. After lots of hassle to get scale-out installed, but I finally got it working. And the answer is Yes.

I processed a data-driven subscription to generate bunch of pdf files and CPU went up on both RS machines. Also looking in the RS log files I can see that each RS machine processed different PDF files.

Data driven subscriptions and scale-out?

Say I have a scheduled data-driven reports that will product pdf files (a lot). Will this benefit
from scale-out?

I.e., having one SQL Server with the reporting services databases and several
servers with the reporting services windows service and web services.

I have this feeling that when the Agent job starts, it will do this in a matter so that all the
reports for this job will be processes on only one RS server? Or perhaps I can partition this
myself, where I somehow have several jobs, each job connecting to a different server generating a
subset of the reports?

No replies so I tested it myself. After lots of hassle to get scale-out installed, but I finally got it working. And the answer is Yes.

I processed a data-driven subscription to generate bunch of pdf files and CPU went up on both RS machines. Also looking in the RS log files I can see that each RS machine processed different PDF files.

Data Driven subscription not working for PDF, IMAGE

Hi,
I worked on SQL Server tutorials in Books online, "Data Driven
Subscription". In this tutorial, reports are delivered via email on a
scheduled time. as per the tutorial emails are being delivered in "MHTML"
format but it does not deliver the report in "PDF" or "Image" format. The
report manager shows a status message that there was an error but there is
no way to know the exact error. It just says "1 errors". The reprot is
working absolutely fine as it is being delivered in "MHTML" format but not
in "PDF" or "IMAGE". Please assist.
Regards
GinnyJust check in errorlog file for any specific errors. Just manually run the
same report and see whether it gives the same error.
Amarnath
"Ginny" wrote:
> Hi,
> I worked on SQL Server tutorials in Books online, "Data Driven
> Subscription". In this tutorial, reports are delivered via email on a
> scheduled time. as per the tutorial emails are being delivered in "MHTML"
> format but it does not deliver the report in "PDF" or "Image" format. The
> report manager shows a status message that there was an error but there is
> no way to know the exact error. It just says "1 errors". The reprot is
> working absolutely fine as it is being delivered in "MHTML" format but not
> in "PDF" or "IMAGE". Please assist.
> Regards
> Ginny
>
>

Damaged PDF files

Hi,
I have created a web application with ASP.Net 2.0. I also have created a
report using Reporting Services 2005. After showing the report on the
screen, I give the suer the possibility to export to report. One of the
possibilities is PDF. Now we have some troubles with this export.
On some PC's after saving or opening the PDF we got the message :
"There was an error opening this document. The file is damaged and could
not be repaired"
It only appears on some PC's, on other PC's exporting the same documents
works fine. It also didn't have any effect on the version of Acrobat. The
problems comes with Acro v 5, 7 and 8. Even after upgrading, deinstalling,
rebooting, installing a newer version the problem still exists on those PC's.
So I hope that someone have a solution for this as we don't know if it's a
problem with reporting services or with acrobat.
Txs in advance.On Apr 30, 4:36 am, Cipidos <Cipi...@.discussions.microsoft.com> wrote:
> Hi,
> I have created a web application with ASP.Net 2.0. I also have created a
> report using Reporting Services 2005. After showing the report on the
> screen, I give the suer the possibility to export to report. One of the
> possibilities is PDF. Now we have some troubles with this export.
> On some PC's after saving or opening the PDF we got the message :
> "There was an error opening this document. The file is damaged and could
> not be repaired"
> It only appears on some PC's, on other PC's exporting the same documents
> works fine. It also didn't have any effect on the version of Acrobat. The
> problems comes with Acro v 5, 7 and 8. Even after upgrading, deinstalling,
> rebooting, installing a newer version the problem still exists on those PC's.
> So I hope that someone have a solution for this as we don't know if it's a
> problem with reporting services or with acrobat.
> Txs in advance.
I've never seen this occur before; however, from what you are saying,
it seems that it is a non-Reporting Services issue. Traditionally, if
it was SSRS, then all of the machines would be behaving the same way.
It may not be related to Acrobat either; however, I'm not sure what
could be causing the problem (missing Adobe update, missing MS Office
update, etc). Sorry that I could not be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Cyrstal Report and ASP- HEEEEELLLP!

Hello:

I am using ASP and Crystal report. I created a .rpt file on a dev server and I can access the report in pdf format using ASP.

But now I want to implement the same on production server. I copied the .rpt file and changed the database settings. The preview pane shows the new data. But when I call the report from ASP and convert it to pdf I get nothing. Just the report heading and no rows get selected.

I know there is data in the database, and I confirm that the db connection is ok.

Any clues?

Also why do we specify the
Set crtable = session("oRpt").Database.Tables.Item(1)
crtable.SetLogonInfo CStr(databaseServer), CStr(databaseTables), CStr(databaseUser), CStr(databasePassword)

in the ASP, when we have to specify the same credentials in the rpt file as well?

Is is adviced to save the report with data or without?

Your help will be greatly appreciated.
ThanksThe report is not to be saved with that data.If u save the report with the data the data will be not be refreshed each time while opening the report.

Set crtable = session("oRpt").Database.Tables.Item(1)
crtable.SetLogonInfo CStr(databaseServer), CStr(databaseTables), CStr(databaseUser), CStr(databasePassword)

These lines are written so that the report can log on to the data server.|||Hi

Iam going to include a crpt report in our existing asp application.
I dont have any idea to code for how to call and use crpt in asp file.

Pls, any one give me sample codings for the same.
mail id saravanakumar77@.yahoo.com

regards
Saravanakumar.D

Cutting and Pasting from PDF output

Hi

We have some reports that are published as PDF files. The layout of the report is fairly free, using lists within lists.

Some of our users want to cut and paste some of the data. When they do so, the order and layout of the fields does not resemble the layout of the report. Any way around this.

Note that when the report is published as mhtml or excel, cutting and pasting from these formats is much better.

thanks

Peter

No, there is no way around that. If the user is going to reuse the figues in another format then it is best to stick to Excel output. If only specific users need this then you could set them up with an subscription that has Excel output, and leave everyone else with the PDF version.