In order for caching to work do you need to use SQL authentication
connecting to the sql server datasource. I know you need stored credentials
Thanks in advanceDo you need to have kerebos turned on. Because if the datasource is on
another server you get double hop and it only works using sql authentication
for us
"bobhug" <bobhug@.discussions.microsoft.com> wrote in message
news:6E6120C2-380C-4082-BE5F-FE2EAEA07834@.microsoft.com...
> Caching works fine using stored Windows credentials.
> Bob
> "Will Byron" wrote:
> > In order for caching to work do you need to use SQL authentication
> > connecting to the sql server datasource. I know you need stored
credentials
> > Thanks in advance
> >
> >
> >
Showing posts with label caching. Show all posts
Showing posts with label caching. Show all posts
Sunday, March 11, 2012
Caching with Dynamic Security
I defined one role in my AS database with dynamic security for each dimension. I am accessing the AS database in an ASP.NET web application which runs under a domain user and uses Form Authentication. Therefore I always connect to the AS database under the same domain user even though security should be based on the user logged into the web application. When I run a MDX query, I pass along the web user's security info as part of the connectionstring and uses dynamic security to get the AllowedSet for each dimension. However, I notice my user defined function is ran only on the first time I run a query, meaning the dimension security is cached based on the domain user in the connection string and not the web user. Sorry if this sounds confusing but I can clarify a bit more if needed. My question boils down to: Is there a way to tell AS database to cache result base on the CustomData property of the connectionstring?
The answer is YES - AS is smart enough to recognize that different values of CustomData were used even though the real identity on the connection is the same. Since the main purpose of CustomData was for custom authentication - it is treated the same as different users. Same is true w.r.t. Roles and UserId properties.
HTH,
Mosha (http://www.mosha.com/msolap)
|||Mosha,
As always, you are right and thanks for the help. I did a little more testing after my post and realized AS is caching the result base on the CustomData property. Thanks!
Caching UDF
Hi All,
I have an application that reads data from a very slow database link
(like 10 seconds per call) though what I am looking for would be of
generic use for anyone who has long-running queries that are
frequently repeated.
I would like to be able to cache the results of a query so that I do
not have to re-execute that query if it is reissued. Ideally I
believe that this could be implemented by hiding the query inside a
UDF and exposing the UDF through a view. The UDF could then "Check
the cache" and only run the slow query if there wasn't a match (or if
the match was too old). From what I understand the best way to do
this would be for the cache to be an extended stored procedure.
Has anyone done or seen this? Has someone written a copy that I
could purchase? Does anyone care to offer their opinnion of how or if
this could work?
Thanks in Advance,
StevenHi
Caching like this is usually the function of a middle tier rather than the
database.
John
"Steven Ensslen" <ensslen@.planet-save.com> wrote in message
news:73ce0e91.0405141350.716061eb@.posting.google.c om...
> Hi All,
> I have an application that reads data from a very slow database link
> (like 10 seconds per call) though what I am looking for would be of
> generic use for anyone who has long-running queries that are
> frequently repeated.
> I would like to be able to cache the results of a query so that I do
> not have to re-execute that query if it is reissued. Ideally I
> believe that this could be implemented by hiding the query inside a
> UDF and exposing the UDF through a view. The UDF could then "Check
> the cache" and only run the slow query if there wasn't a match (or if
> the match was too old). From what I understand the best way to do
> this would be for the cache to be an extended stored procedure.
> Has anyone done or seen this? Has someone written a copy that I
> could purchase? Does anyone care to offer their opinnion of how or if
> this could work?
> Thanks in Advance,
> Steven|||[posted and mailed, please reply in news]
Steven Ensslen (ensslen@.planet-save.com) writes:
> I have an application that reads data from a very slow database link
> (like 10 seconds per call) though what I am looking for would be of
> generic use for anyone who has long-running queries that are
> frequently repeated.
> I would like to be able to cache the results of a query so that I do
> not have to re-execute that query if it is reissued. Ideally I
> believe that this could be implemented by hiding the query inside a
> UDF and exposing the UDF through a view. The UDF could then "Check
> the cache" and only run the slow query if there wasn't a match (or if
> the match was too old). From what I understand the best way to do
> this would be for the cache to be an extended stored procedure.
Unless I am misunderstanding something, this won't fly at all. The UDF
and the extended stored procedure still executes on the server, so there
is no cache you could retrieve data from. SQL Server maintains a cache, but
that is from disk to local memory, so from your point of view, this is
still on the remote side of your link.
For such a cache to be meaningful, you must have it on your side of the
link. Thus, the typical place to fix this would be in the application
itself (unless there is a separate middle tier between the application
and the database).
If this is an application you cannot modify, you might still be able to
do it, but it will be hairy. In this case you would point your application
to a local SQL Server, which use linked servers to access the remote
server, and this local server would implement a cache. But how you would
load the cache and keep int current is far from trivial. To develop this,
I wold need some more information to proceed.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the replies, but I guess that I haven't explained my idea
clearly enough.
> Unless I am misunderstanding something, this won't fly at all. The UDF
> and the extended stored procedure still executes on the server, so there
> is no cache you could retrieve data from. SQL Server maintains a cache, but
> that is from disk to local memory, so from your point of view, this is
> still on the remote side of your link.
> For such a cache to be meaningful, you must have it on your side of the
> link. Thus, the typical place to fix this would be in the application
> itself (unless there is a separate middle tier between the application
> and the database).
I'm looking for a custom-coded,programmer-activated, server-side
cache. I want to be able to store an arbitrary string so that it
persists for my entire database session and I do not have to execute
the expensive query that generated that string more than once.
> If this is an application you cannot modify, you might still be able to
> do it, but it will be hairy. In this case you would point your application
> to a local SQL Server, which use linked servers to access the remote
> server, and this local server would implement a cache. But how you would
> load the cache and keep int current is far from trivial. To develop this,
> I wold need some more information to proceed.
You're correct that I can't modify the application. So I'd like the
local server to implement a cache of the remote server.
Has anyone done this? Does anyone have an example or know of a 3rd
party program/extension that will perform this function?
Steven|||Steven Ensslen (ensslen@.planet-save.com) writes:
> I'm looking for a custom-coded,programmer-activated, server-side
> cache. I want to be able to store an arbitrary string so that it
> persists for my entire database session and I do not have to execute
> the expensive query that generated that string more than once.
I'm afraid that I don't really follow. Can you give an overview the
architecture of the application as it works now? I mean which boxes
you have, and where the slow link is.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Caching stored procedures
I've recently been told by developers that some of our
SQL 2000 stored procedures an application is using take
longer to run the first time they are ran in a while. If
they run them again, they seem to be quicker. Does
anyone have any suggestions as to where to start looking
to resolve this? Does this have to do with the execution
plan not staying in the cache? Is there a way to fix
this?Stored procedures are compiled when the execution plan is not in cache
but the performance hit is usually not significant for occasional
compiles. The more likely cause is that data is retrieved from disk
when the proc is first run and remains in cache for subsequent access.
You can see if this is the case by running the following test:
USE MyDatabase
GO
CHECKPOINT
DBCC DROPCLEANBUFFERS
GO
SELECT GETDATE()
EXECUTE MyProcedure
SELECT GETDATE()
EXECUTE MyProcedure
SELECT GETDATE()
GO
If the second execution is noticeably faster, you might take a look at
the execution plan to see if you can optimize the queries and/or add
indexes. This can reduce both physical and logical i/o.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Bill" <bill4390@.hotmaillcom> wrote in message
news:286f01c3673b$06480360$7d02280a@.phx.gbl...
> I've recently been told by developers that some of our
> SQL 2000 stored procedures an application is using take
> longer to run the first time they are ran in a while. If
> they run them again, they seem to be quicker. Does
> anyone have any suggestions as to where to start looking
> to resolve this? Does this have to do with the execution
> plan not staying in the cache? Is there a way to fix
> this?
>
SQL 2000 stored procedures an application is using take
longer to run the first time they are ran in a while. If
they run them again, they seem to be quicker. Does
anyone have any suggestions as to where to start looking
to resolve this? Does this have to do with the execution
plan not staying in the cache? Is there a way to fix
this?Stored procedures are compiled when the execution plan is not in cache
but the performance hit is usually not significant for occasional
compiles. The more likely cause is that data is retrieved from disk
when the proc is first run and remains in cache for subsequent access.
You can see if this is the case by running the following test:
USE MyDatabase
GO
CHECKPOINT
DBCC DROPCLEANBUFFERS
GO
SELECT GETDATE()
EXECUTE MyProcedure
SELECT GETDATE()
EXECUTE MyProcedure
SELECT GETDATE()
GO
If the second execution is noticeably faster, you might take a look at
the execution plan to see if you can optimize the queries and/or add
indexes. This can reduce both physical and logical i/o.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Bill" <bill4390@.hotmaillcom> wrote in message
news:286f01c3673b$06480360$7d02280a@.phx.gbl...
> I've recently been told by developers that some of our
> SQL 2000 stored procedures an application is using take
> longer to run the first time they are ran in a while. If
> they run them again, they seem to be quicker. Does
> anyone have any suggestions as to where to start looking
> to resolve this? Does this have to do with the execution
> plan not staying in the cache? Is there a way to fix
> this?
>
Caching Set Up Problem
Hi Expert,
Am I doing it right? Can you give me any suggestions please? Thank you!!
I followed the way for setting up the sql cache dependency fromhttp://quickstarts.asp.net/QuickStartv20/aspnet/doc/caching/SQLInvalidation.aspx.
However, I still have the following error:
When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.
IssueSummary.aspx
<%@.PageLanguage="VB"MasterPageFile="~/MasterPage.master"AutoEventWireup="true"CodeFile="IssueSummary.aspx.vb"Inherits="IssueSummary"title="Issue Summary" %>
<%@.RegisterAssembly="AtlasControlToolkit"Namespace="AtlasControlToolkit"TagPrefix="cc2" %>
<%@.RegisterAssembly="Microsoft.Web.Atlas"Namespace="Microsoft.Web.UI"TagPrefix="cc1" %>
<%@.OutputCacheDuration="999999"VaryByParam="None"SqlDependency="CommandNotification" %>
Global.asax
Sub Application_Start(ByVal senderAsObject,ByVal eAs EventArgs)
Dim connectionStringAsString = ConfigurationManager.ConnectionStrings("ICTConnectionString").ConnectionString
Dim needToInstallAsBoolean =True
Try
Dim tables()AsString
tables = SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionString)
If (Not tablesIsNothing)Then
Dim tblAsString
ForEach tblIn tables
If (tbl.ToLower().Equals("IssueDetails"))Then
needToInstall =False
EndIf
Next
EndIf
Catch exAs Exception
needToInstall =True
EndTry
If (needToInstall)Then
SqlCacheDependencyAdmin.EnableNotifications(connectionString)
SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString,"IssueDetails")
EndIf
EndSub
Web.config
<connectionStrings>
<addname="ICTConnectionString"connectionString="Data Source=jamesle-3;Initial Catalog=IssueCommunicationTool;Integrated Security=True"providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependencyenabled="true"pollTime="1000" >
<databases>
<addname="ICTDB"connectionStringName="ICTConnectionString" />
</databases>
</sqlCacheDependency>
</caching>
SQL Server 2005 - Database - IssueCommunicationTool
SELECT is_broker_enabledFROMsys.databasesWHEREname='IssueCommunicationTool'
Return = 1, it means the broker service is enabled. Also, the database has created the table ASPNET_SQLCacheTableForChangeNotification,
the trigger in IssueDetails table, and some other store procedures.
Just thought that if loading them dynamicly you may want just to add the user control in the code behind
Cache["Myusercontrol"] = usercontrolname;
grab them
if ( Cache["Myusercontrol"] != null )
{
usercontrolname = (UserControl) Cache["Myusercontrol"];
}
|||You are messing the both ways for SQL caching - polling and notifications. Polling is for both 2000 and 2005 version, whether notifications is only for 2005, because of the Service Broker.
In your case you have been usingSqlDependency="CommandNotification" in the outputcache directive. This means that you are using notification based invalidation. In such case you should call SqlDependency.Start(connectionString) in the Application_OnStart method (Global.asax file). In such case you did not need any settings in the web.config file. You need then only if you use polling based invalidation. This is all written down on the page that you point. Just read it more carefully.
Caching results of Stored Procedure
I was wondering if there is anyway to cache the results of a stored procedure in order to gain speed ?
Or do I need to do something else clever ?
Here is my SQL
procedure spGetProcedureParams(v_procname in varchar2,
v_packagename in varchar2,
CurRef out typeRefCur,
v_errcode out integer,
v_errmesg out varchar2)
is
begin
v_errcode :=0;
open CurRef for
select argument_name,
data_type,
NVL(pls_type,'REF'),
in_out
from user_arguments
where upper(object_name) = upper(v_procname)
and upper(package_name) = upper(v_packagename)
order by sequence;
exception
when others then
v_errcode :=SQLCODE;
v_errmesg :=SQLERRM;
end spGetProcedureParams;
end pkgUtils;
/I'm not really sure what you are looking for. You could fetch the data into a collection like an index-by table. Whether this will improve performance depends on what you are going to do with it next.
Or do I need to do something else clever ?
Here is my SQL
procedure spGetProcedureParams(v_procname in varchar2,
v_packagename in varchar2,
CurRef out typeRefCur,
v_errcode out integer,
v_errmesg out varchar2)
is
begin
v_errcode :=0;
open CurRef for
select argument_name,
data_type,
NVL(pls_type,'REF'),
in_out
from user_arguments
where upper(object_name) = upper(v_procname)
and upper(package_name) = upper(v_packagename)
order by sequence;
exception
when others then
v_errcode :=SQLCODE;
v_errmesg :=SQLERRM;
end spGetProcedureParams;
end pkgUtils;
/I'm not really sure what you are looking for. You could fetch the data into a collection like an index-by table. Whether this will improve performance depends on what you are going to do with it next.
Caching results - can't stop it
When I run the web-based report viewer
(http://url/ReportServer?<parameters>), it is caching the results. I can go
to another part of my web app and change some data, but when I go back to
the report, it doesn't reflect changes. Also, if I change the report and
redeploy from Vis. Studio and hit the same URL for the report, I don't see
the changes. {That is, until I hit the little "refresh" button on the
report viewer toolbar. The cache does "time out" eventually, but I have not
tried measuring that time.}
I've set the web site properties on the ReportServer virtual to "Expire
Content Immediately." I've also checked the reports themselves and made
sure the Execution properties have selected "Render this report with the
most recent data" and "Do not cache temporary copies of this report" -- both
are true. So why aren't my updates reflected on the next page hit
automatically?What you are seeing is IE caching issue. That is why it works (and why they
have) the refresh button. When using the URL it is the same as the IE
refresh which causes the IE cache to be used. With your URL add this to the
report url: rs:ClearSession=true
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:%23Xg56aXNFHA.3296@.TK2MSFTNGP15.phx.gbl...
> When I run the web-based report viewer
> (http://url/ReportServer?<parameters>), it is caching the results. I can
go
> to another part of my web app and change some data, but when I go back to
> the report, it doesn't reflect changes. Also, if I change the report and
> redeploy from Vis. Studio and hit the same URL for the report, I don't see
> the changes. {That is, until I hit the little "refresh" button on the
> report viewer toolbar. The cache does "time out" eventually, but I have
not
> tried measuring that time.}
> I've set the web site properties on the ReportServer virtual to "Expire
> Content Immediately." I've also checked the reports themselves and made
> sure the Execution properties have selected "Render this report with the
> most recent data" and "Do not cache temporary copies of this report" --
both
> are true. So why aren't my updates reflected on the next page hit
> automatically?
>|||I added the rs:ClearSession parameter, but now it's kicking my users out of
the system (it's the only thing that changed, so this is definitely the
source of the problem -- the web app is under forms authentication) -- it
appears to be clearing out the whole session, not just anything related to
the Report Server. This is extremely bad.
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:%23Xg56aXNFHA.3296@.TK2MSFTNGP15.phx.gbl...
> When I run the web-based report viewer
> (http://url/ReportServer?<parameters>), it is caching the results. I can
> go to another part of my web app and change some data, but when I go back
> to the report, it doesn't reflect changes. Also, if I change the report
> and redeploy from Vis. Studio and hit the same URL for the report, I don't
> see the changes. {That is, until I hit the little "refresh" button on the
> report viewer toolbar. The cache does "time out" eventually, but I have
> not tried measuring that time.}
> I've set the web site properties on the ReportServer virtual to "Expire
> Content Immediately." I've also checked the reports themselves and made
> sure the Execution properties have selected "Render this report with the
> most recent data" and "Do not cache temporary copies of this report" --
> both are true. So why aren't my updates reflected on the next page hit
> automatically?
>|||Ahh, didn't know you were doing forms authentication.
If the issue is only when you roll out a change then what you need to do is
when you roll out a change the user might need to close IE and then re-open
it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:OLCt4%23tNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> I added the rs:ClearSession parameter, but now it's kicking my users out
of
> the system (it's the only thing that changed, so this is definitely the
> source of the problem -- the web app is under forms authentication) -- it
> appears to be clearing out the whole session, not just anything related to
> the Report Server. This is extremely bad.
> "DJM" <msnews@.puddlestheshark.com> wrote in message
> news:%23Xg56aXNFHA.3296@.TK2MSFTNGP15.phx.gbl...
> > When I run the web-based report viewer
> > (http://url/ReportServer?<parameters>), it is caching the results. I
can
> > go to another part of my web app and change some data, but when I go
back
> > to the report, it doesn't reflect changes. Also, if I change the report
> > and redeploy from Vis. Studio and hit the same URL for the report, I
don't
> > see the changes. {That is, until I hit the little "refresh" button on
the
> > report viewer toolbar. The cache does "time out" eventually, but I have
> > not tried measuring that time.}
> >
> > I've set the web site properties on the ReportServer virtual to "Expire
> > Content Immediately." I've also checked the reports themselves and made
> > sure the Execution properties have selected "Render this report with the
> > most recent data" and "Do not cache temporary copies of this report" --
> > both are true. So why aren't my updates reflected on the next page hit
> > automatically?
> >
>|||It's not; it's whenever a report is run.
A little more detail:
We have a web application, let's say it's at www.url.com, which is not the
same as the server's default web site. This site is secured using Forms
Authentication. We wanted to add the Report Viewer to our application.
What we did was to add the ReportServer virtual to the www.url.com website,
and add a page to our application with a ReportViewer control and direct it
to www.url.com/ReportServer. (We copied the ReportServer folder on the
server, so we could set its configuration independent of the
defaultip/ReportServer site.) Unfortunately, we had some difficulty getting
this virtual to work with Forms Auth -- we tried the sample Forms Auth
validator, we tried just setting it to Forms Auth (hoping it would inherit
the authentication from the parent site -- it didn't, it just kept
redirecting its frame to our login page), and ultimately, we ended up just
turning authentication off completely. (Yes, this is absolutely insecure,
but we were unfortunately under "just make it work" pressure, and it was the
only way we could get the stupid login box to go away whenever a user tried
to run a report.)
When adding the rs:ClearSession to the querystring, it appears to annihilate
the Session of our web app -- the error we're seeing is actually a page that
is displayed when our web app detects the Session object is no more (which
usually only happens on a Session Timeout). As soon as we removed that
parameter (fortunately we store this in Web.config), our apparent timeout
problems went away immediately.
But we're back at this same problem where a user can run a report, go to
another part of the app and change data, come back to this same report, and
not see the updates immediately, despite the fact that all reports are set
to "render with most recent data" and "do not cache" and the
www.url.com/ReportServer virtual is set in IIS to "expire content
immediately".
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OIIcIpuNFHA.576@.TK2MSFTNGP15.phx.gbl...
> Ahh, didn't know you were doing forms authentication.
> If the issue is only when you roll out a change then what you need to do
> is
> when you roll out a change the user might need to close IE and then
> re-open
> it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "DJM" <msnews@.puddlestheshark.com> wrote in message
> news:OLCt4%23tNFHA.3704@.TK2MSFTNGP12.phx.gbl...
>> I added the rs:ClearSession parameter, but now it's kicking my users out
> of
>> the system (it's the only thing that changed, so this is definitely the
>> source of the problem -- the web app is under forms authentication) -- it
>> appears to be clearing out the whole session, not just anything related
>> to
>> the Report Server. This is extremely bad.
>> "DJM" <msnews@.puddlestheshark.com> wrote in message
>> news:%23Xg56aXNFHA.3296@.TK2MSFTNGP15.phx.gbl...
>> > When I run the web-based report viewer
>> > (http://url/ReportServer?<parameters>), it is caching the results. I
> can
>> > go to another part of my web app and change some data, but when I go
> back
>> > to the report, it doesn't reflect changes. Also, if I change the
>> > report
>> > and redeploy from Vis. Studio and hit the same URL for the report, I
> don't
>> > see the changes. {That is, until I hit the little "refresh" button on
> the
>> > report viewer toolbar. The cache does "time out" eventually, but I
>> > have
>> > not tried measuring that time.}
>> >
>> > I've set the web site properties on the ReportServer virtual to "Expire
>> > Content Immediately." I've also checked the reports themselves and
>> > made
>> > sure the Execution properties have selected "Render this report with
>> > the
>> > most recent data" and "Do not cache temporary copies of this report" --
>> > both are true. So why aren't my updates reflected on the next page hit
>> > automatically?
>> >
>>
>|||I have not done this so I am fresh out of ideas for you. Hopefully someone
else can jump in.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:OYLoaFvNFHA.204@.TK2MSFTNGP15.phx.gbl...
> It's not; it's whenever a report is run.
> A little more detail:
> We have a web application, let's say it's at www.url.com, which is not the
> same as the server's default web site. This site is secured using Forms
> Authentication. We wanted to add the Report Viewer to our application.
> What we did was to add the ReportServer virtual to the www.url.com
website,
> and add a page to our application with a ReportViewer control and direct
it
> to www.url.com/ReportServer. (We copied the ReportServer folder on the
> server, so we could set its configuration independent of the
> defaultip/ReportServer site.) Unfortunately, we had some difficulty
getting
> this virtual to work with Forms Auth -- we tried the sample Forms Auth
> validator, we tried just setting it to Forms Auth (hoping it would inherit
> the authentication from the parent site -- it didn't, it just kept
> redirecting its frame to our login page), and ultimately, we ended up just
> turning authentication off completely. (Yes, this is absolutely insecure,
> but we were unfortunately under "just make it work" pressure, and it was
the
> only way we could get the stupid login box to go away whenever a user
tried
> to run a report.)
> When adding the rs:ClearSession to the querystring, it appears to
annihilate
> the Session of our web app -- the error we're seeing is actually a page
that
> is displayed when our web app detects the Session object is no more (which
> usually only happens on a Session Timeout). As soon as we removed that
> parameter (fortunately we store this in Web.config), our apparent timeout
> problems went away immediately.
> But we're back at this same problem where a user can run a report, go to
> another part of the app and change data, come back to this same report,
and
> not see the updates immediately, despite the fact that all reports are set
> to "render with most recent data" and "do not cache" and the
> www.url.com/ReportServer virtual is set in IIS to "expire content
> immediately".
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OIIcIpuNFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Ahh, didn't know you were doing forms authentication.
> >
> > If the issue is only when you roll out a change then what you need to do
> > is
> > when you roll out a change the user might need to close IE and then
> > re-open
> > it.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "DJM" <msnews@.puddlestheshark.com> wrote in message
> > news:OLCt4%23tNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> >> I added the rs:ClearSession parameter, but now it's kicking my users
out
> > of
> >> the system (it's the only thing that changed, so this is definitely the
> >> source of the problem -- the web app is under forms authentication) --
it
> >> appears to be clearing out the whole session, not just anything related
> >> to
> >> the Report Server. This is extremely bad.
> >>
> >> "DJM" <msnews@.puddlestheshark.com> wrote in message
> >> news:%23Xg56aXNFHA.3296@.TK2MSFTNGP15.phx.gbl...
> >> > When I run the web-based report viewer
> >> > (http://url/ReportServer?<parameters>), it is caching the results. I
> > can
> >> > go to another part of my web app and change some data, but when I go
> > back
> >> > to the report, it doesn't reflect changes. Also, if I change the
> >> > report
> >> > and redeploy from Vis. Studio and hit the same URL for the report, I
> > don't
> >> > see the changes. {That is, until I hit the little "refresh" button
on
> > the
> >> > report viewer toolbar. The cache does "time out" eventually, but I
> >> > have
> >> > not tried measuring that time.}
> >> >
> >> > I've set the web site properties on the ReportServer virtual to
"Expire
> >> > Content Immediately." I've also checked the reports themselves and
> >> > made
> >> > sure the Execution properties have selected "Render this report with
> >> > the
> >> > most recent data" and "Do not cache temporary copies of this
report" --
> >> > both are true. So why aren't my updates reflected on the next page
hit
> >> > automatically?
> >> >
> >>
> >>
> >
> >
>|||Fortunately, in my organization we are able to use rs:ClearSession for
our current needs. If you can't use rs:ClearSession, the workaround we
have come up with is to add a dummy parameter to the URL. IE will only
show you the stale, cached copy of the report if the entire URL is
character-for-character identical. If you increment the dummy
parameter every time you click the link, then the URL's will be
different, and IE won't cache.
Creating the unique dummy parameter does require a tiny bit of
client-side JavaScript programming -- you can't use a simple static
HTML link. This was not a problem for us since we were usually
launching reports from buttons that already had code.
Hope this helps,
Ted
Bruce L-C [MVP] wrote:
> I have not done this so I am fresh out of ideas for you. Hopefully
someone
> else can jump in.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "DJM" <msnews@.puddlestheshark.com> wrote in message
> news:OYLoaFvNFHA.204@.TK2MSFTNGP15.phx.gbl...
> > It's not; it's whenever a report is run.
> >
> > A little more detail:
> > We have a web application, let's say it's at www.url.com, which is
not the
> > same as the server's default web site. This site is secured using
Forms
> > Authentication. We wanted to add the Report Viewer to our
application.
> > What we did was to add the ReportServer virtual to the www.url.com
> website,
> > and add a page to our application with a ReportViewer control and
direct
> it
> > to www.url.com/ReportServer. (We copied the ReportServer folder on
the
> > server, so we could set its configuration independent of the
> > defaultip/ReportServer site.) Unfortunately, we had some
difficulty
> getting
> > this virtual to work with Forms Auth -- we tried the sample Forms
Auth
> > validator, we tried just setting it to Forms Auth (hoping it would
inherit
> > the authentication from the parent site -- it didn't, it just kept
> > redirecting its frame to our login page), and ultimately, we ended
up just
> > turning authentication off completely. (Yes, this is absolutely
insecure,
> > but we were unfortunately under "just make it work" pressure, and
it was
> the
> > only way we could get the stupid login box to go away whenever a
user
> tried
> > to run a report.)
> >
> > When adding the rs:ClearSession to the querystring, it appears to
> annihilate
> > the Session of our web app -- the error we're seeing is actually a
page
> that
> > is displayed when our web app detects the Session object is no more
(which
> > usually only happens on a Session Timeout). As soon as we removed
that
> > parameter (fortunately we store this in Web.config), our apparent
timeout
> > problems went away immediately.
> >
> > But we're back at this same problem where a user can run a report,
go to
> > another part of the app and change data, come back to this same
report,
> and
> > not see the updates immediately, despite the fact that all reports
are set
> > to "render with most recent data" and "do not cache" and the
> > www.url.com/ReportServer virtual is set in IIS to "expire content
> > immediately".
> >|||Ted, that is a cool workaround. Creative solution.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ted K" <tkyi@.infotechsm.com> wrote in message
news:1112393836.495225.113290@.f14g2000cwb.googlegroups.com...
> Fortunately, in my organization we are able to use rs:ClearSession for
> our current needs. If you can't use rs:ClearSession, the workaround we
> have come up with is to add a dummy parameter to the URL. IE will only
> show you the stale, cached copy of the report if the entire URL is
> character-for-character identical. If you increment the dummy
> parameter every time you click the link, then the URL's will be
> different, and IE won't cache.
> Creating the unique dummy parameter does require a tiny bit of
> client-side JavaScript programming -- you can't use a simple static
> HTML link. This was not a problem for us since we were usually
> launching reports from buttons that already had code.
> Hope this helps,
> Ted
> Bruce L-C [MVP] wrote:
> > I have not done this so I am fresh out of ideas for you. Hopefully
> someone
> > else can jump in.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "DJM" <msnews@.puddlestheshark.com> wrote in message
> > news:OYLoaFvNFHA.204@.TK2MSFTNGP15.phx.gbl...
> > > It's not; it's whenever a report is run.
> > >
> > > A little more detail:
> > > We have a web application, let's say it's at www.url.com, which is
> not the
> > > same as the server's default web site. This site is secured using
> Forms
> > > Authentication. We wanted to add the Report Viewer to our
> application.
> > > What we did was to add the ReportServer virtual to the www.url.com
> > website,
> > > and add a page to our application with a ReportViewer control and
> direct
> > it
> > > to www.url.com/ReportServer. (We copied the ReportServer folder on
> the
> > > server, so we could set its configuration independent of the
> > > defaultip/ReportServer site.) Unfortunately, we had some
> difficulty
> > getting
> > > this virtual to work with Forms Auth -- we tried the sample Forms
> Auth
> > > validator, we tried just setting it to Forms Auth (hoping it would
> inherit
> > > the authentication from the parent site -- it didn't, it just kept
> > > redirecting its frame to our login page), and ultimately, we ended
> up just
> > > turning authentication off completely. (Yes, this is absolutely
> insecure,
> > > but we were unfortunately under "just make it work" pressure, and
> it was
> > the
> > > only way we could get the stupid login box to go away whenever a
> user
> > tried
> > > to run a report.)
> > >
> > > When adding the rs:ClearSession to the querystring, it appears to
> > annihilate
> > > the Session of our web app -- the error we're seeing is actually a
> page
> > that
> > > is displayed when our web app detects the Session object is no more
> (which
> > > usually only happens on a Session Timeout). As soon as we removed
> that
> > > parameter (fortunately we store this in Web.config), our apparent
> timeout
> > > problems went away immediately.
> > >
> > > But we're back at this same problem where a user can run a report,
> go to
> > > another part of the app and change data, come back to this same
> report,
> > and
> > > not see the updates immediately, despite the fact that all reports
> are set
> > > to "render with most recent data" and "do not cache" and the
> > > www.url.com/ReportServer virtual is set in IIS to "expire content
> > > immediately".
> > >
>|||An interesting idea, but unfortunately it doesn't work. I added a "&rc:u="
+ DateTime.Now.Ticks.ToString() to the querystring, and it still shows me
the cached copy of the data. (If I try it without the "rc:" prefix, it
assumes a report parameter, and unless I add a parameter to all of our
reports, I get an error message from Report Server about passing in a
parameter that doesn't exist.)
"Ted K" <tkyi@.infotechsm.com> wrote in message
news:1112393836.495225.113290@.f14g2000cwb.googlegroups.com...
> Fortunately, in my organization we are able to use rs:ClearSession for
> our current needs. If you can't use rs:ClearSession, the workaround we
> have come up with is to add a dummy parameter to the URL. IE will only
> show you the stale, cached copy of the report if the entire URL is
> character-for-character identical. If you increment the dummy
> parameter every time you click the link, then the URL's will be
> different, and IE won't cache.
> Creating the unique dummy parameter does require a tiny bit of
> client-side JavaScript programming -- you can't use a simple static
> HTML link. This was not a problem for us since we were usually
> launching reports from buttons that already had code.
> Hope this helps,
> Ted
> Bruce L-C [MVP] wrote:
>> I have not done this so I am fresh out of ideas for you. Hopefully
> someone
>> else can jump in.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "DJM" <msnews@.puddlestheshark.com> wrote in message
>> news:OYLoaFvNFHA.204@.TK2MSFTNGP15.phx.gbl...
>> > It's not; it's whenever a report is run.
>> >
>> > A little more detail:
>> > We have a web application, let's say it's at www.url.com, which is
> not the
>> > same as the server's default web site. This site is secured using
> Forms
>> > Authentication. We wanted to add the Report Viewer to our
> application.
>> > What we did was to add the ReportServer virtual to the www.url.com
>> website,
>> > and add a page to our application with a ReportViewer control and
> direct
>> it
>> > to www.url.com/ReportServer. (We copied the ReportServer folder on
> the
>> > server, so we could set its configuration independent of the
>> > defaultip/ReportServer site.) Unfortunately, we had some
> difficulty
>> getting
>> > this virtual to work with Forms Auth -- we tried the sample Forms
> Auth
>> > validator, we tried just setting it to Forms Auth (hoping it would
> inherit
>> > the authentication from the parent site -- it didn't, it just kept
>> > redirecting its frame to our login page), and ultimately, we ended
> up just
>> > turning authentication off completely. (Yes, this is absolutely
> insecure,
>> > but we were unfortunately under "just make it work" pressure, and
> it was
>> the
>> > only way we could get the stupid login box to go away whenever a
> user
>> tried
>> > to run a report.)
>> >
>> > When adding the rs:ClearSession to the querystring, it appears to
>> annihilate
>> > the Session of our web app -- the error we're seeing is actually a
> page
>> that
>> > is displayed when our web app detects the Session object is no more
> (which
>> > usually only happens on a Session Timeout). As soon as we removed
> that
>> > parameter (fortunately we store this in Web.config), our apparent
> timeout
>> > problems went away immediately.
>> >
>> > But we're back at this same problem where a user can run a report,
> go to
>> > another part of the app and change data, come back to this same
> report,
>> and
>> > not see the updates immediately, despite the fact that all reports
> are set
>> > to "render with most recent data" and "do not cache" and the
>> > www.url.com/ReportServer virtual is set in IIS to "expire content
>> > immediately".
>> >
>|||Sorry, DJM, I misspoke a little in my last post. Let me clarify. As
Bruce has explained, RS has to maintain session so it can handle
certain cases, such as moving to the next page or exporting to a new
format. For those cases, it is desirable that the report remain
unchanged for that user.
In order to implement the workaround, you have to modify the part of
the URL that RS cares about. So, yes, when I said a dummy parameter, I
really meant adding an extra parameter to your report. I realized that
my last post makes it sound like you can add any characters to the URL.
If you already have a zillion reports, then of course it will be a pain
to add a parameter to all of them. (I'd edit the XML directly, and
maybe try to set up some kind of smart search and replace, instead of
using Visual Studio by hand.) Unless you can resolve your
authentication issue, I think this is the way for you to go.
Ted
DJM wrote:
> An interesting idea, but unfortunately it doesn't work. I added a
"&rc:u="
> + DateTime.Now.Ticks.ToString() to the querystring, and it still
shows me
> the cached copy of the data. (If I try it without the "rc:" prefix,
it
> assumes a report parameter, and unless I add a parameter to all of
our
> reports, I get an error message from Report Server about passing in a
> parameter that doesn't exist.)
> "Ted K" <tkyi@.infotechsm.com> wrote in message
> news:1112393836.495225.113290@.f14g2000cwb.googlegroups.com...
> > Fortunately, in my organization we are able to use rs:ClearSession
for
> > our current needs. If you can't use rs:ClearSession, the
workaround we
> > have come up with is to add a dummy parameter to the URL. IE will
only
> > show you the stale, cached copy of the report if the entire URL is
> > character-for-character identical. If you increment the dummy
> > parameter every time you click the link, then the URL's will be
> > different, and IE won't cache.
> >
> > Creating the unique dummy parameter does require a tiny bit of
> > client-side JavaScript programming -- you can't use a simple static
> > HTML link. This was not a problem for us since we were usually
> > launching reports from buttons that already had code.
> >
> > Hope this helps,
> > Ted
> >|||Alright, I'll give that a try then. I still consider this a RS bug.
"Ted K" <tkyi@.infotechsm.com> wrote in message
news:1112633029.297136.68110@.g14g2000cwa.googlegroups.com...
> Sorry, DJM, I misspoke a little in my last post. Let me clarify. As
> Bruce has explained, RS has to maintain session so it can handle
> certain cases, such as moving to the next page or exporting to a new
> format. For those cases, it is desirable that the report remain
> unchanged for that user.
> In order to implement the workaround, you have to modify the part of
> the URL that RS cares about. So, yes, when I said a dummy parameter, I
> really meant adding an extra parameter to your report. I realized that
> my last post makes it sound like you can add any characters to the URL.
> If you already have a zillion reports, then of course it will be a pain
> to add a parameter to all of them. (I'd edit the XML directly, and
> maybe try to set up some kind of smart search and replace, instead of
> using Visual Studio by hand.) Unless you can resolve your
> authentication issue, I think this is the way for you to go.
> Ted
> DJM wrote:
>> An interesting idea, but unfortunately it doesn't work. I added a
> "&rc:u="
>> + DateTime.Now.Ticks.ToString() to the querystring, and it still
> shows me
>> the cached copy of the data. (If I try it without the "rc:" prefix,
> it
>> assumes a report parameter, and unless I add a parameter to all of
> our
>> reports, I get an error message from Report Server about passing in a
>> parameter that doesn't exist.)
>> "Ted K" <tkyi@.infotechsm.com> wrote in message
>> news:1112393836.495225.113290@.f14g2000cwb.googlegroups.com...
>> > Fortunately, in my organization we are able to use rs:ClearSession
> for
>> > our current needs. If you can't use rs:ClearSession, the
> workaround we
>> > have come up with is to add a dummy parameter to the URL. IE will
> only
>> > show you the stale, cached copy of the report if the entire URL is
>> > character-for-character identical. If you increment the dummy
>> > parameter every time you click the link, then the URL's will be
>> > different, and IE won't cache.
>> >
>> > Creating the unique dummy parameter does require a tiny bit of
>> > client-side JavaScript programming -- you can't use a simple static
>> > HTML link. This was not a problem for us since we were usually
>> > launching reports from buttons that already had code.
>> >
>> > Hope this helps,
>> > Ted
>> >
>|||Ok, that worked. I ended up having to spend a couple days and doing a lot
of it by hand (for some reason, I couldn't navigate the XML with code very
easily - SelectNodes didn't want to find nodes I knew existed, even when I
specified the correct namespace), but adding a text parameter and passing in
DateTime.Now.Ticks.ToString() on execute (thankfully there are only two
report viewing functions in our app) now forces the reports to pull current
data. Thanks for the help!
"Ted K" <tkyi@.infotechsm.com> wrote in message
news:1112633029.297136.68110@.g14g2000cwa.googlegroups.com...
> Sorry, DJM, I misspoke a little in my last post. Let me clarify. As
> Bruce has explained, RS has to maintain session so it can handle
> certain cases, such as moving to the next page or exporting to a new
> format. For those cases, it is desirable that the report remain
> unchanged for that user.
> In order to implement the workaround, you have to modify the part of
> the URL that RS cares about. So, yes, when I said a dummy parameter, I
> really meant adding an extra parameter to your report. I realized that
> my last post makes it sound like you can add any characters to the URL.
> If you already have a zillion reports, then of course it will be a pain
> to add a parameter to all of them. (I'd edit the XML directly, and
> maybe try to set up some kind of smart search and replace, instead of
> using Visual Studio by hand.) Unless you can resolve your
> authentication issue, I think this is the way for you to go.
> Ted
> DJM wrote:
>> An interesting idea, but unfortunately it doesn't work. I added a
> "&rc:u="
>> + DateTime.Now.Ticks.ToString() to the querystring, and it still
> shows me
>> the cached copy of the data. (If I try it without the "rc:" prefix,
> it
>> assumes a report parameter, and unless I add a parameter to all of
> our
>> reports, I get an error message from Report Server about passing in a
>> parameter that doesn't exist.)
>> "Ted K" <tkyi@.infotechsm.com> wrote in message
>> news:1112393836.495225.113290@.f14g2000cwb.googlegroups.com...
>> > Fortunately, in my organization we are able to use rs:ClearSession
> for
>> > our current needs. If you can't use rs:ClearSession, the
> workaround we
>> > have come up with is to add a dummy parameter to the URL. IE will
> only
>> > show you the stale, cached copy of the report if the entire URL is
>> > character-for-character identical. If you increment the dummy
>> > parameter every time you click the link, then the URL's will be
>> > different, and IE won't cache.
>> >
>> > Creating the unique dummy parameter does require a tiny bit of
>> > client-side JavaScript programming -- you can't use a simple static
>> > HTML link. This was not a problem for us since we were usually
>> > launching reports from buttons that already had code.
>> >
>> > Hope this helps,
>> > Ted
>> >
>
(http://url/ReportServer?<parameters>), it is caching the results. I can go
to another part of my web app and change some data, but when I go back to
the report, it doesn't reflect changes. Also, if I change the report and
redeploy from Vis. Studio and hit the same URL for the report, I don't see
the changes. {That is, until I hit the little "refresh" button on the
report viewer toolbar. The cache does "time out" eventually, but I have not
tried measuring that time.}
I've set the web site properties on the ReportServer virtual to "Expire
Content Immediately." I've also checked the reports themselves and made
sure the Execution properties have selected "Render this report with the
most recent data" and "Do not cache temporary copies of this report" -- both
are true. So why aren't my updates reflected on the next page hit
automatically?What you are seeing is IE caching issue. That is why it works (and why they
have) the refresh button. When using the URL it is the same as the IE
refresh which causes the IE cache to be used. With your URL add this to the
report url: rs:ClearSession=true
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:%23Xg56aXNFHA.3296@.TK2MSFTNGP15.phx.gbl...
> When I run the web-based report viewer
> (http://url/ReportServer?<parameters>), it is caching the results. I can
go
> to another part of my web app and change some data, but when I go back to
> the report, it doesn't reflect changes. Also, if I change the report and
> redeploy from Vis. Studio and hit the same URL for the report, I don't see
> the changes. {That is, until I hit the little "refresh" button on the
> report viewer toolbar. The cache does "time out" eventually, but I have
not
> tried measuring that time.}
> I've set the web site properties on the ReportServer virtual to "Expire
> Content Immediately." I've also checked the reports themselves and made
> sure the Execution properties have selected "Render this report with the
> most recent data" and "Do not cache temporary copies of this report" --
both
> are true. So why aren't my updates reflected on the next page hit
> automatically?
>|||I added the rs:ClearSession parameter, but now it's kicking my users out of
the system (it's the only thing that changed, so this is definitely the
source of the problem -- the web app is under forms authentication) -- it
appears to be clearing out the whole session, not just anything related to
the Report Server. This is extremely bad.
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:%23Xg56aXNFHA.3296@.TK2MSFTNGP15.phx.gbl...
> When I run the web-based report viewer
> (http://url/ReportServer?<parameters>), it is caching the results. I can
> go to another part of my web app and change some data, but when I go back
> to the report, it doesn't reflect changes. Also, if I change the report
> and redeploy from Vis. Studio and hit the same URL for the report, I don't
> see the changes. {That is, until I hit the little "refresh" button on the
> report viewer toolbar. The cache does "time out" eventually, but I have
> not tried measuring that time.}
> I've set the web site properties on the ReportServer virtual to "Expire
> Content Immediately." I've also checked the reports themselves and made
> sure the Execution properties have selected "Render this report with the
> most recent data" and "Do not cache temporary copies of this report" --
> both are true. So why aren't my updates reflected on the next page hit
> automatically?
>|||Ahh, didn't know you were doing forms authentication.
If the issue is only when you roll out a change then what you need to do is
when you roll out a change the user might need to close IE and then re-open
it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:OLCt4%23tNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> I added the rs:ClearSession parameter, but now it's kicking my users out
of
> the system (it's the only thing that changed, so this is definitely the
> source of the problem -- the web app is under forms authentication) -- it
> appears to be clearing out the whole session, not just anything related to
> the Report Server. This is extremely bad.
> "DJM" <msnews@.puddlestheshark.com> wrote in message
> news:%23Xg56aXNFHA.3296@.TK2MSFTNGP15.phx.gbl...
> > When I run the web-based report viewer
> > (http://url/ReportServer?<parameters>), it is caching the results. I
can
> > go to another part of my web app and change some data, but when I go
back
> > to the report, it doesn't reflect changes. Also, if I change the report
> > and redeploy from Vis. Studio and hit the same URL for the report, I
don't
> > see the changes. {That is, until I hit the little "refresh" button on
the
> > report viewer toolbar. The cache does "time out" eventually, but I have
> > not tried measuring that time.}
> >
> > I've set the web site properties on the ReportServer virtual to "Expire
> > Content Immediately." I've also checked the reports themselves and made
> > sure the Execution properties have selected "Render this report with the
> > most recent data" and "Do not cache temporary copies of this report" --
> > both are true. So why aren't my updates reflected on the next page hit
> > automatically?
> >
>|||It's not; it's whenever a report is run.
A little more detail:
We have a web application, let's say it's at www.url.com, which is not the
same as the server's default web site. This site is secured using Forms
Authentication. We wanted to add the Report Viewer to our application.
What we did was to add the ReportServer virtual to the www.url.com website,
and add a page to our application with a ReportViewer control and direct it
to www.url.com/ReportServer. (We copied the ReportServer folder on the
server, so we could set its configuration independent of the
defaultip/ReportServer site.) Unfortunately, we had some difficulty getting
this virtual to work with Forms Auth -- we tried the sample Forms Auth
validator, we tried just setting it to Forms Auth (hoping it would inherit
the authentication from the parent site -- it didn't, it just kept
redirecting its frame to our login page), and ultimately, we ended up just
turning authentication off completely. (Yes, this is absolutely insecure,
but we were unfortunately under "just make it work" pressure, and it was the
only way we could get the stupid login box to go away whenever a user tried
to run a report.)
When adding the rs:ClearSession to the querystring, it appears to annihilate
the Session of our web app -- the error we're seeing is actually a page that
is displayed when our web app detects the Session object is no more (which
usually only happens on a Session Timeout). As soon as we removed that
parameter (fortunately we store this in Web.config), our apparent timeout
problems went away immediately.
But we're back at this same problem where a user can run a report, go to
another part of the app and change data, come back to this same report, and
not see the updates immediately, despite the fact that all reports are set
to "render with most recent data" and "do not cache" and the
www.url.com/ReportServer virtual is set in IIS to "expire content
immediately".
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OIIcIpuNFHA.576@.TK2MSFTNGP15.phx.gbl...
> Ahh, didn't know you were doing forms authentication.
> If the issue is only when you roll out a change then what you need to do
> is
> when you roll out a change the user might need to close IE and then
> re-open
> it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "DJM" <msnews@.puddlestheshark.com> wrote in message
> news:OLCt4%23tNFHA.3704@.TK2MSFTNGP12.phx.gbl...
>> I added the rs:ClearSession parameter, but now it's kicking my users out
> of
>> the system (it's the only thing that changed, so this is definitely the
>> source of the problem -- the web app is under forms authentication) -- it
>> appears to be clearing out the whole session, not just anything related
>> to
>> the Report Server. This is extremely bad.
>> "DJM" <msnews@.puddlestheshark.com> wrote in message
>> news:%23Xg56aXNFHA.3296@.TK2MSFTNGP15.phx.gbl...
>> > When I run the web-based report viewer
>> > (http://url/ReportServer?<parameters>), it is caching the results. I
> can
>> > go to another part of my web app and change some data, but when I go
> back
>> > to the report, it doesn't reflect changes. Also, if I change the
>> > report
>> > and redeploy from Vis. Studio and hit the same URL for the report, I
> don't
>> > see the changes. {That is, until I hit the little "refresh" button on
> the
>> > report viewer toolbar. The cache does "time out" eventually, but I
>> > have
>> > not tried measuring that time.}
>> >
>> > I've set the web site properties on the ReportServer virtual to "Expire
>> > Content Immediately." I've also checked the reports themselves and
>> > made
>> > sure the Execution properties have selected "Render this report with
>> > the
>> > most recent data" and "Do not cache temporary copies of this report" --
>> > both are true. So why aren't my updates reflected on the next page hit
>> > automatically?
>> >
>>
>|||I have not done this so I am fresh out of ideas for you. Hopefully someone
else can jump in.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:OYLoaFvNFHA.204@.TK2MSFTNGP15.phx.gbl...
> It's not; it's whenever a report is run.
> A little more detail:
> We have a web application, let's say it's at www.url.com, which is not the
> same as the server's default web site. This site is secured using Forms
> Authentication. We wanted to add the Report Viewer to our application.
> What we did was to add the ReportServer virtual to the www.url.com
website,
> and add a page to our application with a ReportViewer control and direct
it
> to www.url.com/ReportServer. (We copied the ReportServer folder on the
> server, so we could set its configuration independent of the
> defaultip/ReportServer site.) Unfortunately, we had some difficulty
getting
> this virtual to work with Forms Auth -- we tried the sample Forms Auth
> validator, we tried just setting it to Forms Auth (hoping it would inherit
> the authentication from the parent site -- it didn't, it just kept
> redirecting its frame to our login page), and ultimately, we ended up just
> turning authentication off completely. (Yes, this is absolutely insecure,
> but we were unfortunately under "just make it work" pressure, and it was
the
> only way we could get the stupid login box to go away whenever a user
tried
> to run a report.)
> When adding the rs:ClearSession to the querystring, it appears to
annihilate
> the Session of our web app -- the error we're seeing is actually a page
that
> is displayed when our web app detects the Session object is no more (which
> usually only happens on a Session Timeout). As soon as we removed that
> parameter (fortunately we store this in Web.config), our apparent timeout
> problems went away immediately.
> But we're back at this same problem where a user can run a report, go to
> another part of the app and change data, come back to this same report,
and
> not see the updates immediately, despite the fact that all reports are set
> to "render with most recent data" and "do not cache" and the
> www.url.com/ReportServer virtual is set in IIS to "expire content
> immediately".
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OIIcIpuNFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Ahh, didn't know you were doing forms authentication.
> >
> > If the issue is only when you roll out a change then what you need to do
> > is
> > when you roll out a change the user might need to close IE and then
> > re-open
> > it.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "DJM" <msnews@.puddlestheshark.com> wrote in message
> > news:OLCt4%23tNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> >> I added the rs:ClearSession parameter, but now it's kicking my users
out
> > of
> >> the system (it's the only thing that changed, so this is definitely the
> >> source of the problem -- the web app is under forms authentication) --
it
> >> appears to be clearing out the whole session, not just anything related
> >> to
> >> the Report Server. This is extremely bad.
> >>
> >> "DJM" <msnews@.puddlestheshark.com> wrote in message
> >> news:%23Xg56aXNFHA.3296@.TK2MSFTNGP15.phx.gbl...
> >> > When I run the web-based report viewer
> >> > (http://url/ReportServer?<parameters>), it is caching the results. I
> > can
> >> > go to another part of my web app and change some data, but when I go
> > back
> >> > to the report, it doesn't reflect changes. Also, if I change the
> >> > report
> >> > and redeploy from Vis. Studio and hit the same URL for the report, I
> > don't
> >> > see the changes. {That is, until I hit the little "refresh" button
on
> > the
> >> > report viewer toolbar. The cache does "time out" eventually, but I
> >> > have
> >> > not tried measuring that time.}
> >> >
> >> > I've set the web site properties on the ReportServer virtual to
"Expire
> >> > Content Immediately." I've also checked the reports themselves and
> >> > made
> >> > sure the Execution properties have selected "Render this report with
> >> > the
> >> > most recent data" and "Do not cache temporary copies of this
report" --
> >> > both are true. So why aren't my updates reflected on the next page
hit
> >> > automatically?
> >> >
> >>
> >>
> >
> >
>|||Fortunately, in my organization we are able to use rs:ClearSession for
our current needs. If you can't use rs:ClearSession, the workaround we
have come up with is to add a dummy parameter to the URL. IE will only
show you the stale, cached copy of the report if the entire URL is
character-for-character identical. If you increment the dummy
parameter every time you click the link, then the URL's will be
different, and IE won't cache.
Creating the unique dummy parameter does require a tiny bit of
client-side JavaScript programming -- you can't use a simple static
HTML link. This was not a problem for us since we were usually
launching reports from buttons that already had code.
Hope this helps,
Ted
Bruce L-C [MVP] wrote:
> I have not done this so I am fresh out of ideas for you. Hopefully
someone
> else can jump in.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "DJM" <msnews@.puddlestheshark.com> wrote in message
> news:OYLoaFvNFHA.204@.TK2MSFTNGP15.phx.gbl...
> > It's not; it's whenever a report is run.
> >
> > A little more detail:
> > We have a web application, let's say it's at www.url.com, which is
not the
> > same as the server's default web site. This site is secured using
Forms
> > Authentication. We wanted to add the Report Viewer to our
application.
> > What we did was to add the ReportServer virtual to the www.url.com
> website,
> > and add a page to our application with a ReportViewer control and
direct
> it
> > to www.url.com/ReportServer. (We copied the ReportServer folder on
the
> > server, so we could set its configuration independent of the
> > defaultip/ReportServer site.) Unfortunately, we had some
difficulty
> getting
> > this virtual to work with Forms Auth -- we tried the sample Forms
Auth
> > validator, we tried just setting it to Forms Auth (hoping it would
inherit
> > the authentication from the parent site -- it didn't, it just kept
> > redirecting its frame to our login page), and ultimately, we ended
up just
> > turning authentication off completely. (Yes, this is absolutely
insecure,
> > but we were unfortunately under "just make it work" pressure, and
it was
> the
> > only way we could get the stupid login box to go away whenever a
user
> tried
> > to run a report.)
> >
> > When adding the rs:ClearSession to the querystring, it appears to
> annihilate
> > the Session of our web app -- the error we're seeing is actually a
page
> that
> > is displayed when our web app detects the Session object is no more
(which
> > usually only happens on a Session Timeout). As soon as we removed
that
> > parameter (fortunately we store this in Web.config), our apparent
timeout
> > problems went away immediately.
> >
> > But we're back at this same problem where a user can run a report,
go to
> > another part of the app and change data, come back to this same
report,
> and
> > not see the updates immediately, despite the fact that all reports
are set
> > to "render with most recent data" and "do not cache" and the
> > www.url.com/ReportServer virtual is set in IIS to "expire content
> > immediately".
> >|||Ted, that is a cool workaround. Creative solution.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ted K" <tkyi@.infotechsm.com> wrote in message
news:1112393836.495225.113290@.f14g2000cwb.googlegroups.com...
> Fortunately, in my organization we are able to use rs:ClearSession for
> our current needs. If you can't use rs:ClearSession, the workaround we
> have come up with is to add a dummy parameter to the URL. IE will only
> show you the stale, cached copy of the report if the entire URL is
> character-for-character identical. If you increment the dummy
> parameter every time you click the link, then the URL's will be
> different, and IE won't cache.
> Creating the unique dummy parameter does require a tiny bit of
> client-side JavaScript programming -- you can't use a simple static
> HTML link. This was not a problem for us since we were usually
> launching reports from buttons that already had code.
> Hope this helps,
> Ted
> Bruce L-C [MVP] wrote:
> > I have not done this so I am fresh out of ideas for you. Hopefully
> someone
> > else can jump in.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "DJM" <msnews@.puddlestheshark.com> wrote in message
> > news:OYLoaFvNFHA.204@.TK2MSFTNGP15.phx.gbl...
> > > It's not; it's whenever a report is run.
> > >
> > > A little more detail:
> > > We have a web application, let's say it's at www.url.com, which is
> not the
> > > same as the server's default web site. This site is secured using
> Forms
> > > Authentication. We wanted to add the Report Viewer to our
> application.
> > > What we did was to add the ReportServer virtual to the www.url.com
> > website,
> > > and add a page to our application with a ReportViewer control and
> direct
> > it
> > > to www.url.com/ReportServer. (We copied the ReportServer folder on
> the
> > > server, so we could set its configuration independent of the
> > > defaultip/ReportServer site.) Unfortunately, we had some
> difficulty
> > getting
> > > this virtual to work with Forms Auth -- we tried the sample Forms
> Auth
> > > validator, we tried just setting it to Forms Auth (hoping it would
> inherit
> > > the authentication from the parent site -- it didn't, it just kept
> > > redirecting its frame to our login page), and ultimately, we ended
> up just
> > > turning authentication off completely. (Yes, this is absolutely
> insecure,
> > > but we were unfortunately under "just make it work" pressure, and
> it was
> > the
> > > only way we could get the stupid login box to go away whenever a
> user
> > tried
> > > to run a report.)
> > >
> > > When adding the rs:ClearSession to the querystring, it appears to
> > annihilate
> > > the Session of our web app -- the error we're seeing is actually a
> page
> > that
> > > is displayed when our web app detects the Session object is no more
> (which
> > > usually only happens on a Session Timeout). As soon as we removed
> that
> > > parameter (fortunately we store this in Web.config), our apparent
> timeout
> > > problems went away immediately.
> > >
> > > But we're back at this same problem where a user can run a report,
> go to
> > > another part of the app and change data, come back to this same
> report,
> > and
> > > not see the updates immediately, despite the fact that all reports
> are set
> > > to "render with most recent data" and "do not cache" and the
> > > www.url.com/ReportServer virtual is set in IIS to "expire content
> > > immediately".
> > >
>|||An interesting idea, but unfortunately it doesn't work. I added a "&rc:u="
+ DateTime.Now.Ticks.ToString() to the querystring, and it still shows me
the cached copy of the data. (If I try it without the "rc:" prefix, it
assumes a report parameter, and unless I add a parameter to all of our
reports, I get an error message from Report Server about passing in a
parameter that doesn't exist.)
"Ted K" <tkyi@.infotechsm.com> wrote in message
news:1112393836.495225.113290@.f14g2000cwb.googlegroups.com...
> Fortunately, in my organization we are able to use rs:ClearSession for
> our current needs. If you can't use rs:ClearSession, the workaround we
> have come up with is to add a dummy parameter to the URL. IE will only
> show you the stale, cached copy of the report if the entire URL is
> character-for-character identical. If you increment the dummy
> parameter every time you click the link, then the URL's will be
> different, and IE won't cache.
> Creating the unique dummy parameter does require a tiny bit of
> client-side JavaScript programming -- you can't use a simple static
> HTML link. This was not a problem for us since we were usually
> launching reports from buttons that already had code.
> Hope this helps,
> Ted
> Bruce L-C [MVP] wrote:
>> I have not done this so I am fresh out of ideas for you. Hopefully
> someone
>> else can jump in.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "DJM" <msnews@.puddlestheshark.com> wrote in message
>> news:OYLoaFvNFHA.204@.TK2MSFTNGP15.phx.gbl...
>> > It's not; it's whenever a report is run.
>> >
>> > A little more detail:
>> > We have a web application, let's say it's at www.url.com, which is
> not the
>> > same as the server's default web site. This site is secured using
> Forms
>> > Authentication. We wanted to add the Report Viewer to our
> application.
>> > What we did was to add the ReportServer virtual to the www.url.com
>> website,
>> > and add a page to our application with a ReportViewer control and
> direct
>> it
>> > to www.url.com/ReportServer. (We copied the ReportServer folder on
> the
>> > server, so we could set its configuration independent of the
>> > defaultip/ReportServer site.) Unfortunately, we had some
> difficulty
>> getting
>> > this virtual to work with Forms Auth -- we tried the sample Forms
> Auth
>> > validator, we tried just setting it to Forms Auth (hoping it would
> inherit
>> > the authentication from the parent site -- it didn't, it just kept
>> > redirecting its frame to our login page), and ultimately, we ended
> up just
>> > turning authentication off completely. (Yes, this is absolutely
> insecure,
>> > but we were unfortunately under "just make it work" pressure, and
> it was
>> the
>> > only way we could get the stupid login box to go away whenever a
> user
>> tried
>> > to run a report.)
>> >
>> > When adding the rs:ClearSession to the querystring, it appears to
>> annihilate
>> > the Session of our web app -- the error we're seeing is actually a
> page
>> that
>> > is displayed when our web app detects the Session object is no more
> (which
>> > usually only happens on a Session Timeout). As soon as we removed
> that
>> > parameter (fortunately we store this in Web.config), our apparent
> timeout
>> > problems went away immediately.
>> >
>> > But we're back at this same problem where a user can run a report,
> go to
>> > another part of the app and change data, come back to this same
> report,
>> and
>> > not see the updates immediately, despite the fact that all reports
> are set
>> > to "render with most recent data" and "do not cache" and the
>> > www.url.com/ReportServer virtual is set in IIS to "expire content
>> > immediately".
>> >
>|||Sorry, DJM, I misspoke a little in my last post. Let me clarify. As
Bruce has explained, RS has to maintain session so it can handle
certain cases, such as moving to the next page or exporting to a new
format. For those cases, it is desirable that the report remain
unchanged for that user.
In order to implement the workaround, you have to modify the part of
the URL that RS cares about. So, yes, when I said a dummy parameter, I
really meant adding an extra parameter to your report. I realized that
my last post makes it sound like you can add any characters to the URL.
If you already have a zillion reports, then of course it will be a pain
to add a parameter to all of them. (I'd edit the XML directly, and
maybe try to set up some kind of smart search and replace, instead of
using Visual Studio by hand.) Unless you can resolve your
authentication issue, I think this is the way for you to go.
Ted
DJM wrote:
> An interesting idea, but unfortunately it doesn't work. I added a
"&rc:u="
> + DateTime.Now.Ticks.ToString() to the querystring, and it still
shows me
> the cached copy of the data. (If I try it without the "rc:" prefix,
it
> assumes a report parameter, and unless I add a parameter to all of
our
> reports, I get an error message from Report Server about passing in a
> parameter that doesn't exist.)
> "Ted K" <tkyi@.infotechsm.com> wrote in message
> news:1112393836.495225.113290@.f14g2000cwb.googlegroups.com...
> > Fortunately, in my organization we are able to use rs:ClearSession
for
> > our current needs. If you can't use rs:ClearSession, the
workaround we
> > have come up with is to add a dummy parameter to the URL. IE will
only
> > show you the stale, cached copy of the report if the entire URL is
> > character-for-character identical. If you increment the dummy
> > parameter every time you click the link, then the URL's will be
> > different, and IE won't cache.
> >
> > Creating the unique dummy parameter does require a tiny bit of
> > client-side JavaScript programming -- you can't use a simple static
> > HTML link. This was not a problem for us since we were usually
> > launching reports from buttons that already had code.
> >
> > Hope this helps,
> > Ted
> >|||Alright, I'll give that a try then. I still consider this a RS bug.
"Ted K" <tkyi@.infotechsm.com> wrote in message
news:1112633029.297136.68110@.g14g2000cwa.googlegroups.com...
> Sorry, DJM, I misspoke a little in my last post. Let me clarify. As
> Bruce has explained, RS has to maintain session so it can handle
> certain cases, such as moving to the next page or exporting to a new
> format. For those cases, it is desirable that the report remain
> unchanged for that user.
> In order to implement the workaround, you have to modify the part of
> the URL that RS cares about. So, yes, when I said a dummy parameter, I
> really meant adding an extra parameter to your report. I realized that
> my last post makes it sound like you can add any characters to the URL.
> If you already have a zillion reports, then of course it will be a pain
> to add a parameter to all of them. (I'd edit the XML directly, and
> maybe try to set up some kind of smart search and replace, instead of
> using Visual Studio by hand.) Unless you can resolve your
> authentication issue, I think this is the way for you to go.
> Ted
> DJM wrote:
>> An interesting idea, but unfortunately it doesn't work. I added a
> "&rc:u="
>> + DateTime.Now.Ticks.ToString() to the querystring, and it still
> shows me
>> the cached copy of the data. (If I try it without the "rc:" prefix,
> it
>> assumes a report parameter, and unless I add a parameter to all of
> our
>> reports, I get an error message from Report Server about passing in a
>> parameter that doesn't exist.)
>> "Ted K" <tkyi@.infotechsm.com> wrote in message
>> news:1112393836.495225.113290@.f14g2000cwb.googlegroups.com...
>> > Fortunately, in my organization we are able to use rs:ClearSession
> for
>> > our current needs. If you can't use rs:ClearSession, the
> workaround we
>> > have come up with is to add a dummy parameter to the URL. IE will
> only
>> > show you the stale, cached copy of the report if the entire URL is
>> > character-for-character identical. If you increment the dummy
>> > parameter every time you click the link, then the URL's will be
>> > different, and IE won't cache.
>> >
>> > Creating the unique dummy parameter does require a tiny bit of
>> > client-side JavaScript programming -- you can't use a simple static
>> > HTML link. This was not a problem for us since we were usually
>> > launching reports from buttons that already had code.
>> >
>> > Hope this helps,
>> > Ted
>> >
>|||Ok, that worked. I ended up having to spend a couple days and doing a lot
of it by hand (for some reason, I couldn't navigate the XML with code very
easily - SelectNodes didn't want to find nodes I knew existed, even when I
specified the correct namespace), but adding a text parameter and passing in
DateTime.Now.Ticks.ToString() on execute (thankfully there are only two
report viewing functions in our app) now forces the reports to pull current
data. Thanks for the help!
"Ted K" <tkyi@.infotechsm.com> wrote in message
news:1112633029.297136.68110@.g14g2000cwa.googlegroups.com...
> Sorry, DJM, I misspoke a little in my last post. Let me clarify. As
> Bruce has explained, RS has to maintain session so it can handle
> certain cases, such as moving to the next page or exporting to a new
> format. For those cases, it is desirable that the report remain
> unchanged for that user.
> In order to implement the workaround, you have to modify the part of
> the URL that RS cares about. So, yes, when I said a dummy parameter, I
> really meant adding an extra parameter to your report. I realized that
> my last post makes it sound like you can add any characters to the URL.
> If you already have a zillion reports, then of course it will be a pain
> to add a parameter to all of them. (I'd edit the XML directly, and
> maybe try to set up some kind of smart search and replace, instead of
> using Visual Studio by hand.) Unless you can resolve your
> authentication issue, I think this is the way for you to go.
> Ted
> DJM wrote:
>> An interesting idea, but unfortunately it doesn't work. I added a
> "&rc:u="
>> + DateTime.Now.Ticks.ToString() to the querystring, and it still
> shows me
>> the cached copy of the data. (If I try it without the "rc:" prefix,
> it
>> assumes a report parameter, and unless I add a parameter to all of
> our
>> reports, I get an error message from Report Server about passing in a
>> parameter that doesn't exist.)
>> "Ted K" <tkyi@.infotechsm.com> wrote in message
>> news:1112393836.495225.113290@.f14g2000cwb.googlegroups.com...
>> > Fortunately, in my organization we are able to use rs:ClearSession
> for
>> > our current needs. If you can't use rs:ClearSession, the
> workaround we
>> > have come up with is to add a dummy parameter to the URL. IE will
> only
>> > show you the stale, cached copy of the report if the entire URL is
>> > character-for-character identical. If you increment the dummy
>> > parameter every time you click the link, then the URL's will be
>> > different, and IE won't cache.
>> >
>> > Creating the unique dummy parameter does require a tiny bit of
>> > client-side JavaScript programming -- you can't use a simple static
>> > HTML link. This was not a problem for us since we were usually
>> > launching reports from buttons that already had code.
>> >
>> > Hope this helps,
>> > Ted
>> >
>
Thursday, March 8, 2012
Caching reports
I have many reports that run off the same query statement. The Statement
uses parameters that are supplied when the report is executed. There are
allso additional parameters that are used in filters and displayed on the
report (such as title information). I have set up the reports to be cached
and modified the parameters in the report that are not used in the Query with
<UsedInQuery>False. If I run the report with all the same parameters the
Cache seems to work well. If I change one of the parameters used as a filter
the cache is not used. Is there any way around this?
What would be even better is if I could set up cacheing on the shared data
source instead of the report and cache the data for all reports that uses the
same query.
ThanksIt sounds like the report cache includes filters in its caching of data.
It's important to remember that the cache is not just a cache of query data,
but a cache of data the way it will be used in the report, ready for
rendering to any of several different formats.
Now, you could work on the SQL side of things, to see if you can streamline
your datasource (the database itself) to work more effeciently with multiple
reports.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ken McCullough" <Ken McCullough@.discussions.microsoft.com> wrote in message
news:44B0FAB8-5260-4F06-830E-47429006D5FB@.microsoft.com...
>I have many reports that run off the same query statement. The Statement
> uses parameters that are supplied when the report is executed. There are
> allso additional parameters that are used in filters and displayed on the
> report (such as title information). I have set up the reports to be
> cached
> and modified the parameters in the report that are not used in the Query
> with
> <UsedInQuery>False. If I run the report with all the same parameters the
> Cache seems to work well. If I change one of the parameters used as a
> filter
> the cache is not used. Is there any way around this?
> What would be even better is if I could set up cacheing on the shared data
> source instead of the report and cache the data for all reports that uses
> the
> same query.
> Thanks
>|||Ken,
Double check that the <UsedInQuery>False</UsedInQuery> that you added are
still in the RDL.
Though I have never determined the exact sequence to duplicate, I have had
times where I believe the Report Designer removed <UsedInQuery> settings and
I had to add them again.
We have done a fair amount of testing with
<UsedInQuery>False</UsedInQuery> and its cache effects, and at least for us
it is definately working as advertised.
Bob
"Ken McCullough" wrote:
> I have many reports that run off the same query statement. The Statement
> uses parameters that are supplied when the report is executed. There are
> allso additional parameters that are used in filters and displayed on the
> report (such as title information). I have set up the reports to be cached
> and modified the parameters in the report that are not used in the Query with
> <UsedInQuery>False. If I run the report with all the same parameters the
> Cache seems to work well. If I change one of the parameters used as a filter
> the cache is not used. Is there any way around this?
> What would be even better is if I could set up cacheing on the shared data
> source instead of the report and cache the data for all reports that uses the
> same query.
> Thanks
>|||I stand corrected then. The documentation indicates that UsedInQuery
affects report snapshots, which is similar to caching.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"bobhug" <bobhug@.discussions.microsoft.com> wrote in message
news:289783D1-D1DC-4E63-B967-B49DC416FCC8@.microsoft.com...
> Ken,
> Double check that the <UsedInQuery>False</UsedInQuery> that you added are
> still in the RDL.
> Though I have never determined the exact sequence to duplicate, I have
> had
> times where I believe the Report Designer removed <UsedInQuery> settings
> and
> I had to add them again.
> We have done a fair amount of testing with
> <UsedInQuery>False</UsedInQuery> and its cache effects, and at least for
> us
> it is definately working as advertised.
> Bob
> "Ken McCullough" wrote:
>> I have many reports that run off the same query statement. The Statement
>> uses parameters that are supplied when the report is executed. There are
>> allso additional parameters that are used in filters and displayed on the
>> report (such as title information). I have set up the reports to be
>> cached
>> and modified the parameters in the report that are not used in the Query
>> with
>> <UsedInQuery>False. If I run the report with all the same parameters the
>> Cache seems to work well. If I change one of the parameters used as a
>> filter
>> the cache is not used. Is there any way around this?
>> What would be even better is if I could set up cacheing on the shared
>> data
>> source instead of the report and cache the data for all reports that uses
>> the
>> same query.
>> Thanks
>>
uses parameters that are supplied when the report is executed. There are
allso additional parameters that are used in filters and displayed on the
report (such as title information). I have set up the reports to be cached
and modified the parameters in the report that are not used in the Query with
<UsedInQuery>False. If I run the report with all the same parameters the
Cache seems to work well. If I change one of the parameters used as a filter
the cache is not used. Is there any way around this?
What would be even better is if I could set up cacheing on the shared data
source instead of the report and cache the data for all reports that uses the
same query.
ThanksIt sounds like the report cache includes filters in its caching of data.
It's important to remember that the cache is not just a cache of query data,
but a cache of data the way it will be used in the report, ready for
rendering to any of several different formats.
Now, you could work on the SQL side of things, to see if you can streamline
your datasource (the database itself) to work more effeciently with multiple
reports.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ken McCullough" <Ken McCullough@.discussions.microsoft.com> wrote in message
news:44B0FAB8-5260-4F06-830E-47429006D5FB@.microsoft.com...
>I have many reports that run off the same query statement. The Statement
> uses parameters that are supplied when the report is executed. There are
> allso additional parameters that are used in filters and displayed on the
> report (such as title information). I have set up the reports to be
> cached
> and modified the parameters in the report that are not used in the Query
> with
> <UsedInQuery>False. If I run the report with all the same parameters the
> Cache seems to work well. If I change one of the parameters used as a
> filter
> the cache is not used. Is there any way around this?
> What would be even better is if I could set up cacheing on the shared data
> source instead of the report and cache the data for all reports that uses
> the
> same query.
> Thanks
>|||Ken,
Double check that the <UsedInQuery>False</UsedInQuery> that you added are
still in the RDL.
Though I have never determined the exact sequence to duplicate, I have had
times where I believe the Report Designer removed <UsedInQuery> settings and
I had to add them again.
We have done a fair amount of testing with
<UsedInQuery>False</UsedInQuery> and its cache effects, and at least for us
it is definately working as advertised.
Bob
"Ken McCullough" wrote:
> I have many reports that run off the same query statement. The Statement
> uses parameters that are supplied when the report is executed. There are
> allso additional parameters that are used in filters and displayed on the
> report (such as title information). I have set up the reports to be cached
> and modified the parameters in the report that are not used in the Query with
> <UsedInQuery>False. If I run the report with all the same parameters the
> Cache seems to work well. If I change one of the parameters used as a filter
> the cache is not used. Is there any way around this?
> What would be even better is if I could set up cacheing on the shared data
> source instead of the report and cache the data for all reports that uses the
> same query.
> Thanks
>|||I stand corrected then. The documentation indicates that UsedInQuery
affects report snapshots, which is similar to caching.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"bobhug" <bobhug@.discussions.microsoft.com> wrote in message
news:289783D1-D1DC-4E63-B967-B49DC416FCC8@.microsoft.com...
> Ken,
> Double check that the <UsedInQuery>False</UsedInQuery> that you added are
> still in the RDL.
> Though I have never determined the exact sequence to duplicate, I have
> had
> times where I believe the Report Designer removed <UsedInQuery> settings
> and
> I had to add them again.
> We have done a fair amount of testing with
> <UsedInQuery>False</UsedInQuery> and its cache effects, and at least for
> us
> it is definately working as advertised.
> Bob
> "Ken McCullough" wrote:
>> I have many reports that run off the same query statement. The Statement
>> uses parameters that are supplied when the report is executed. There are
>> allso additional parameters that are used in filters and displayed on the
>> report (such as title information). I have set up the reports to be
>> cached
>> and modified the parameters in the report that are not used in the Query
>> with
>> <UsedInQuery>False. If I run the report with all the same parameters the
>> Cache seems to work well. If I change one of the parameters used as a
>> filter
>> the cache is not used. Is there any way around this?
>> What would be even better is if I could set up cacheing on the shared
>> data
>> source instead of the report and cache the data for all reports that uses
>> the
>> same query.
>> Thanks
>>
caching questions
I am new to Reporting Services and would like to know if it can do the
following:
1. If I cache a report with timed expiration such as 48 hours, can I
programatically expire a specific report before it would have expired
normally?
2. When a report is generated from cache, is it possible for it to get any
"superficial" customizations at render time, such as colors or a gif?
Thanks for any help!Hi,
> 1. If I cache a report with timed expiration such as 48 hours, can I
> programatically expire a specific report before it would have expired
> normally?
We do not have straightforward method available. To do so, you could use
SetCacheOptions Method set the CacheReport false and then reset the
expiration time.
> 2. When a report is generated from cache, is it possible for it to get
any
> "superficial" customizations at render time, such as colors or a gif?
No, it's not possible
Sincerely yours,
Michael Cheng
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.
following:
1. If I cache a report with timed expiration such as 48 hours, can I
programatically expire a specific report before it would have expired
normally?
2. When a report is generated from cache, is it possible for it to get any
"superficial" customizations at render time, such as colors or a gif?
Thanks for any help!Hi,
> 1. If I cache a report with timed expiration such as 48 hours, can I
> programatically expire a specific report before it would have expired
> normally?
We do not have straightforward method available. To do so, you could use
SetCacheOptions Method set the CacheReport false and then reset the
expiration time.
> 2. When a report is generated from cache, is it possible for it to get
any
> "superficial" customizations at render time, such as colors or a gif?
No, it's not possible
Sincerely yours,
Michael Cheng
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.
Caching Question
Question for all Cache Gurus
I have a page in my website that pulls content for anewsletter from a SQL Server 2000 database using a SqlDataSource and aFormView. I add a new newsletter every week. Other than that weekly addition,no changes are really ever needed or made to the data. Scott Gu says everyapplication can and should make use of caching and I would like to add somekind of caching to improve the performance of this page. I have read about SqlCacheDependency with SQL2000, but if I understand it correctly, it will poll the database for changesevery x seconds, an expensive operation that I just don't need; the data onlychanges once a week. I've seen some code examples using DataSets and the CacheAPI but I'm really just looking for a simple, mostly declarative solution using theSqlDataSource. Theoretically, I wouldlike the data cached forever until I insert a new item. I have set thefollowing cache-related properties on my SqlDataSource as follows:
EnableCaching = True
DataSourceMode = DataSet
CacheDuration = Infinite
CacheExpirationPolicy = Absolute
Setting these properties seems to work great for caching thedata but how do I programmatically invalidate the cache when a new item isinserted? I have a separate admin page with a FormView that I use to insert thenewsletter each week. Is there some kind of code that I could put in theItemInserted event that would invalidate the cache so that the new item will bedisplayed and then cached? I don't have any experience with caching; where am I going wrong? Are my expectations unrealistic? Thank you in advance to anyone who can shed somelight on this issue.
The best way to do this is by using filebased cache dependency. So when ever any change happens to the file cache gets invalidated. check out the below link.
http://www.eggheadcafe.com/articles/20030716.asp
In this Peter used SQL trigger to update the file, but for your requirment I would suggest, as soon as upload a new news letter through admin portal, make change to this dependency file, thats it cache gets invalidated and any new requests gets the new data.
Caching problem?
If I create a view and then a mining structure run it everything works fine BUT if I then alter the view (e.g change a filter setting in a named query) or the actual contents of the database, the OLD contents persist.
Using the view refresh says there are no changes (which is correct as I don't change any column names etc.). If I remove Keep Training Cases, this fixes it, but for Time Series, it then gives an error, so you have to go back and re-select it... is there a direct option to clear caches?
The dependency settings aren't sensitive to non-destructive changes in the DSV. You can simply do a ProcessFull on the mining structure to reprocess from the data.
There is an option ProcessClearStructureOnly (something like that), that clears the training cases from the cache.
TimeSeries requires the cache or the viewer will not work - you can likely drop the cache using the process method above, but you will get errors when trying to view the time series models (at least the chart part)
caching problem in sql reporting services
Hi all,
I have developed a web application which displays records stored in the database based on certain criteria supplied by the user. I have used SQL Reporting services for generating a report coresponding to the displayed records. The problem I faces is as follows.
I generated a report for the records displayed in the application. Then, I updated one of the records and again generated the report. But the change I made is not reflected in it. The report is the same as the one previously generated. But the change is reflected if I logged out of the application, again logged in and generated the report.
I checked the 'Execution properties' of the report in the Report Manager and I found that the 'Do not cache temporary copies of this report' option remains selected. But the caching problem exists. Please help if anyone knows a solution. I am desperately in need of a solution for the above problem.
Thanks,
Renju.
Had similar problem with SRS 2000 (are you using same version).
Resolved by creating a dummy parameter that passed the current date/time - forcing a new dataset everytime...
caching problem
Hi all gurus
I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
ram , intel xeon 8 processor machine.
We have 2 files file1 with 15,000 query and file2 with 60,000 query.
if run file2 alone it takes first 6 mins and if i run it second time
it takes 2 mins.
so this time it uses caches and returns result in 2 mins
So its taking benefit of cache.
file2 first 5 min
file2 second 2 min
But when i run file1 it takes 2 minutes and file2 takes 6 mins.
After then again if i run file1 again it takes again 2 mins and file2
takes 5 mins.
memory goes to 10 GB from 16 GB.
file1 first 2 min
file2 first 5 min
file1 second 2 min
file2 second 5 min
here second time when i run this files it doesn't take benefit of
cache and take same time
so why when i run both files sequentially 2 times 2nd time its not
taking benefit of caching even 6 GB of memory available.If there is 6
GB memory available then it should keep all data in cache and give
fast results second time.
Is there any limitation for caching or query plan? running 2 different
files will drop cache?
What exactly do you mean by files and 15,000 query? And if sql server is
using all 16GB then you have a problem with your configuration. You need to
set a MAX Memory setting of no more than 14GB to leave room for the OS to do
it's job.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
> Hi all gurus
> I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
> ram , intel xeon 8 processor machine.
> We have 2 files file1 with 15,000 query and file2 with 60,000 query.
>
> if run file2 alone it takes first 6 mins and if i run it second time
> it takes 2 mins.
> so this time it uses caches and returns result in 2 mins
> So its taking benefit of cache.
> file2 first 5 min
> file2 second 2 min
> But when i run file1 it takes 2 minutes and file2 takes 6 mins.
> After then again if i run file1 again it takes again 2 mins and file2
> takes 5 mins.
> memory goes to 10 GB from 16 GB.
> file1 first 2 min
> file2 first 5 min
> file1 second 2 min
> file2 second 5 min
> here second time when i run this files it doesn't take benefit of
> cache and take same time
> so why when i run both files sequentially 2 times 2nd time its not
> taking benefit of caching even 6 GB of memory available.If there is 6
> GB memory available then it should keep all data in cache and give
> fast results second time.
> Is there any limitation for caching or query plan? running 2 different
> files will drop cache?
|||On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:[vbcol=seagreen]
> What exactly do you mean by files and 15,000 query? And if sql server is
> using all 16GB then you have a problem with your configuration. You need to
> set a MAX Memory setting of no more than 14GB to leave room for the OS to do
> it's job.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> <shahdha...@.gmail.com> wrote in message
> news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
>
>
>
>
>
Hi Andrew
there are about 15000 select queries in first file and 60000 select
queries in second file. And MS is not using more than 10 GB.
Problem is that if i run only one file 2 times then at second time is
uses cache, but if I run file sequentially 2 time then its not using
cache.
why its doing so?
|||60K selects? Wow that is one heck of a report. You said initially that
the memory went from 10GB to 16GB and now you are saying it is not using
more than 10GB. How are you determining this? What I said memory wise is
still true regardless and should be changed if not that way now. The
scenario you use is in no way proof that the cache is not being used. There
are many things that can cause this behavior. Poorly executed parallel plans
or blocking for instance can easily account for these numbers. Did you check
for blocking? Did you look at the file stats to see how much physical I/O
you are doing? And how large is the database itself?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
> On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
> Hi Andrew
> there are about 15000 select queries in first file and 60000 select
> queries in second file. And MS is not using more than 10 GB.
> Problem is that if i run only one file 2 times then at second time is
> uses cache, but if I run file sequentially 2 time then its not using
> cache.
> why its doing so?
|||On Jan 11, 12:22 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:[vbcol=seagreen]
> 60K selects? Wow that is one heck of a report. You said initially that
> the memory went from 10GB to 16GB and now you are saying it is not using
> more than 10GB. How are you determining this? What I said memory wise is
> still true regardless and should be changed if not that way now. The
> scenario you use is in no way proof that the cache is not being used. There
> are many things that can cause this behavior. Poorly executed parallel plans
> or blocking for instance can easily account for these numbers. Did you check
> for blocking? Did you look at the file stats to see how much physical I/O
> you are doing? And how large is the database itself?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> <shahdha...@.gmail.com> wrote in message
> news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
>
>
>
>
>
>
>
>
sorry there is a typo. from 16 gb memory 10 gb is used. so i think if
6 gb is available , I got this info from task manager it showes 10 gb
uses, then in second case when 2 files are sequentially run 2 times
then why its not using cache 2nd times?
|||Task manager does not tell you how much memory SQL Server is really using
you should use the Perfmon counters for that. What is the Min and Max
memory set to in SQL Server? And regardless that does not tell you if the
data is cached or not. There are too many other factors that can be
involved. Until you get the actual metrics that I mentioned you are just
guessing and probably barking up the wrong tree.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:6b8e2894-1ccf-4b5c-9a3c-e6ed7c3208c7@.j78g2000hsd.googlegroups.com...
> On Jan 11, 12:22 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
> sorry there is a typo. from 16 gb memory 10 gb is used. so i think if
> 6 gb is available , I got this info from task manager it showes 10 gb
> uses, then in second case when 2 files are sequentially run 2 times
> then why its not using cache 2nd times?
>
I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
ram , intel xeon 8 processor machine.
We have 2 files file1 with 15,000 query and file2 with 60,000 query.
if run file2 alone it takes first 6 mins and if i run it second time
it takes 2 mins.
so this time it uses caches and returns result in 2 mins
So its taking benefit of cache.
file2 first 5 min
file2 second 2 min
But when i run file1 it takes 2 minutes and file2 takes 6 mins.
After then again if i run file1 again it takes again 2 mins and file2
takes 5 mins.
memory goes to 10 GB from 16 GB.
file1 first 2 min
file2 first 5 min
file1 second 2 min
file2 second 5 min
here second time when i run this files it doesn't take benefit of
cache and take same time
so why when i run both files sequentially 2 times 2nd time its not
taking benefit of caching even 6 GB of memory available.If there is 6
GB memory available then it should keep all data in cache and give
fast results second time.
Is there any limitation for caching or query plan? running 2 different
files will drop cache?
What exactly do you mean by files and 15,000 query? And if sql server is
using all 16GB then you have a problem with your configuration. You need to
set a MAX Memory setting of no more than 14GB to leave room for the OS to do
it's job.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
> Hi all gurus
> I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
> ram , intel xeon 8 processor machine.
> We have 2 files file1 with 15,000 query and file2 with 60,000 query.
>
> if run file2 alone it takes first 6 mins and if i run it second time
> it takes 2 mins.
> so this time it uses caches and returns result in 2 mins
> So its taking benefit of cache.
> file2 first 5 min
> file2 second 2 min
> But when i run file1 it takes 2 minutes and file2 takes 6 mins.
> After then again if i run file1 again it takes again 2 mins and file2
> takes 5 mins.
> memory goes to 10 GB from 16 GB.
> file1 first 2 min
> file2 first 5 min
> file1 second 2 min
> file2 second 5 min
> here second time when i run this files it doesn't take benefit of
> cache and take same time
> so why when i run both files sequentially 2 times 2nd time its not
> taking benefit of caching even 6 GB of memory available.If there is 6
> GB memory available then it should keep all data in cache and give
> fast results second time.
> Is there any limitation for caching or query plan? running 2 different
> files will drop cache?
|||On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:[vbcol=seagreen]
> What exactly do you mean by files and 15,000 query? And if sql server is
> using all 16GB then you have a problem with your configuration. You need to
> set a MAX Memory setting of no more than 14GB to leave room for the OS to do
> it's job.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> <shahdha...@.gmail.com> wrote in message
> news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
>
>
>
>
>
Hi Andrew
there are about 15000 select queries in first file and 60000 select
queries in second file. And MS is not using more than 10 GB.
Problem is that if i run only one file 2 times then at second time is
uses cache, but if I run file sequentially 2 time then its not using
cache.
why its doing so?
|||60K selects? Wow that is one heck of a report. You said initially that
the memory went from 10GB to 16GB and now you are saying it is not using
more than 10GB. How are you determining this? What I said memory wise is
still true regardless and should be changed if not that way now. The
scenario you use is in no way proof that the cache is not being used. There
are many things that can cause this behavior. Poorly executed parallel plans
or blocking for instance can easily account for these numbers. Did you check
for blocking? Did you look at the file stats to see how much physical I/O
you are doing? And how large is the database itself?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
> On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
> Hi Andrew
> there are about 15000 select queries in first file and 60000 select
> queries in second file. And MS is not using more than 10 GB.
> Problem is that if i run only one file 2 times then at second time is
> uses cache, but if I run file sequentially 2 time then its not using
> cache.
> why its doing so?
|||On Jan 11, 12:22 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:[vbcol=seagreen]
> 60K selects? Wow that is one heck of a report. You said initially that
> the memory went from 10GB to 16GB and now you are saying it is not using
> more than 10GB. How are you determining this? What I said memory wise is
> still true regardless and should be changed if not that way now. The
> scenario you use is in no way proof that the cache is not being used. There
> are many things that can cause this behavior. Poorly executed parallel plans
> or blocking for instance can easily account for these numbers. Did you check
> for blocking? Did you look at the file stats to see how much physical I/O
> you are doing? And how large is the database itself?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> <shahdha...@.gmail.com> wrote in message
> news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
>
>
>
>
>
>
>
>
sorry there is a typo. from 16 gb memory 10 gb is used. so i think if
6 gb is available , I got this info from task manager it showes 10 gb
uses, then in second case when 2 files are sequentially run 2 times
then why its not using cache 2nd times?
|||Task manager does not tell you how much memory SQL Server is really using
you should use the Perfmon counters for that. What is the Min and Max
memory set to in SQL Server? And regardless that does not tell you if the
data is cached or not. There are too many other factors that can be
involved. Until you get the actual metrics that I mentioned you are just
guessing and probably barking up the wrong tree.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:6b8e2894-1ccf-4b5c-9a3c-e6ed7c3208c7@.j78g2000hsd.googlegroups.com...
> On Jan 11, 12:22 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
> sorry there is a typo. from 16 gb memory 10 gb is used. so i think if
> 6 gb is available , I got this info from task manager it showes 10 gb
> uses, then in second case when 2 files are sequentially run 2 times
> then why its not using cache 2nd times?
>
Caching problem
Hi,
I'm retrieving data from on Oracle database. Even though caching for this
report is disabled, i still get the same data. If i run the report in VS and
preview it, there seems no prblem..
Anyone an idea?if you are calling the report via URL access then add the rs:ClearSession
parameter to the url....
"Koen" wrote:
> Hi,
> I'm retrieving data from on Oracle database. Even though caching for this
> report is disabled, i still get the same data. If i run the report in VS and
> preview it, there seems no prblem..
> Anyone an idea?|||is there a way to set that at the report level?
"NH" wrote:
> if you are calling the report via URL access then add the rs:ClearSession
> parameter to the url....
> "Koen" wrote:
> > Hi,
> >
> > I'm retrieving data from on Oracle database. Even though caching for this
> > report is disabled, i still get the same data. If i run the report in VS and
> > preview it, there seems no prblem..
> >
> > Anyone an idea?|||I dont know of a way but it shouldnt really matter as long as you specify it
in the url.
"Marvin" wrote:
> is there a way to set that at the report level?
> "NH" wrote:
> > if you are calling the report via URL access then add the rs:ClearSession
> > parameter to the url....
> >
> > "Koen" wrote:
> >
> > > Hi,
> > >
> > > I'm retrieving data from on Oracle database. Even though caching for this
> > > report is disabled, i still get the same data. If i run the report in VS and
> > > preview it, there seems no prblem..
> > >
> > > Anyone an idea?
I'm retrieving data from on Oracle database. Even though caching for this
report is disabled, i still get the same data. If i run the report in VS and
preview it, there seems no prblem..
Anyone an idea?if you are calling the report via URL access then add the rs:ClearSession
parameter to the url....
"Koen" wrote:
> Hi,
> I'm retrieving data from on Oracle database. Even though caching for this
> report is disabled, i still get the same data. If i run the report in VS and
> preview it, there seems no prblem..
> Anyone an idea?|||is there a way to set that at the report level?
"NH" wrote:
> if you are calling the report via URL access then add the rs:ClearSession
> parameter to the url....
> "Koen" wrote:
> > Hi,
> >
> > I'm retrieving data from on Oracle database. Even though caching for this
> > report is disabled, i still get the same data. If i run the report in VS and
> > preview it, there seems no prblem..
> >
> > Anyone an idea?|||I dont know of a way but it shouldnt really matter as long as you specify it
in the url.
"Marvin" wrote:
> is there a way to set that at the report level?
> "NH" wrote:
> > if you are calling the report via URL access then add the rs:ClearSession
> > parameter to the url....
> >
> > "Koen" wrote:
> >
> > > Hi,
> > >
> > > I'm retrieving data from on Oracle database. Even though caching for this
> > > report is disabled, i still get the same data. If i run the report in VS and
> > > preview it, there seems no prblem..
> > >
> > > Anyone an idea?
caching problem
Hi all gurus
I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
ram , intel xeon 8 processor machine.
We have 2 files file1 with 15,000 query and file2 with 60,000 query.
if run file2 alone it takes first 6 mins and if i run it second time
it takes 2 mins.
so this time it uses caches and returns result in 2 mins
So its taking benefit of cache.
file2 first 5 min
file2 second 2 min
But when i run file1 it takes 2 minutes and file2 takes 6 mins.
After then again if i run file1 again it takes again 2 mins and file2
takes 5 mins.
memory goes to 10 GB from 16 GB.
file1 first 2 min
file2 first 5 min
file1 second 2 min
file2 second 5 min
here second time when i run this files it doesn't take benefit of
cache and take same time
so why when i run both files sequentially 2 times 2nd time its not
taking benefit of caching even 6 GB of memory available.If there is 6
GB memory available then it should keep all data in cache and give
fast results second time.
Is there any limitation for caching or query plan? running 2 different
files will drop cache?What exactly do you mean by files and 15,000 query? And if sql server is
using all 16GB then you have a problem with your configuration. You need to
set a MAX Memory setting of no more than 14GB to leave room for the OS to do
it's job.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
> Hi all gurus
> I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
> ram , intel xeon 8 processor machine.
> We have 2 files file1 with 15,000 query and file2 with 60,000 query.
>
> if run file2 alone it takes first 6 mins and if i run it second time
> it takes 2 mins.
> so this time it uses caches and returns result in 2 mins
> So its taking benefit of cache.
> file2 first 5 min
> file2 second 2 min
> But when i run file1 it takes 2 minutes and file2 takes 6 mins.
> After then again if i run file1 again it takes again 2 mins and file2
> takes 5 mins.
> memory goes to 10 GB from 16 GB.
> file1 first 2 min
> file2 first 5 min
> file1 second 2 min
> file2 second 5 min
> here second time when i run this files it doesn't take benefit of
> cache and take same time
> so why when i run both files sequentially 2 times 2nd time its not
> taking benefit of caching even 6 GB of memory available.If there is 6
> GB memory available then it should keep all data in cache and give
> fast results second time.
> Is there any limitation for caching or query plan? running 2 different
> files will drop cache?|||On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> What exactly do you mean by files and 15,000 query? And if sql server is
> using all 16GB then you have a problem with your configuration. You need to
> set a MAX Memory setting of no more than 14GB to leave room for the OS to do
> it's job.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> <shahdha...@.gmail.com> wrote in message
> news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
> > Hi all gurus
> > I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
> > ram , intel xeon 8 processor machine.
> > We have 2 files file1 with 15,000 query and file2 with 60,000 query.
> > if run file2 alone it takes first 6 mins and if i run it second time
> > it takes 2 mins.
> > so this time it uses caches and returns result in 2 mins
> > So its taking benefit of cache.
> > file2 first 5 min
> > file2 second 2 min
> > But when i run file1 it takes 2 minutes and file2 takes 6 mins.
> > After then again if i run file1 again it takes again 2 mins and file2
> > takes 5 mins.
> > memory goes to 10 GB from 16 GB.
> > file1 first 2 min
> > file2 first 5 min
> > file1 second 2 min
> > file2 second 5 min
> > here second time when i run this files it doesn't take benefit of
> > cache and take same time
> > so why when i run both files sequentially 2 times 2nd time its not
> > taking benefit of caching even 6 GB of memory available.If there is 6
> > GB memory available then it should keep all data in cache and give
> > fast results second time.
> > Is there any limitation for caching or query plan? running 2 different
> > files will drop cache?
Hi Andrew
there are about 15000 select queries in first file and 60000 select
queries in second file. And MS is not using more than 10 GB.
Problem is that if i run only one file 2 times then at second time is
uses cache, but if I run file sequentially 2 time then its not using
cache.
why its doing so?|||60K selects? Wow that is one heck of a report. You said initially that
the memory went from 10GB to 16GB and now you are saying it is not using
more than 10GB. How are you determining this? What I said memory wise is
still true regardless and should be changed if not that way now. The
scenario you use is in no way proof that the cache is not being used. There
are many things that can cause this behavior. Poorly executed parallel plans
or blocking for instance can easily account for these numbers. Did you check
for blocking? Did you look at the file stats to see how much physical I/O
you are doing? And how large is the database itself?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
> On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
>> What exactly do you mean by files and 15,000 query? And if sql server
>> is
>> using all 16GB then you have a problem with your configuration. You need
>> to
>> set a MAX Memory setting of no more than 14GB to leave room for the OS to
>> do
>> it's job.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>> <shahdha...@.gmail.com> wrote in message
>> news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
>> > Hi all gurus
>> > I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
>> > ram , intel xeon 8 processor machine.
>> > We have 2 files file1 with 15,000 query and file2 with 60,000 query.
>> > if run file2 alone it takes first 6 mins and if i run it second time
>> > it takes 2 mins.
>> > so this time it uses caches and returns result in 2 mins
>> > So its taking benefit of cache.
>> > file2 first 5 min
>> > file2 second 2 min
>> > But when i run file1 it takes 2 minutes and file2 takes 6 mins.
>> > After then again if i run file1 again it takes again 2 mins and file2
>> > takes 5 mins.
>> > memory goes to 10 GB from 16 GB.
>> > file1 first 2 min
>> > file2 first 5 min
>> > file1 second 2 min
>> > file2 second 5 min
>> > here second time when i run this files it doesn't take benefit of
>> > cache and take same time
>> > so why when i run both files sequentially 2 times 2nd time its not
>> > taking benefit of caching even 6 GB of memory available.If there is 6
>> > GB memory available then it should keep all data in cache and give
>> > fast results second time.
>> > Is there any limitation for caching or query plan? running 2 different
>> > files will drop cache?
> Hi Andrew
> there are about 15000 select queries in first file and 60000 select
> queries in second file. And MS is not using more than 10 GB.
> Problem is that if i run only one file 2 times then at second time is
> uses cache, but if I run file sequentially 2 time then its not using
> cache.
> why its doing so?|||On Jan 11, 12:22 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> 60K selects? Wow that is one heck of a report. You said initially that
> the memory went from 10GB to 16GB and now you are saying it is not using
> more than 10GB. How are you determining this? What I said memory wise is
> still true regardless and should be changed if not that way now. The
> scenario you use is in no way proof that the cache is not being used. There
> are many things that can cause this behavior. Poorly executed parallel plans
> or blocking for instance can easily account for these numbers. Did you check
> for blocking? Did you look at the file stats to see how much physical I/O
> you are doing? And how large is the database itself?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> <shahdha...@.gmail.com> wrote in message
> news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
> > On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> > wrote:
> >> What exactly do you mean by files and 15,000 query? And if sql server
> >> is
> >> using all 16GB then you have a problem with your configuration. You need
> >> to
> >> set a MAX Memory setting of no more than 14GB to leave room for the OS to
> >> do
> >> it's job.
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >> <shahdha...@.gmail.com> wrote in message
> >>news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
> >> > Hi all gurus
> >> > I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
> >> > ram , intel xeon 8 processor machine.
> >> > We have 2 files file1 with 15,000 query and file2 with 60,000 query.
> >> > if run file2 alone it takes first 6 mins and if i run it second time
> >> > it takes 2 mins.
> >> > so this time it uses caches and returns result in 2 mins
> >> > So its taking benefit of cache.
> >> > file2 first 5 min
> >> > file2 second 2 min
> >> > But when i run file1 it takes 2 minutes and file2 takes 6 mins.
> >> > After then again if i run file1 again it takes again 2 mins and file2
> >> > takes 5 mins.
> >> > memory goes to 10 GB from 16 GB.
> >> > file1 first 2 min
> >> > file2 first 5 min
> >> > file1 second 2 min
> >> > file2 second 5 min
> >> > here second time when i run this files it doesn't take benefit of
> >> > cache and take same time
> >> > so why when i run both files sequentially 2 times 2nd time its not
> >> > taking benefit of caching even 6 GB of memory available.If there is 6
> >> > GB memory available then it should keep all data in cache and give
> >> > fast results second time.
> >> > Is there any limitation for caching or query plan? running 2 different
> >> > files will drop cache?
> > Hi Andrew
> > there are about 15000 select queries in first file and 60000 select
> > queries in second file. And MS is not using more than 10 GB.
> > Problem is that if i run only one file 2 times then at second time is
> > uses cache, but if I run file sequentially 2 time then its not using
> > cache.
> > why its doing so?
sorry there is a typo. from 16 gb memory 10 gb is used. so i think if
6 gb is available , I got this info from task manager it showes 10 gb
uses, then in second case when 2 files are sequentially run 2 times
then why its not using cache 2nd times?|||Task manager does not tell you how much memory SQL Server is really using
you should use the Perfmon counters for that. What is the Min and Max
memory set to in SQL Server? And regardless that does not tell you if the
data is cached or not. There are too many other factors that can be
involved. Until you get the actual metrics that I mentioned you are just
guessing and probably barking up the wrong tree.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:6b8e2894-1ccf-4b5c-9a3c-e6ed7c3208c7@.j78g2000hsd.googlegroups.com...
> On Jan 11, 12:22 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
>> 60K selects? Wow that is one heck of a report. You said initially that
>> the memory went from 10GB to 16GB and now you are saying it is not using
>> more than 10GB. How are you determining this? What I said memory wise
>> is
>> still true regardless and should be changed if not that way now. The
>> scenario you use is in no way proof that the cache is not being used.
>> There
>> are many things that can cause this behavior. Poorly executed parallel
>> plans
>> or blocking for instance can easily account for these numbers. Did you
>> check
>> for blocking? Did you look at the file stats to see how much physical
>> I/O
>> you are doing? And how large is the database itself?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>> <shahdha...@.gmail.com> wrote in message
>> news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
>> > On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
>> > wrote:
>> >> What exactly do you mean by files and 15,000 query? And if sql
>> >> server
>> >> is
>> >> using all 16GB then you have a problem with your configuration. You
>> >> need
>> >> to
>> >> set a MAX Memory setting of no more than 14GB to leave room for the OS
>> >> to
>> >> do
>> >> it's job.
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >> <shahdha...@.gmail.com> wrote in message
>> >>news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
>> >> > Hi all gurus
>> >> > I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
>> >> > ram , intel xeon 8 processor machine.
>> >> > We have 2 files file1 with 15,000 query and file2 with 60,000 query.
>> >> > if run file2 alone it takes first 6 mins and if i run it second time
>> >> > it takes 2 mins.
>> >> > so this time it uses caches and returns result in 2 mins
>> >> > So its taking benefit of cache.
>> >> > file2 first 5 min
>> >> > file2 second 2 min
>> >> > But when i run file1 it takes 2 minutes and file2 takes 6 mins.
>> >> > After then again if i run file1 again it takes again 2 mins and
>> >> > file2
>> >> > takes 5 mins.
>> >> > memory goes to 10 GB from 16 GB.
>> >> > file1 first 2 min
>> >> > file2 first 5 min
>> >> > file1 second 2 min
>> >> > file2 second 5 min
>> >> > here second time when i run this files it doesn't take benefit of
>> >> > cache and take same time
>> >> > so why when i run both files sequentially 2 times 2nd time its not
>> >> > taking benefit of caching even 6 GB of memory available.If there is
>> >> > 6
>> >> > GB memory available then it should keep all data in cache and give
>> >> > fast results second time.
>> >> > Is there any limitation for caching or query plan? running 2
>> >> > different
>> >> > files will drop cache?
>> > Hi Andrew
>> > there are about 15000 select queries in first file and 60000 select
>> > queries in second file. And MS is not using more than 10 GB.
>> > Problem is that if i run only one file 2 times then at second time is
>> > uses cache, but if I run file sequentially 2 time then its not using
>> > cache.
>> > why its doing so?
> sorry there is a typo. from 16 gb memory 10 gb is used. so i think if
> 6 gb is available , I got this info from task manager it showes 10 gb
> uses, then in second case when 2 files are sequentially run 2 times
> then why its not using cache 2nd times?
>
I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
ram , intel xeon 8 processor machine.
We have 2 files file1 with 15,000 query and file2 with 60,000 query.
if run file2 alone it takes first 6 mins and if i run it second time
it takes 2 mins.
so this time it uses caches and returns result in 2 mins
So its taking benefit of cache.
file2 first 5 min
file2 second 2 min
But when i run file1 it takes 2 minutes and file2 takes 6 mins.
After then again if i run file1 again it takes again 2 mins and file2
takes 5 mins.
memory goes to 10 GB from 16 GB.
file1 first 2 min
file2 first 5 min
file1 second 2 min
file2 second 5 min
here second time when i run this files it doesn't take benefit of
cache and take same time
so why when i run both files sequentially 2 times 2nd time its not
taking benefit of caching even 6 GB of memory available.If there is 6
GB memory available then it should keep all data in cache and give
fast results second time.
Is there any limitation for caching or query plan? running 2 different
files will drop cache?What exactly do you mean by files and 15,000 query? And if sql server is
using all 16GB then you have a problem with your configuration. You need to
set a MAX Memory setting of no more than 14GB to leave room for the OS to do
it's job.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
> Hi all gurus
> I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
> ram , intel xeon 8 processor machine.
> We have 2 files file1 with 15,000 query and file2 with 60,000 query.
>
> if run file2 alone it takes first 6 mins and if i run it second time
> it takes 2 mins.
> so this time it uses caches and returns result in 2 mins
> So its taking benefit of cache.
> file2 first 5 min
> file2 second 2 min
> But when i run file1 it takes 2 minutes and file2 takes 6 mins.
> After then again if i run file1 again it takes again 2 mins and file2
> takes 5 mins.
> memory goes to 10 GB from 16 GB.
> file1 first 2 min
> file2 first 5 min
> file1 second 2 min
> file2 second 5 min
> here second time when i run this files it doesn't take benefit of
> cache and take same time
> so why when i run both files sequentially 2 times 2nd time its not
> taking benefit of caching even 6 GB of memory available.If there is 6
> GB memory available then it should keep all data in cache and give
> fast results second time.
> Is there any limitation for caching or query plan? running 2 different
> files will drop cache?|||On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> What exactly do you mean by files and 15,000 query? And if sql server is
> using all 16GB then you have a problem with your configuration. You need to
> set a MAX Memory setting of no more than 14GB to leave room for the OS to do
> it's job.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> <shahdha...@.gmail.com> wrote in message
> news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
> > Hi all gurus
> > I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
> > ram , intel xeon 8 processor machine.
> > We have 2 files file1 with 15,000 query and file2 with 60,000 query.
> > if run file2 alone it takes first 6 mins and if i run it second time
> > it takes 2 mins.
> > so this time it uses caches and returns result in 2 mins
> > So its taking benefit of cache.
> > file2 first 5 min
> > file2 second 2 min
> > But when i run file1 it takes 2 minutes and file2 takes 6 mins.
> > After then again if i run file1 again it takes again 2 mins and file2
> > takes 5 mins.
> > memory goes to 10 GB from 16 GB.
> > file1 first 2 min
> > file2 first 5 min
> > file1 second 2 min
> > file2 second 5 min
> > here second time when i run this files it doesn't take benefit of
> > cache and take same time
> > so why when i run both files sequentially 2 times 2nd time its not
> > taking benefit of caching even 6 GB of memory available.If there is 6
> > GB memory available then it should keep all data in cache and give
> > fast results second time.
> > Is there any limitation for caching or query plan? running 2 different
> > files will drop cache?
Hi Andrew
there are about 15000 select queries in first file and 60000 select
queries in second file. And MS is not using more than 10 GB.
Problem is that if i run only one file 2 times then at second time is
uses cache, but if I run file sequentially 2 time then its not using
cache.
why its doing so?|||60K selects? Wow that is one heck of a report. You said initially that
the memory went from 10GB to 16GB and now you are saying it is not using
more than 10GB. How are you determining this? What I said memory wise is
still true regardless and should be changed if not that way now. The
scenario you use is in no way proof that the cache is not being used. There
are many things that can cause this behavior. Poorly executed parallel plans
or blocking for instance can easily account for these numbers. Did you check
for blocking? Did you look at the file stats to see how much physical I/O
you are doing? And how large is the database itself?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
> On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
>> What exactly do you mean by files and 15,000 query? And if sql server
>> is
>> using all 16GB then you have a problem with your configuration. You need
>> to
>> set a MAX Memory setting of no more than 14GB to leave room for the OS to
>> do
>> it's job.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>> <shahdha...@.gmail.com> wrote in message
>> news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
>> > Hi all gurus
>> > I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
>> > ram , intel xeon 8 processor machine.
>> > We have 2 files file1 with 15,000 query and file2 with 60,000 query.
>> > if run file2 alone it takes first 6 mins and if i run it second time
>> > it takes 2 mins.
>> > so this time it uses caches and returns result in 2 mins
>> > So its taking benefit of cache.
>> > file2 first 5 min
>> > file2 second 2 min
>> > But when i run file1 it takes 2 minutes and file2 takes 6 mins.
>> > After then again if i run file1 again it takes again 2 mins and file2
>> > takes 5 mins.
>> > memory goes to 10 GB from 16 GB.
>> > file1 first 2 min
>> > file2 first 5 min
>> > file1 second 2 min
>> > file2 second 5 min
>> > here second time when i run this files it doesn't take benefit of
>> > cache and take same time
>> > so why when i run both files sequentially 2 times 2nd time its not
>> > taking benefit of caching even 6 GB of memory available.If there is 6
>> > GB memory available then it should keep all data in cache and give
>> > fast results second time.
>> > Is there any limitation for caching or query plan? running 2 different
>> > files will drop cache?
> Hi Andrew
> there are about 15000 select queries in first file and 60000 select
> queries in second file. And MS is not using more than 10 GB.
> Problem is that if i run only one file 2 times then at second time is
> uses cache, but if I run file sequentially 2 time then its not using
> cache.
> why its doing so?|||On Jan 11, 12:22 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> 60K selects? Wow that is one heck of a report. You said initially that
> the memory went from 10GB to 16GB and now you are saying it is not using
> more than 10GB. How are you determining this? What I said memory wise is
> still true regardless and should be changed if not that way now. The
> scenario you use is in no way proof that the cache is not being used. There
> are many things that can cause this behavior. Poorly executed parallel plans
> or blocking for instance can easily account for these numbers. Did you check
> for blocking? Did you look at the file stats to see how much physical I/O
> you are doing? And how large is the database itself?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> <shahdha...@.gmail.com> wrote in message
> news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
> > On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> > wrote:
> >> What exactly do you mean by files and 15,000 query? And if sql server
> >> is
> >> using all 16GB then you have a problem with your configuration. You need
> >> to
> >> set a MAX Memory setting of no more than 14GB to leave room for the OS to
> >> do
> >> it's job.
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >> <shahdha...@.gmail.com> wrote in message
> >>news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
> >> > Hi all gurus
> >> > I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
> >> > ram , intel xeon 8 processor machine.
> >> > We have 2 files file1 with 15,000 query and file2 with 60,000 query.
> >> > if run file2 alone it takes first 6 mins and if i run it second time
> >> > it takes 2 mins.
> >> > so this time it uses caches and returns result in 2 mins
> >> > So its taking benefit of cache.
> >> > file2 first 5 min
> >> > file2 second 2 min
> >> > But when i run file1 it takes 2 minutes and file2 takes 6 mins.
> >> > After then again if i run file1 again it takes again 2 mins and file2
> >> > takes 5 mins.
> >> > memory goes to 10 GB from 16 GB.
> >> > file1 first 2 min
> >> > file2 first 5 min
> >> > file1 second 2 min
> >> > file2 second 5 min
> >> > here second time when i run this files it doesn't take benefit of
> >> > cache and take same time
> >> > so why when i run both files sequentially 2 times 2nd time its not
> >> > taking benefit of caching even 6 GB of memory available.If there is 6
> >> > GB memory available then it should keep all data in cache and give
> >> > fast results second time.
> >> > Is there any limitation for caching or query plan? running 2 different
> >> > files will drop cache?
> > Hi Andrew
> > there are about 15000 select queries in first file and 60000 select
> > queries in second file. And MS is not using more than 10 GB.
> > Problem is that if i run only one file 2 times then at second time is
> > uses cache, but if I run file sequentially 2 time then its not using
> > cache.
> > why its doing so?
sorry there is a typo. from 16 gb memory 10 gb is used. so i think if
6 gb is available , I got this info from task manager it showes 10 gb
uses, then in second case when 2 files are sequentially run 2 times
then why its not using cache 2nd times?|||Task manager does not tell you how much memory SQL Server is really using
you should use the Perfmon counters for that. What is the Min and Max
memory set to in SQL Server? And regardless that does not tell you if the
data is cached or not. There are too many other factors that can be
involved. Until you get the actual metrics that I mentioned you are just
guessing and probably barking up the wrong tree.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<shahdharti@.gmail.com> wrote in message
news:6b8e2894-1ccf-4b5c-9a3c-e6ed7c3208c7@.j78g2000hsd.googlegroups.com...
> On Jan 11, 12:22 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
>> 60K selects? Wow that is one heck of a report. You said initially that
>> the memory went from 10GB to 16GB and now you are saying it is not using
>> more than 10GB. How are you determining this? What I said memory wise
>> is
>> still true regardless and should be changed if not that way now. The
>> scenario you use is in no way proof that the cache is not being used.
>> There
>> are many things that can cause this behavior. Poorly executed parallel
>> plans
>> or blocking for instance can easily account for these numbers. Did you
>> check
>> for blocking? Did you look at the file stats to see how much physical
>> I/O
>> you are doing? And how large is the database itself?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>> <shahdha...@.gmail.com> wrote in message
>> news:f38dfcd7-42f4-4875-a6fb-9326c08a55b5@.i72g2000hsd.googlegroups.com...
>> > On Jan 10, 11:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
>> > wrote:
>> >> What exactly do you mean by files and 15,000 query? And if sql
>> >> server
>> >> is
>> >> using all 16GB then you have a problem with your configuration. You
>> >> need
>> >> to
>> >> set a MAX Memory setting of no more than 14GB to leave room for the OS
>> >> to
>> >> do
>> >> it's job.
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >> <shahdha...@.gmail.com> wrote in message
>> >>news:e1cf9402-53a0-4cfc-932d-cb98811fe848@.i7g2000prf.googlegroups.com...
>> >> > Hi all gurus
>> >> > I am using windows 2003 sp2 with SQL Server 2005 SP2. Its a 16 GB
>> >> > ram , intel xeon 8 processor machine.
>> >> > We have 2 files file1 with 15,000 query and file2 with 60,000 query.
>> >> > if run file2 alone it takes first 6 mins and if i run it second time
>> >> > it takes 2 mins.
>> >> > so this time it uses caches and returns result in 2 mins
>> >> > So its taking benefit of cache.
>> >> > file2 first 5 min
>> >> > file2 second 2 min
>> >> > But when i run file1 it takes 2 minutes and file2 takes 6 mins.
>> >> > After then again if i run file1 again it takes again 2 mins and
>> >> > file2
>> >> > takes 5 mins.
>> >> > memory goes to 10 GB from 16 GB.
>> >> > file1 first 2 min
>> >> > file2 first 5 min
>> >> > file1 second 2 min
>> >> > file2 second 5 min
>> >> > here second time when i run this files it doesn't take benefit of
>> >> > cache and take same time
>> >> > so why when i run both files sequentially 2 times 2nd time its not
>> >> > taking benefit of caching even 6 GB of memory available.If there is
>> >> > 6
>> >> > GB memory available then it should keep all data in cache and give
>> >> > fast results second time.
>> >> > Is there any limitation for caching or query plan? running 2
>> >> > different
>> >> > files will drop cache?
>> > Hi Andrew
>> > there are about 15000 select queries in first file and 60000 select
>> > queries in second file. And MS is not using more than 10 GB.
>> > Problem is that if i run only one file 2 times then at second time is
>> > uses cache, but if I run file sequentially 2 time then its not using
>> > cache.
>> > why its doing so?
> sorry there is a typo. from 16 gb memory 10 gb is used. so i think if
> 6 gb is available , I got this info from task manager it showes 10 gb
> uses, then in second case when 2 files are sequentially run 2 times
> then why its not using cache 2nd times?
>
Caching or reusing parameters populated with SqlCommandBuilder.DeriveParameters
Hello,
I have a real heartache with runtime parameter interogation on my DB.
Sure I get the latest and greatest and sure I don't have to type in all those lovely parameter types..but...the hit I take on performance for making no less then 3 DB hits for each SqlAdapter is unreasonable!
So ...I like the idea of maybe calling it once for all my stored procs on application startup...and then maybe saving this in CacheObject.
My problem is that I can't see where you can even serialize a SqlParametersCollection or even for that matter assign it to a Command object. Can you cache a command object ?
LOL
I think I may just have to write some generic routine for creating and populating my command objects based on a key (type) and then use that to fetch my command.Update,
command.Insert and command.
I would like to use the new AsynchBlock to do the fetching of the stored proc parameters and then just pull them from the Cache object...put a file watch so that if the DB's change my params it re-pulls them again.
*nice*....
Then I get the best of both worlds...caching...and no parameter writing...
Ericsp_sproc_columns [[@.procedure_name =] 'name']
[,[@.procedure_owner =] 'owner']
[,[@.procedure_qualifier =] 'qualifier']
[,[@.column_name =] 'column_name']
[,[@.ODBCVer =] 'ODBCVer']
sp_stored_procedures [[@.sp_name =] 'name']
[,[@.sp_owner =] 'owner']
[,[@.sp_qualifier =] 'qualifier']
does that help?|||well yeah that's what I was going to do once I have the params in some cachable state...
I was just wondering if you could fetch the SqlParametersCollection from the Command object...alal IDbCommandParameters
and then cache those dudes...:)
I can manually interogate my hash for the parameters based on type key..."UpdSales", "InsSales","DelSales"...etc..|||those are the system procedures you need to get ALL the procedures from your database, then get their parameters.
Check out the details from books online.|||Duh...
I know that!
The SqlCommandBuilder.DerieveParameters(SqlCommand)
And those parameters have to stored somewhere in command object right?
Ahhh the Parameters which implments the IDbParameterCollection...
So can you fetch or store that Parameters collection...
That is what I"m asking...
I think I will just write the code "AsyncService" to fetch them at Application on start and then store them....I just don't like all those chatty calls ...that's all
Caching of stored procedure
Hello all,
I've got an application that calls a really simple stored procedure - just selects all records from the dbase. The problem is this seems to cache every now and then and as the same table is updated very frequently by other users this means the data returned isn't up to date. I thought it was Sql Server caching the results of the stored procedure, but I can do an iisreset and it will be up to date again. And unless I'm missing some point iisreset has no bearing on Sql Server. So is there an application data cache somewhere that I should be clearing to ensure the recordset is always up to date?
The question that comes immediately to my mind is.. Are you storing the data from the database in some state bags.. like Session state or Application State or.. simply any application caching?
The problem obviously lies with your application... not any sql server caching.. So, how do you retrieve the data that your application use? Make a fresh database call everytime you read that data?
|||Hi,
I was being stupid - I wasn't storing the data in state but I was using paged data and my page number was referenced statically so whenever one user moved the page on everyone saw the older pages!
Doh!
Caching of reports
Hi!
I am using the report service to generate all of my reports. The reports are
generated as pdf and then printed.
The following situaton:
- The user fills out a windows form and previews the report. On the preview
he recognised that there is a mistake in the text he filled out and closes
the preview.
- The user corrects the mistake and previews the report again (same report,
same user, same parameters)
- In the preview the report has not changed !!!!
I have set all the reports not to cache (store no temporary copies of the
report).
When I use SQL Profiler to analyze whats going on on the sql server the
following messages appear:
"exec GetChunkPointerAndLength @.SnapshotDataID = 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
= N'CompiledDefinition', @.ChunkType = 0"
and after that a few calls of:
"exec ReadChunkPortion"
Definitly the stored procedure which provides the values for the report is
not called.
In the scenario shown above the stored procedure must be called everytime
the report is rendered.
How can I configure report services to do so?
Thanks.I am calling the "FlushCache" method before rendering the pdf-file which does
not change the behavior listed below.
"Thomas Vierlinger" schrieb:
> Hi!
> I am using the report service to generate all of my reports. The reports are
> generated as pdf and then printed.
> The following situaton:
> - The user fills out a windows form and previews the report. On the preview
> he recognised that there is a mistake in the text he filled out and closes
> the preview.
> - The user corrects the mistake and previews the report again (same report,
> same user, same parameters)
> - In the preview the report has not changed !!!!
>
> I have set all the reports not to cache (store no temporary copies of the
> report).
> When I use SQL Profiler to analyze whats going on on the sql server the
> following messages appear:
> "exec GetChunkPointerAndLength @.SnapshotDataID => 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> = N'CompiledDefinition', @.ChunkType = 0"
> and after that a few calls of:
> "exec ReadChunkPortion"
> Definitly the stored procedure which provides the values for the report is
> not called.
> In the scenario shown above the stored procedure must be called everytime
> the report is rendered.
> How can I configure report services to do so?
> Thanks.
>
>|||Try passing the following parameter...
to the report...
rs:ClearSession=true
"Thomas Vierlinger" wrote:
> I am calling the "FlushCache" method before rendering the pdf-file which does
> not change the behavior listed below.
> "Thomas Vierlinger" schrieb:
> > Hi!
> >
> > I am using the report service to generate all of my reports. The reports are
> > generated as pdf and then printed.
> >
> > The following situaton:
> > - The user fills out a windows form and previews the report. On the preview
> > he recognised that there is a mistake in the text he filled out and closes
> > the preview.
> > - The user corrects the mistake and previews the report again (same report,
> > same user, same parameters)
> > - In the preview the report has not changed !!!!
> >
> >
> > I have set all the reports not to cache (store no temporary copies of the
> > report).
> > When I use SQL Profiler to analyze whats going on on the sql server the
> > following messages appear:
> >
> > "exec GetChunkPointerAndLength @.SnapshotDataID => > 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> > = N'CompiledDefinition', @.ChunkType = 0"
> >
> > and after that a few calls of:
> >
> > "exec ReadChunkPortion"
> >
> > Definitly the stored procedure which provides the values for the report is
> > not called.
> > In the scenario shown above the stored procedure must be called everytime
> > the report is rendered.
> >
> > How can I configure report services to do so?
> >
> > Thanks.
> >
> >
> >|||As described below I am not rendering with url access.
I am rendering pdf files direkt from C# code using the webservice render
method (ReportingService.Render).
"CCP" schrieb:
> Try passing the following parameter...
> to the report...
> rs:ClearSession=true
>
> "Thomas Vierlinger" wrote:
> > I am calling the "FlushCache" method before rendering the pdf-file which does
> > not change the behavior listed below.
> >
> > "Thomas Vierlinger" schrieb:
> >
> > > Hi!
> > >
> > > I am using the report service to generate all of my reports. The reports are
> > > generated as pdf and then printed.
> > >
> > > The following situaton:
> > > - The user fills out a windows form and previews the report. On the preview
> > > he recognised that there is a mistake in the text he filled out and closes
> > > the preview.
> > > - The user corrects the mistake and previews the report again (same report,
> > > same user, same parameters)
> > > - In the preview the report has not changed !!!!
> > >
> > >
> > > I have set all the reports not to cache (store no temporary copies of the
> > > report).
> > > When I use SQL Profiler to analyze whats going on on the sql server the
> > > following messages appear:
> > >
> > > "exec GetChunkPointerAndLength @.SnapshotDataID => > > 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> > > = N'CompiledDefinition', @.ChunkType = 0"
> > >
> > > and after that a few calls of:
> > >
> > > "exec ReadChunkPortion"
> > >
> > > Definitly the stored procedure which provides the values for the report is
> > > not called.
> > > In the scenario shown above the stored procedure must be called everytime
> > > the report is rendered.
> > >
> > > How can I configure report services to do so?
> > >
> > > Thanks.
> > >
> > >
> > >|||i think u can set that value as a parameter in your report...
"Thomas Vierlinger" wrote:
> As described below I am not rendering with url access.
> I am rendering pdf files direkt from C# code using the webservice render
> method (ReportingService.Render).
>
> "CCP" schrieb:
> > Try passing the following parameter...
> > to the report...
> > rs:ClearSession=true
> >
> >
> > "Thomas Vierlinger" wrote:
> >
> > > I am calling the "FlushCache" method before rendering the pdf-file which does
> > > not change the behavior listed below.
> > >
> > > "Thomas Vierlinger" schrieb:
> > >
> > > > Hi!
> > > >
> > > > I am using the report service to generate all of my reports. The reports are
> > > > generated as pdf and then printed.
> > > >
> > > > The following situaton:
> > > > - The user fills out a windows form and previews the report. On the preview
> > > > he recognised that there is a mistake in the text he filled out and closes
> > > > the preview.
> > > > - The user corrects the mistake and previews the report again (same report,
> > > > same user, same parameters)
> > > > - In the preview the report has not changed !!!!
> > > >
> > > >
> > > > I have set all the reports not to cache (store no temporary copies of the
> > > > report).
> > > > When I use SQL Profiler to analyze whats going on on the sql server the
> > > > following messages appear:
> > > >
> > > > "exec GetChunkPointerAndLength @.SnapshotDataID => > > > 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> > > > = N'CompiledDefinition', @.ChunkType = 0"
> > > >
> > > > and after that a few calls of:
> > > >
> > > > "exec ReadChunkPortion"
> > > >
> > > > Definitly the stored procedure which provides the values for the report is
> > > > not called.
> > > > In the scenario shown above the stored procedure must be called everytime
> > > > the report is rendered.
> > > >
> > > > How can I configure report services to do so?
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >|||and how should that work?
bol says it is an url parameter
"CCP" schrieb:
> i think u can set that value as a parameter in your report...
> "Thomas Vierlinger" wrote:
> > As described below I am not rendering with url access.
> >
> > I am rendering pdf files direkt from C# code using the webservice render
> > method (ReportingService.Render).
> >
> >
> > "CCP" schrieb:
> >
> > > Try passing the following parameter...
> > > to the report...
> > > rs:ClearSession=true
> > >
> > >
> > > "Thomas Vierlinger" wrote:
> > >
> > > > I am calling the "FlushCache" method before rendering the pdf-file which does
> > > > not change the behavior listed below.
> > > >
> > > > "Thomas Vierlinger" schrieb:
> > > >
> > > > > Hi!
> > > > >
> > > > > I am using the report service to generate all of my reports. The reports are
> > > > > generated as pdf and then printed.
> > > > >
> > > > > The following situaton:
> > > > > - The user fills out a windows form and previews the report. On the preview
> > > > > he recognised that there is a mistake in the text he filled out and closes
> > > > > the preview.
> > > > > - The user corrects the mistake and previews the report again (same report,
> > > > > same user, same parameters)
> > > > > - In the preview the report has not changed !!!!
> > > > >
> > > > >
> > > > > I have set all the reports not to cache (store no temporary copies of the
> > > > > report).
> > > > > When I use SQL Profiler to analyze whats going on on the sql server the
> > > > > following messages appear:
> > > > >
> > > > > "exec GetChunkPointerAndLength @.SnapshotDataID => > > > > 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> > > > > = N'CompiledDefinition', @.ChunkType = 0"
> > > > >
> > > > > and after that a few calls of:
> > > > >
> > > > > "exec ReadChunkPortion"
> > > > >
> > > > > Definitly the stored procedure which provides the values for the report is
> > > > > not called.
> > > > > In the scenario shown above the stored procedure must be called everytime
> > > > > the report is rendered.
> > > > >
> > > > > How can I configure report services to do so?
> > > > >
> > > > > Thanks.
> > > > >
> > > > >
> > > > >
I am using the report service to generate all of my reports. The reports are
generated as pdf and then printed.
The following situaton:
- The user fills out a windows form and previews the report. On the preview
he recognised that there is a mistake in the text he filled out and closes
the preview.
- The user corrects the mistake and previews the report again (same report,
same user, same parameters)
- In the preview the report has not changed !!!!
I have set all the reports not to cache (store no temporary copies of the
report).
When I use SQL Profiler to analyze whats going on on the sql server the
following messages appear:
"exec GetChunkPointerAndLength @.SnapshotDataID = 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
= N'CompiledDefinition', @.ChunkType = 0"
and after that a few calls of:
"exec ReadChunkPortion"
Definitly the stored procedure which provides the values for the report is
not called.
In the scenario shown above the stored procedure must be called everytime
the report is rendered.
How can I configure report services to do so?
Thanks.I am calling the "FlushCache" method before rendering the pdf-file which does
not change the behavior listed below.
"Thomas Vierlinger" schrieb:
> Hi!
> I am using the report service to generate all of my reports. The reports are
> generated as pdf and then printed.
> The following situaton:
> - The user fills out a windows form and previews the report. On the preview
> he recognised that there is a mistake in the text he filled out and closes
> the preview.
> - The user corrects the mistake and previews the report again (same report,
> same user, same parameters)
> - In the preview the report has not changed !!!!
>
> I have set all the reports not to cache (store no temporary copies of the
> report).
> When I use SQL Profiler to analyze whats going on on the sql server the
> following messages appear:
> "exec GetChunkPointerAndLength @.SnapshotDataID => 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> = N'CompiledDefinition', @.ChunkType = 0"
> and after that a few calls of:
> "exec ReadChunkPortion"
> Definitly the stored procedure which provides the values for the report is
> not called.
> In the scenario shown above the stored procedure must be called everytime
> the report is rendered.
> How can I configure report services to do so?
> Thanks.
>
>|||Try passing the following parameter...
to the report...
rs:ClearSession=true
"Thomas Vierlinger" wrote:
> I am calling the "FlushCache" method before rendering the pdf-file which does
> not change the behavior listed below.
> "Thomas Vierlinger" schrieb:
> > Hi!
> >
> > I am using the report service to generate all of my reports. The reports are
> > generated as pdf and then printed.
> >
> > The following situaton:
> > - The user fills out a windows form and previews the report. On the preview
> > he recognised that there is a mistake in the text he filled out and closes
> > the preview.
> > - The user corrects the mistake and previews the report again (same report,
> > same user, same parameters)
> > - In the preview the report has not changed !!!!
> >
> >
> > I have set all the reports not to cache (store no temporary copies of the
> > report).
> > When I use SQL Profiler to analyze whats going on on the sql server the
> > following messages appear:
> >
> > "exec GetChunkPointerAndLength @.SnapshotDataID => > 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> > = N'CompiledDefinition', @.ChunkType = 0"
> >
> > and after that a few calls of:
> >
> > "exec ReadChunkPortion"
> >
> > Definitly the stored procedure which provides the values for the report is
> > not called.
> > In the scenario shown above the stored procedure must be called everytime
> > the report is rendered.
> >
> > How can I configure report services to do so?
> >
> > Thanks.
> >
> >
> >|||As described below I am not rendering with url access.
I am rendering pdf files direkt from C# code using the webservice render
method (ReportingService.Render).
"CCP" schrieb:
> Try passing the following parameter...
> to the report...
> rs:ClearSession=true
>
> "Thomas Vierlinger" wrote:
> > I am calling the "FlushCache" method before rendering the pdf-file which does
> > not change the behavior listed below.
> >
> > "Thomas Vierlinger" schrieb:
> >
> > > Hi!
> > >
> > > I am using the report service to generate all of my reports. The reports are
> > > generated as pdf and then printed.
> > >
> > > The following situaton:
> > > - The user fills out a windows form and previews the report. On the preview
> > > he recognised that there is a mistake in the text he filled out and closes
> > > the preview.
> > > - The user corrects the mistake and previews the report again (same report,
> > > same user, same parameters)
> > > - In the preview the report has not changed !!!!
> > >
> > >
> > > I have set all the reports not to cache (store no temporary copies of the
> > > report).
> > > When I use SQL Profiler to analyze whats going on on the sql server the
> > > following messages appear:
> > >
> > > "exec GetChunkPointerAndLength @.SnapshotDataID => > > 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> > > = N'CompiledDefinition', @.ChunkType = 0"
> > >
> > > and after that a few calls of:
> > >
> > > "exec ReadChunkPortion"
> > >
> > > Definitly the stored procedure which provides the values for the report is
> > > not called.
> > > In the scenario shown above the stored procedure must be called everytime
> > > the report is rendered.
> > >
> > > How can I configure report services to do so?
> > >
> > > Thanks.
> > >
> > >
> > >|||i think u can set that value as a parameter in your report...
"Thomas Vierlinger" wrote:
> As described below I am not rendering with url access.
> I am rendering pdf files direkt from C# code using the webservice render
> method (ReportingService.Render).
>
> "CCP" schrieb:
> > Try passing the following parameter...
> > to the report...
> > rs:ClearSession=true
> >
> >
> > "Thomas Vierlinger" wrote:
> >
> > > I am calling the "FlushCache" method before rendering the pdf-file which does
> > > not change the behavior listed below.
> > >
> > > "Thomas Vierlinger" schrieb:
> > >
> > > > Hi!
> > > >
> > > > I am using the report service to generate all of my reports. The reports are
> > > > generated as pdf and then printed.
> > > >
> > > > The following situaton:
> > > > - The user fills out a windows form and previews the report. On the preview
> > > > he recognised that there is a mistake in the text he filled out and closes
> > > > the preview.
> > > > - The user corrects the mistake and previews the report again (same report,
> > > > same user, same parameters)
> > > > - In the preview the report has not changed !!!!
> > > >
> > > >
> > > > I have set all the reports not to cache (store no temporary copies of the
> > > > report).
> > > > When I use SQL Profiler to analyze whats going on on the sql server the
> > > > following messages appear:
> > > >
> > > > "exec GetChunkPointerAndLength @.SnapshotDataID => > > > 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> > > > = N'CompiledDefinition', @.ChunkType = 0"
> > > >
> > > > and after that a few calls of:
> > > >
> > > > "exec ReadChunkPortion"
> > > >
> > > > Definitly the stored procedure which provides the values for the report is
> > > > not called.
> > > > In the scenario shown above the stored procedure must be called everytime
> > > > the report is rendered.
> > > >
> > > > How can I configure report services to do so?
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >|||and how should that work?
bol says it is an url parameter
"CCP" schrieb:
> i think u can set that value as a parameter in your report...
> "Thomas Vierlinger" wrote:
> > As described below I am not rendering with url access.
> >
> > I am rendering pdf files direkt from C# code using the webservice render
> > method (ReportingService.Render).
> >
> >
> > "CCP" schrieb:
> >
> > > Try passing the following parameter...
> > > to the report...
> > > rs:ClearSession=true
> > >
> > >
> > > "Thomas Vierlinger" wrote:
> > >
> > > > I am calling the "FlushCache" method before rendering the pdf-file which does
> > > > not change the behavior listed below.
> > > >
> > > > "Thomas Vierlinger" schrieb:
> > > >
> > > > > Hi!
> > > > >
> > > > > I am using the report service to generate all of my reports. The reports are
> > > > > generated as pdf and then printed.
> > > > >
> > > > > The following situaton:
> > > > > - The user fills out a windows form and previews the report. On the preview
> > > > > he recognised that there is a mistake in the text he filled out and closes
> > > > > the preview.
> > > > > - The user corrects the mistake and previews the report again (same report,
> > > > > same user, same parameters)
> > > > > - In the preview the report has not changed !!!!
> > > > >
> > > > >
> > > > > I have set all the reports not to cache (store no temporary copies of the
> > > > > report).
> > > > > When I use SQL Profiler to analyze whats going on on the sql server the
> > > > > following messages appear:
> > > > >
> > > > > "exec GetChunkPointerAndLength @.SnapshotDataID => > > > > 'A8A41F0F-2C70-4D7D-BE21-DD3BD5F0E921', @.IsPermanentSnapshot = 1, @.ChunkName
> > > > > = N'CompiledDefinition', @.ChunkType = 0"
> > > > >
> > > > > and after that a few calls of:
> > > > >
> > > > > "exec ReadChunkPortion"
> > > > >
> > > > > Definitly the stored procedure which provides the values for the report is
> > > > > not called.
> > > > > In the scenario shown above the stored procedure must be called everytime
> > > > > the report is rendered.
> > > > >
> > > > > How can I configure report services to do so?
> > > > >
> > > > > Thanks.
> > > > >
> > > > >
> > > > >
Subscribe to:
Posts (Atom)