Calculated Measures

We are presently having an issue where if we use calculated measures an MDX
query that takes about 1-2 seconds to run suddenly takes about 1.5 minutes
to run. These measures are not extremely complicated they are simple like
taking TotalDollars/TotalUnits. TotalDollars and TotalUnits are already
measures in the cube. Any ideas why this could be happening?
Thanks,
AlWe are having an issue with Calculated Members and CrossJoins. We have an
MDX query where when we get to a certain granular level we are taking a
considerable performance hit. Take the following query. It takes about 1.5
minutes to run.
WITH MEMBER [Measures].[Average Bill Rate2] AS
'Measures.[Total Revenue]/Measures.[Total Hours] '
SELECT NON EMPTY { [Measures].[Average Bill Rate2], [Measures].[Total
Cost],
[Measures].[Total Hours], [Measures].[Total Revenue] } ON COLUMNS,
NON EMPTY { (
[Profit Center Rollup].[Profit Center Rollups].[Profit Center Level 10].
ALLMEMBERS *
[Project Rollup].[Project Name Attribute].[Project Name
Attribute].ALLMEMBERS *
[Project Type].[Project Types].[Project Type].ALLMEMBERS *
[Transaction Type].[Transactions].[Transaction Type].ALLMEMBERS ) }
If I remove the line [Project Rollup].[Project Name Attribute].[Project Name
Attribute].ALLMEMBERS * from the query it takes 1-2 seconds to run.
Or if I remove "[Measures].[Average Bill Rate2]," from the query it again
takes 1-2 seconds to run. Do you know if there are any work arounds or
fixes to this issue? Also keep in mind that this query is originating in
SSRS. The issue here is that any changes made to the query directly means
that we can no longer use the query designer in SSRS.
Thanks,
Al

Calculate The Time To Run SP

Hi All
There is Any Way To Calculate The Time To Run SP Or Select Statement Before
Run It
For Example
SELECT *
FROM stores
WHERE (state = 'CA')
How long Time Take This Query to Run
Thankstry using
SET STATISTICS TIME ON|||No, there are no such facilities in SQL Server. One of the reasons is that t
he optimize can pick
different executing plans, and any estimates based on one execution plan wil
l be totally off if some
other execution plan is selected. You also have the probability of blocking.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Taha" <taha105@.hotmail.com> wrote in message news:efD9epuVGHA.5364@.tk2msftngp13.phx.gbl...

> Hi All
> There is Any Way To Calculate The Time To Run SP Or Select Statement Befor
e Run It
> For Example
> SELECT *
> FROM stores
> WHERE (state = 'CA')
> How long Time Take This Query to Run
> Thanks
>|||Thank You Fro Reply
But What I Looking For UDF Or SP That Return Elapsed time For Select
Statement I Send To This SP Or UDF Whit out Run it Men Not Need Data Return
Just The Time To Calculate This Statement
Because I Have Large data and I want say to the user how many this query
take time
Thanks
"Taha" <taha105@.hotmail.com> wrote in message
news:efD9epuVGHA.5364@.tk2msftngp13.phx.gbl...
> Hi All
> There is Any Way To Calculate The Time To Run SP Or Select Statement
> Before Run It
> For Example
> SELECT *
> FROM stores
> WHERE (state = 'CA')
> How long Time Take This Query to Run
> Thanks
>|||As a maximum you could say them an approximation
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Taha" wrote:

> Thank You Fro Reply
> But What I Looking For UDF Or SP That Return Elapsed time For Select
> Statement I Send To This SP Or UDF Whit out Run it Men Not Need Data Retur
n
> Just The Time To Calculate This Statement
> Because I Have Large data and I want say to the user how many this query
> take time
> Thanks
>
> "Taha" <taha105@.hotmail.com> wrote in message
> news:efD9epuVGHA.5364@.tk2msftngp13.phx.gbl...
>
>|||There is no way to find an accurate time for an SP to run. As Tibor Karaszi
had said, the same SP can run for different periods depending on the server
load, configuration, database size and the mood of the SQL Engine :)
You can only find the time it took for the current execution.|||Ok How find the time it took for the current execution Please
Only Time return Parameter I need
Thanks
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:8BA9246F-EC38-491C-B1B6-E7B9B3ADE53D@.microsoft.com...
> There is no way to find an accurate time for an SP to run. As Tibor
> Karaszi
> had said, the same SP can run for different periods depending on the
> server
> load, configuration, database size and the mood of the SQL Engine :)
> You can only find the time it took for the current execution.
>|||You have to do this yourself. Either in the client application (declare a va
riable, set it to
current time before execution and after execution check number of ms or s el
apsed), or in the stored
procedure with same basic logic and have an output parm of the procedure whe
re you send out the
number of ms.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Taha" <taha105@.hotmail.com> wrote in message news:eRATWOxVGHA.440@.TK2MSFTNGP10.phx.gbl...[
color=darkred]
> Ok How find the time it took for the current execution Please
> Only Time return Parameter I need
> Thanks
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:8BA9246F-EC38-491C-B1B6-E7B9B3ADE53D@.microsoft.com...
>[/color]|||Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OguNqexVGHA.1536@.TK2MSFTNGP15.phx.gbl...
> You have to do this yourself. Either in the client application (declare a
> variable, set it to current time before execution and after execution
> check number of ms or s elapsed), or in the stored procedure with same
> basic logic and have an output parm of the procedure where you send out
> the number of ms.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Taha" <taha105@.hotmail.com> wrote in message
> news:eRATWOxVGHA.440@.TK2MSFTNGP10.phx.gbl...
>|||You can use profiler also.
Regards
Amish shahsql

Calculate Lastmonth in an SP

Hi - from within an SP (which is run automatically), how would I
determine range for 'last month' - eg. this is April, so I would like my
SP to be able to calculate 1 March 2005 to 31 March 2005?
My problem is for this: I have a .net web application which keeps track
of time for consultants. At the moment, on the first of each month, I
go to a web page, enter the start date and end date of the previous
month, and hit a button - this calculates, depending on the time the
consultant has spent with each client, appends the client ID, the agent
ID, and the commission to a 'billing' table.
The consultants then look each month, and click a button to send an
email to each of the clients (if they agree the charges).
What I would like to do, is have an SP, run from a job on the first of
each month, so that I don't have to manually run this process.
So, in the SP I would have something like:
Insert into charges (clientid, agentid, commission) values (@.cliendid,
@.agentid, @.commission) WHERE dateCharged = @.lastmonth
But how do I set @.lastmonth from within the SP - without me having to
become involved?
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***Mark
select dateadd(m,datediff(m,0,getdate())-1,0)as FirstDay,
dateadd(m,datediff(m,0,getdate()),0)-1 as LastDay
"Mark" <anonymous@.devdex.com> wrote in message
news:ezIhI4yPFHA.2652@.TK2MSFTNGP10.phx.gbl...
> Hi - from within an SP (which is run automatically), how would I
> determine range for 'last month' - eg. this is April, so I would like my
> SP to be able to calculate 1 March 2005 to 31 March 2005?
> My problem is for this: I have a .net web application which keeps track
> of time for consultants. At the moment, on the first of each month, I
> go to a web page, enter the start date and end date of the previous
> month, and hit a button - this calculates, depending on the time the
> consultant has spent with each client, appends the client ID, the agent
> ID, and the commission to a 'billing' table.
> The consultants then look each month, and click a button to send an
> email to each of the clients (if they agree the charges).
> What I would like to do, is have an SP, run from a job on the first of
> each month, so that I don't have to manually run this process.
> So, in the SP I would have something like:
> Insert into charges (clientid, agentid, commission) values (@.cliendid,
> @.agentid, @.commission) WHERE dateCharged = @.lastmonth
> But how do I set @.lastmonth from within the SP - without me having to
> become involved?
> Thanks for any help,
> Mark
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Hi Mark
You may want to use a calendar table to do this see
http://www.aspfaq.com/show.asp_?id=2519 or you can use the dateadd function
directly.
John
"Mark" wrote:

> Hi - from within an SP (which is run automatically), how would I
> determine range for 'last month' - eg. this is April, so I would like my
> SP to be able to calculate 1 March 2005 to 31 March 2005?
> My problem is for this: I have a .net web application which keeps track
> of time for consultants. At the moment, on the first of each month, I
> go to a web page, enter the start date and end date of the previous
> month, and hit a button - this calculates, depending on the time the
> consultant has spent with each client, appends the client ID, the agent
> ID, and the commission to a 'billing' table.
> The consultants then look each month, and click a button to send an
> email to each of the clients (if they agree the charges).
> What I would like to do, is have an SP, run from a job on the first of
> each month, so that I don't have to manually run this process.
> So, in the SP I would have something like:
> Insert into charges (clientid, agentid, commission) values (@.cliendid,
> @.agentid, @.commission) WHERE dateCharged = @.lastmonth
> But how do I set @.lastmonth from within the SP - without me having to
> become involved?
> Thanks for any help,
> Mark
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

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?
>

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
>> >
>

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
>>

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

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?

Caching in Reporting Services

Consider the following:
I have a report that takes in 2 parameters: Start Date and End Date.
If I run the reporet twice, thefirst time passing 1/1/1900-1/1/2007 as
the parameters
and then I run it again with date range 1/1/2000-1/1/2001
Will RS go back to the Database to retrieve that data? Or is it cached
and it will just generate the results immidietly.
regards,
Stas K.No caching occurs if a parameter changes in any way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1136313076.592719.46480@.g44g2000cwa.googlegroups.com...
> Consider the following:
> I have a report that takes in 2 parameters: Start Date and End Date.
> If I run the reporet twice, thefirst time passing 1/1/1900-1/1/2007 as
> the parameters
> and then I run it again with date range 1/1/2000-1/1/2001
> Will RS go back to the Database to retrieve that data? Or is it cached
> and it will just generate the results immidietly.
> regards,
> Stas K.
>|||Alright.
I heard there is a Snapshot functionality...
Using Filters Instead of Query Parameters
Reporting Services has several methods for dynamically filtering report
contents, including the following:
=B7Query parameters filter data at the source as it is retrieved.
=B7Report filters, applied to a dataset or data region, limit the data
that is displayed from a generated report.
Using filters retrieves all data, but only data that is relevant to the
user is displayed. This may be less efficient on an individual report
basis than filtering at the source. However, it lets you retrieve the
data once from the source and store in it a snapshot to serve many
different user communities. On the other hand, when using query
parameters, you must revisit the data source for each new value of the
query parameters. Filters enable you to use execution snapshots and
still get full parameterization.
This is where I got stuck - perhaps im missunderstadning the statement.
What does this mean?
regards,
Stas K.|||If you want to use snapshots combined with a filter then you could do this.
Note though that all the data is returned to reporting services and stored
for filtering later. If your data is large at all this could be problematic.
My opinion is that snapshots should be a last resort and only after a lot of
thought.
So, are you using filters or query paraemeters. You are using query
parameters if you query definition looks something like this:
select * from mytable where mydatefield > @.StartDate and mydatefield <
@.EndDate
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1136315942.418004.219830@.g44g2000cwa.googlegroups.com...
Alright.
I heard there is a Snapshot functionality...
Using Filters Instead of Query Parameters
Reporting Services has several methods for dynamically filtering report
contents, including the following:
·Query parameters filter data at the source as it is retrieved.
·Report filters, applied to a dataset or data region, limit the data
that is displayed from a generated report.
Using filters retrieves all data, but only data that is relevant to the
user is displayed. This may be less efficient on an individual report
basis than filtering at the source. However, it lets you retrieve the
data once from the source and store in it a snapshot to serve many
different user communities. On the other hand, when using query
parameters, you must revisit the data source for each new value of the
query parameters. Filters enable you to use execution snapshots and
still get full parameterization.
This is where I got stuck - perhaps im missunderstadning the statement.
What does this mean?
regards,
Stas K.

Cached data being displayed

If I run a report, then change some data in the database. Then, navigate
away from my sql report, go back back in and re-run the report, it doesn't
show the data that I just changed in the database. I have to actually click
the 'refresh' button to get the report actually "re-run". It appears to
display cached data.
How can I tell the report to not do this?I had the same issue and found the solution was to add another rs command to
the report url...
i.e. rs:ClearSession=true
e.g.
http://server/reportserver?/YourReport&rs:Command=Render&rs:ClearSession=true
"Brian Patrick" wrote:
> If I run a report, then change some data in the database. Then, navigate
> away from my sql report, go back back in and re-run the report, it doesn't
> show the data that I just changed in the database. I have to actually click
> the 'refresh' button to get the report actually "re-run". It appears to
> display cached data.
> How can I tell the report to not do this?
>
>|||I too had the same issue. I did tried the same solution as you have given here.
I found that even a true or false has the same effect ie.. clearing the
cached data for the both the values. Only we need to specify any one of them.
And in case we don't specify rs:ClearSession='' cached data is not cleared.
Did u experiance that?
Suresh
"NH" wrote:
> I had the same issue and found the solution was to add another rs command to
> the report url...
> i.e. rs:ClearSession=true
> e.g.
> http://server/reportserver?/YourReport&rs:Command=Render&rs:ClearSession=true
> "Brian Patrick" wrote:
> > If I run a report, then change some data in the database. Then, navigate
> > away from my sql report, go back back in and re-run the report, it doesn't
> > show the data that I just changed in the database. I have to actually click
> > the 'refresh' button to get the report actually "re-run". It appears to
> > display cached data.
> >
> > How can I tell the report to not do this?
> >
> >
> >|||Well I have not tested this. It sounds like odd behaviour.
"Suresh" wrote:
> I too had the same issue. I did tried the same solution as you have given here.
> I found that even a true or false has the same effect ie.. clearing the
> cached data for the both the values. Only we need to specify any one of them.
> And in case we don't specify rs:ClearSession='' cached data is not cleared.
> Did u experiance that?
> Suresh
>
> "NH" wrote:
> > I had the same issue and found the solution was to add another rs command to
> > the report url...
> >
> > i.e. rs:ClearSession=true
> >
> > e.g.
> > http://server/reportserver?/YourReport&rs:Command=Render&rs:ClearSession=true
> >
> > "Brian Patrick" wrote:
> >
> > > If I run a report, then change some data in the database. Then, navigate
> > > away from my sql report, go back back in and re-run the report, it doesn't
> > > show the data that I just changed in the database. I have to actually click
> > > the 'refresh' button to get the report actually "re-run". It appears to
> > > display cached data.
> > >
> > > How can I tell the report to not do this?
> > >
> > >
> > >|||We had the same issue. Impmented rs:ClearSession=true and that worked ome
time ago. Now it no longer worls on a RS sp 1 Windows Server 2003 with a
APS.NET app accessing reports with url. This is causing major issues with
client and the on-line business application not reporting correctly as data
enry occur. Surley Microsoft has some solution?
"NH" wrote:
> Well I have not tested this. It sounds like odd behaviour.
> "Suresh" wrote:
> > I too had the same issue. I did tried the same solution as you have given here.
> > I found that even a true or false has the same effect ie.. clearing the
> > cached data for the both the values. Only we need to specify any one of them.
> >
> > And in case we don't specify rs:ClearSession='' cached data is not cleared.
> > Did u experiance that?
> >
> > Suresh
> >
> >
> > "NH" wrote:
> >
> > > I had the same issue and found the solution was to add another rs command to
> > > the report url...
> > >
> > > i.e. rs:ClearSession=true
> > >
> > > e.g.
> > > http://server/reportserver?/YourReport&rs:Command=Render&rs:ClearSession=true
> > >
> > > "Brian Patrick" wrote:
> > >
> > > > If I run a report, then change some data in the database. Then, navigate
> > > > away from my sql report, go back back in and re-run the report, it doesn't
> > > > show the data that I just changed in the database. I have to actually click
> > > > the 'refresh' button to get the report actually "re-run". It appears to
> > > > display cached data.
> > > >
> > > > How can I tell the report to not do this?
> > > >
> > > >
> > > >

Cache plan different using sp_prepare and sp_executesql.

I have a 3rd party application that uses sp_prepare and sp_execute for data retreival. The following statement takes around 40 seconds to run:

declare @.P1 int

exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4 bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', N'SELECT SHAPE ,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINCT sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AND SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_ ,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fid AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320, 16825) ))', 1 select @.P1

exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840, 117193071

The query plan created by sp_prepare is used during the sp_execute but it's very slow compared to running this query ad hoc or with sp_executesql. If I clear the proc cache before running the sp_execute this runs in less than 1 second. The tables used are pretty large (8 - 15 million rows) but are indexed correctly and I've updated the statistics and rebuilt the indexes but neither improves the performance.

Does anyone know why using the sp_prepare statement causes a poor query plan?

Thanks.

Doug Matney

Hi

I have come across an interesting article regarding sp_prepare. Hope , it'll be useful for you too

http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=51

NB.

cache

Sometimes we expect a report to run from cache and it doesnt. Scenario is we
subscribe to the report to run at 3 am and set execution to cache report and
expire at midnight. So this way when a user runs during the day, the report
is already cached
However I noticed in the executionlog table that when the report runs from a
subscription it runs as MHTML and when the user runs from browser it runs
from HTML 4.0 format and I think these differences in formats throw off the
cache execution status.
What is the difference in formats.
I also notice that sometimes a subscription executes once but the user gets
two emails. They try to export to excel and get a message that they do not
have sufficient permissions. My thought was that Installation of Microsoft
Publisher put Office in Read Only mode. We have had that issue with Office
Web Component in the past.
Assistance with these issues is appreciated
ThanksPlease answer my post
Thanks
"Will Byron" <WillB@.CEPSystems.com> wrote in message
news:%23G%23q9rBcEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Sometimes we expect a report to run from cache and it doesnt. Scenario is
we
> subscribe to the report to run at 3 am and set execution to cache report
and
> expire at midnight. So this way when a user runs during the day, the
report
> is already cached
> However I noticed in the executionlog table that when the report runs from
a
> subscription it runs as MHTML and when the user runs from browser it runs
> from HTML 4.0 format and I think these differences in formats throw off
the
> cache execution status.
> What is the difference in formats.
> I also notice that sometimes a subscription executes once but the user
gets
> two emails. They try to export to excel and get a message that they do not
> have sufficient permissions. My thought was that Installation of Microsoft
> Publisher put Office in Read Only mode. We have had that issue with Office
> Web Component in the past.
> Assistance with these issues is appreciated
> Thanks
>|||The format should not be a factor in the cachine decision, but different
parameters can be. The cache key is composed of the report and its
parameters. Are you running the report with the same parameters in the two
cases?
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Will Byron" <will.byron@.maxqtech.com> wrote in message
news:OaIZOJOcEHA.1408@.TK2MSFTNGP12.phx.gbl...
> Please answer my post
> Thanks
> "Will Byron" <WillB@.CEPSystems.com> wrote in message
> news:%23G%23q9rBcEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Sometimes we expect a report to run from cache and it doesnt. Scenario
is
> we
> > subscribe to the report to run at 3 am and set execution to cache report
> and
> > expire at midnight. So this way when a user runs during the day, the
> report
> > is already cached
> >
> > However I noticed in the executionlog table that when the report runs
from
> a
> > subscription it runs as MHTML and when the user runs from browser it
runs
> > from HTML 4.0 format and I think these differences in formats throw off
> the
> > cache execution status.
> >
> > What is the difference in formats.
> >
> > I also notice that sometimes a subscription executes once but the user
> gets
> > two emails. They try to export to excel and get a message that they do
not
> > have sufficient permissions. My thought was that Installation of
Microsoft
> > Publisher put Office in Read Only mode. We have had that issue with
Office
> > Web Component in the past.
> > Assistance with these issues is appreciated
> > Thanks
> >
> >
>

C# SQL-DMO ExecuteImmediate

Received by email from LineVoltageHalogen [tropicalfruitdrops@.yahoo.com]

> I am trying to run a sql script via SQL-DMO. The script just rebuilds
> some stored procs and I can get it to work, however it always runs against
> the "master" database. Could you show me how to specify which database?
> Here is what my code looks like:
> SQLDMO.SQLServer2 DMOSQLServerName = new SQLDMO.SQLServer2();
> SQLDMO.Database2 DMOPerStoreDbName = new SQLDMO.Database2();
> DMOSQLServerName.Connect(myGetConfigData.OlapServe rName,myGetConfigData.OlapUserLogin,OlapPass);
> DMOPerStoreDbName.Name =
> myConnectionData.OlapDatabaseName.ToString().Trim( );
> if (File.Exists(@."MyScript.sql"))
> {
> SR2=File.OpenText(@."MyScript.sql");
> S2=SR2.ReadLine();
> try
> {
> SQLScript = SR2.ReadToEnd();
> SR2.Close();
> DMOPerStoreDbName.ExecuteImmediate(SQLScript,SQLDM O.SQLDMO_EXEC_TYPE.SQLDMOExec_Default,
> null);
> }
>
> So as you can see the script runs but against the master database, I need
> it to run against a database I specify. Can you help?
>
> TFD

That's because you're setting the database name, instead of getting a
reference to an existing database from the server's Databases collection -
this code works for me:

SQLDMO.SQLServer2 srv = new SQLDMO.SQLServer2();
SQLDMO._Database db = new SQLDMO.Database();

srv.Name = "MyServer";
srv.LoginSecure = true;
srv.Connect(null,null,null);

db = srv.Databases.Item("MyDatabase", null);
db.ExecuteImmediate(" /* SQL or script contents go here */ ",
SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, null);

srv.DisConnect();

SimonThank You.

TFD

C drive not big enough

I am installing the X86 Executable version of SQL Server. I copied it to my desktop and clicked RUN. It extracts the files and then the Install Wizard tells me there is not enough space on the C Drive to load the program. I have 23 gigabits of free space and the program only takes about 900 megabits.

Is there a solution to this problem?

Thanks,

Modez

Hi Modez.

Is this Sql 2000 or Sql 2005?

Try creating a 'dummy' text file that is a few hundred mb in size or more (just create some garbage in the file and copy/paste it until the file becomes large enough), then try running the setup. If that works, you can delete the garbage file on completion.

Repost if you continue to have troubles,

HTH

|||

Chad:

It is 2005. I tried making a txt. file and got the same message. Then I tried to download it again and run it from the web site instead of saving it to my desktop but it stills says my C: drive is not big enough.

Do you have any other suggestions?

Thanks,

Modez

by vb.net create exe File to update sql server

is there any chance to create an exe file to update the sql server database by uisng windows schdule ?

for example this exe file will run to update my database, every night @. 12:00 AM.

this exe should be in vb.net

pllllzzzz help

What is your design for that ? Do you want to execute DDL or DML or just maintainance on the database ? You might check the option of the SQL Server Agent, which does Scheduling for SQL Server. More information would be helpful to help you.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

You can also VBScript from SQL Agent…

button and queries

Is it possible to create a button/icon to run a query?sorry..to run a stored proc.|||from what? web (php/asp) or from your desktop on your OS?|||sorry..yeah, from my dektop|||One way could be to write a local scriptfile in vbscript or so and then connect to db and execute the SP.|||yeah, possile, it would just be handy if there was a facility within sql server to create a button

Business Intel. Dev. Studio loads VS.Net 2005

Hi,
I installed the Business Intelligence Development Studio but when I run it
it is just running my VS.Net 2005. Is this what it is supposed to do? I
want to run some imports/exports and it looked like I needed that installed.
There is no option in SQL Manager Studio to do Imports & Exports. At least
not in the Express CTP version. Does anyone know the EXE name of the file
that is supposed to run when launching the BIDS? The old Enterprise Manager
made importing and exporting a breeze and I'd really like to find where to
do that in SQL Server 2005. Thanks for any help!
Yes, BIDS is just a cut down VS2005 with limited projects avaulable. If you
have the full version of VS2005 installed it will just launch that and you
should see the BI project types in there. BIDS does not have any
import/export functionality, that's in Management Studio (full version not
Express version). Since it relies on SSIS which does not come with Express
then it makes sense it's no there. I'm not sure if this will be added at a
later stage to improve the usefulness of SSMS Express.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"HTML" <test@.test.123> wrote in message
news:uH3UG9aKGHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I installed the Business Intelligence Development Studio but when I run it
> it is just running my VS.Net 2005. Is this what it is supposed to do? I
> want to run some imports/exports and it looked like I needed that
> installed. There is no option in SQL Manager Studio to do Imports &
> Exports. At least not in the Express CTP version. Does anyone know the
> EXE name of the file that is supposed to run when launching the BIDS? The
> old Enterprise Manager made importing and exporting a breeze and I'd
> really like to find where to do that in SQL Server 2005. Thanks for any
> help!
>

BulkLoad Problem

Anyone know why this will not work... when I run the script it just inserts
one blank row... No Data !
The Script...
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=SqlXmlTest;integrated security=SSPI"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
set objBL=Nothing
The Schema...
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:relation="Invoices" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" type="xsd:string" />
<xsd:element name="Supplier" type="xsd:string" />
<xsd:element name="NetAmount" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Data...
<?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
NetAmount="68.66" /></Invoices>
The Table...
CREATE TABLE [dbo].[Invoices] (
[Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
[Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
[NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
Your schema doesn't actually describe your XML. Try something like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" sql:relation="Invoices" />
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field=
"Invoice"/>
<xsd:attribute name="Supplier" type="xsd:string" sql:field=
"Supplier"/>
<xsd:attribute name="NetAmount" type="xsd:string"
sql:field="NetAmount"/>
</xsd:complexType>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:72538FD0-A75B-483F-A549-37C993C21432@.microsoft.com...
Anyone know why this will not work... when I run the script it just inserts
one blank row... No Data !
The Script...
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=SqlXmlTest;integrated security=SSPI"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
set objBL=Nothing
The Schema...
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:relation="Invoices" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" type="xsd:string" />
<xsd:element name="Supplier" type="xsd:string" />
<xsd:element name="NetAmount" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Data...
<?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
NetAmount="68.66" /></Invoices>
The Table...
CREATE TABLE [dbo].[Invoices] (
[Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
[Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
[NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
|||Oops! Sorry, I meant:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" sql:relation="Invoices">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field="Invoice"
/>
<xsd:attribute name="Supplier" type="xsd:string"
sql:field="Supplier" />
<xsd:attribute name="NetAmount" type="xsd:string"
sql:field="NetAmount" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:u0GX%23FYpEHA.3392@.TK2MSFTNGP15.phx.gbl...
Your schema doesn't actually describe your XML. Try something like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" sql:relation="Invoices" />
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field=
"Invoice"/>
<xsd:attribute name="Supplier" type="xsd:string" sql:field=
"Supplier"/>
<xsd:attribute name="NetAmount" type="xsd:string"
sql:field="NetAmount"/>
</xsd:complexType>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:72538FD0-A75B-483F-A549-37C993C21432@.microsoft.com...
Anyone know why this will not work... when I run the script it just inserts
one blank row... No Data !
The Script...
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=SqlXmlTest;integrated security=SSPI"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
set objBL=Nothing
The Schema...
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:relation="Invoices" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" type="xsd:string" />
<xsd:element name="Supplier" type="xsd:string" />
<xsd:element name="NetAmount" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Data...
<?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
NetAmount="68.66" /></Invoices>
The Table...
CREATE TABLE [dbo].[Invoices] (
[Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
[Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
[NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
|||Thank you ! I am very grateful for your help.
Cheers,
Rob
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:OomSYYYpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> Oops! Sorry, I meant:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Invoices" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Invoice" sql:relation="Invoices">
> <xsd:complexType>
> <xsd:attribute name="id" type="xsd:string" sql:field="Invoice"
> />
> <xsd:attribute name="Supplier" type="xsd:string"
> sql:field="Supplier" />
> <xsd:attribute name="NetAmount" type="xsd:string"
> sql:field="NetAmount" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:u0GX%23FYpEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Your schema doesn't actually describe your XML. Try something like this:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Invoices" sql:is-constant="1" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Invoice" sql:relation="Invoices" />
> <xsd:complexType>
> <xsd:attribute name="id" type="xsd:string" sql:field=
> "Invoice"/>
> <xsd:attribute name="Supplier" type="xsd:string" sql:field=
> "Supplier"/>
> <xsd:attribute name="NetAmount" type="xsd:string"
> sql:field="NetAmount"/>
> </xsd:complexType>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Rob C" <RobC@.discussions.microsoft.com> wrote in message
> news:72538FD0-A75B-483F-A549-37C993C21432@.microsoft.com...
> Anyone know why this will not work... when I run the script it just
> inserts
> one blank row... No Data !
> The Script...
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
> objBL.ConnectionString = "provider=SQLOLEDB;data
> source=localhost;database=SqlXmlTest;integrated security=SSPI"
> objBL.ErrorLogFile = "c:\error.log"
> objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
> set objBL=Nothing
> The Schema...
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Invoices" sql:relation="Invoices" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Invoice" type="xsd:string" />
> <xsd:element name="Supplier" type="xsd:string" />
> <xsd:element name="NetAmount" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> The Data...
> <?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
> NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
> NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
> NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
> NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
> NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
> NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
> NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
> NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
> NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
> NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
> NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
> NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
> NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
> NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
> NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
> NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
> NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
> NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
> NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
> NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
> NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
> NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
> NetAmount="68.66" /></Invoices>
> The Table...
> CREATE TABLE [dbo].[Invoices] (
> [Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
> [Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
> [NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
> ) ON [PRIMARY]
>
>
>