Launch Subscription via VB Code

I have created a subscription via Report Manager & would like to launch it
via vb code or rs script on any given date or time. That is, even though the
subcription may have a set date/time, via CODe I would launch it & modified
its date & time as necessary.
--
EdgarNot particularly elegant, but you can do this in the following way:
1) create a shared schedule that fires once, in the past. Save the Schedule
ID you get back
2) create a subscription that is triggered by this shared schedule.
3) use some code to call the FireEvent method and submit the
TimedSubscription event as the EventType and the schedule ID from (1) as the
MatchData.
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"mresanchez" <mresanchez@.donot-spam.com> wrote in message
news:045CCA4A-B712-489D-8609-068642154128@.microsoft.com...
>I have created a subscription via Report Manager & would like to launch it
> via vb code or rs script on any given date or time. That is, even though
> the
> subcription may have a set date/time, via CODe I would launch it &
> modified
> its date & time as necessary.
> --
> Edgar|||Lukasz,
I tried your solution & everything works fine except that I get an error
saying that the subscription cannot be found. Even though the subscription
ID is listed within SQL Server. Below is the output I get with the error. I
also Echo the subscription ID & the line so I can see the ID which later I
find in the subscriptions table in SQL Svr. Is there a particular way to
format the subscriptionID in order for RS to see it?
The Current Subscription is: 0d49d301-ecd8-43fc-8319-21ec19e82f77
==========================================System.Web.Services.Protocols.SoapException:
System.Web.Services.Protocols.SoapException: The subsc
ription cannot be found. The subscription identifier that is provided to an
operation cannot be located in the report server database. -->
Microsoft.ReportingServices.Diagnostics.Utilities.Subscrip
tionNotFoundException: The subscription cannot be found. The subscription
identifier that is provid
ed to an operation cannot be located in the report server database.
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.WebServer.ReportingService.GetSubscriptionProperties(String Subsc
riptionID, String& Owner, ExtensionSettings& ExtensionSettings, String&
Description, ActiveState& A
ctive, String& Status, String& EventType, String& MatchData,
ParameterValue[]& Parameters)
at
System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message,
WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
methodName, Object[] param
eters)
at
Microsoft.SqlServer.ReportingServices.ReportingService.GetSubscriptionProperties(String Subsc
riptionID, ExtensionSettings& ExtensionSettings, String& Description,
ActiveState& Active, String&
Status, String& EventType, String& MatchData, ParameterValue[]& Parameters)
at ____ScriptClass.Main()
The command completed successfullysql

Lats execution time of stored procedure

Hello,
Is it possible to know the last time that a stored procedure was executed /
invoced?
Thanks and best regards,
Jorge
Hi,
No, By default sql server will not store those information.
The only way to get this is by reading the tranasction log using tools like
Log explorer (www.lumigent.com)
or by running SQL Profiler.
Thanks
Hari
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
> Hello,
> Is it possible to know the last time that a stored procedure was executed
> /
> invoced?
> Thanks and best regards,
> Jorge
>
|||Thank and best regards.
Jorge
"Hari Prasad" wrote:

> Hi,
> No, By default sql server will not store those information.
> The only way to get this is by reading the tranasction log using tools like
> Log explorer (www.lumigent.com)
> or by running SQL Profiler.
> Thanks
> Hari
> SQL Server MVP
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
>
>

Lats execution time of stored procedure

Hello,
Is it possible to know the last time that a stored procedure was executed /
invoced?
Thanks and best regards,
JorgeHi,
No, By default sql server will not store those information.
The only way to get this is by reading the tranasction log using tools like
Log explorer (www.lumigent.com)
or by running SQL Profiler.
Thanks
Hari
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
> Hello,
> Is it possible to know the last time that a stored procedure was executed
> /
> invoced?
> Thanks and best regards,
> Jorge
>|||Thank and best regards.
Jorge
"Hari Prasad" wrote:
> Hi,
> No, By default sql server will not store those information.
> The only way to get this is by reading the tranasction log using tools like
> Log explorer (www.lumigent.com)
> or by running SQL Profiler.
> Thanks
> Hari
> SQL Server MVP
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
> > Hello,
> >
> > Is it possible to know the last time that a stored procedure was executed
> > /
> > invoced?
> >
> > Thanks and best regards,
> > Jorge
> >
> >
>
>

Lats execution time of stored procedure

Hello,
Is it possible to know the last time that a stored procedure was executed /
invoced?
Thanks and best regards,
JorgeHi,
No, By default sql server will not store those information.
The only way to get this is by reading the tranasction log using tools like
Log explorer (www.lumigent.com)
or by running SQL Profiler.
Thanks
Hari
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
> Hello,
> Is it possible to know the last time that a stored procedure was executed
> /
> invoced?
> Thanks and best regards,
> Jorge
>|||Thank and best regards.
Jorge
"Hari Prasad" wrote:

> Hi,
> No, By default sql server will not store those information.
> The only way to get this is by reading the tranasction log using tools lik
e
> Log explorer (www.lumigent.com)
> or by running SQL Profiler.
> Thanks
> Hari
> SQL Server MVP
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
>
>

Latest value for all members at a given point in time

We have a fact table with a current balance record. For this balance rows are added to the table only when the balance has changed. This way we can reduce the volume in the fact table by not saving redundant information for every point in time that we want to analyze.

In SQL it is a simple task to get a view of the lastest fact records for a given point in time. SELECT f1.* FROM factTable f1 WHERE f1.timestamp = (SELECT MAX(f2.timestamp) FROM factTable f2 WHERE f2.timestamp <= givenPointInTime AND f2.balanceKey = f1.balanceKey). In other words, the given point in time directly determines which rows should be fetched from our fact table, one row for each balance record (balanceKey). This row will of course include several dimension attributes, which represent the point in time when the balance changed.

Now, we would like to reproduce this behaviour in our Analysis Services 2005 cube. Using a time dimension, we would like to be able to select a given point in time and have the cube return all rows previous to that time, one row for each balanceKey. Not only the rows matching the given point in time. How can this be done? For the other dimension attributes, they should behave in a normal way, such that for the returned rows, you can slice using any combination of them.

Any help or ideas are appreciated,
Lars

Assuming that you're using AS 2005 Enterprise Edition, you could try the "Last Nonempty Child" aggregation:

http://msdn2.microsoft.com/en-us/library/ms175356.aspx

>>

SQL Server 2005 Books Online

Defining Semiadditive Behavior

Semiadditive measures, which do not uniformly aggregate across all dimensions, are very common in many business scenarios. Every cube that is based on snapshots of balances over time exhibits this problem. You can find these snapshots in applications dealing with securities, account balances, budgeting, human resources, insurance policies and claims, and many other business domains.

...

LastNonEmpty

The member value is evaluated as the value of its last child along the time dimension that contains data.

>>

|||

Thanks Deepak. We are on an Enterprise Edition (pre-SP1) but we do not get the desired behaviour using the "Last Nonempty Child" aggregation. In fact, it doesn't seem to make any difference at all from using SUM. We have created an example containing a relational database with a simple fact table and an analysis services database built upon it with a measure called "Account Balance". If we browse the cube and add the measure and the Account dimension to the results pane, then filter based on the Time dimension and select the date 2005-12-31, Account number 3 should display the balance 400. Currently it does not.

The example (SQL script + XMLA script) can be found at: http://www.intellibis.se/pub/CumulativeExample.zip

Can anyone help us?

Regards,
Lars

sql

Latest modification time of a table

Is there any way to get the latest modification time of a table (modified by
either others or myself)?
TIAUnfoortunately, the only way to do this is to write code, (a trigger,
probbaly) that would record, (somewhere) the current datetime whenever
someone updates inserts or deletes a record in the table. If that is not
being done, then you'reout of luck for past changes, but you can implement
something like this for future changes, if you wanted...
"Krygim" wrote:

> Is there any way to get the latest modification time of a table (modified
by
> either others or myself)?
> TIA
>
>

Latch wait time

Does anyone know the potential causes of a high average latch wait time
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.
If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas

"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>
|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:

>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>

Latch wait time

Does anyone know the potential causes of a high average latch wait time
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas
"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:

>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>

Latch wait time

Does anyone know the potential causes of a high average latch wait time
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas
"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:
>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>

Latch Time

Hi All,

This might be a bit of a question like "how long is a peice of string" however here goes....

What is considered to be a high average latch time?

We have a SQL2000 machine which has:
700-900 ms average latch time
100% Buffer Cache Hit Ratio
No memory pages / second
Very low (almost always below 5%) cpu usage
Next to no disk transfers / sec
0 average lock time

I am surprised that the latch time is so high. Any ideas, whether I should be looking at something in particular?

Cheers in advance.
TroyAnyone - please?|||It really is kind of a hard question to answer. :) A high latch time generally points to a memory shortage and has a direct correlation to a low buffer cache hit ratio. Since your buffer cach hit ratio is 100%, you obviously don't have a problem with that.

The other thing it can point to is needing more I/O width on your disk subsystem. That's pretty doubtful again with that high of a buffer cach hit ratio. You might want to look at your physical read and write cache to see what those counters are at.

Really though, it doesn't seem to me you have any problems and I wouldn't worry about the 700-900ms average latch time. If you see that number rising dramatically when a load gets put on the system, you need to come back and add to this post so we can look at it. Otherwise, I wouldn't worry about it, especially if your system seems to be running fine and getting the desired response times from the database server.

Hope this helped.|||Thanks for the response!! Yeah that is pretty much what I had thought - but was after some confirmation from others.
Had a feeling it was slow disk/issues with I/O, so will keep an eye on it and if it causes any more concern, I will post back.

Thanks again!
Troy

lastwaittype = MISCELLANEOUS

Hi,
What does it really mean when lastwaittype = MISCELLANEOUS
for all the processes in sysprocesses and when waittime is
0 all the time for blocked spids?
Many thanks,
Oskar
Catch all wait types. From this source:
http://sqldev.net/misc/WaitTypes.htm
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:462d01c47333$c581f010$a501280a@.phx.gbl...
> Hi,
> What does it really mean when lastwaittype = MISCELLANEOUS
> for all the processes in sysprocesses and when waittime is
> 0 all the time for blocked spids?
> --
> Many thanks,
> Oskar
>
>
|||Hi,
OK, let me rephrase that: what does "catch all wait types"
actually mean? The explanation given there seems very
cryptic to me. And what about ubiquitous 0 values for
waittime?
Sincerely,
Oskar

>--Original Message--
>Catch all wait types. From this source:
>http://sqldev.net/misc/WaitTypes.htm
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:462d01c47333$c581f010$a501280a@.phx.gbl...
MISCELLANEOUS[vbcol=seagreen]
is
>
>.
>
|||Catch all generally means it did not fit into any of the other defined
categories, and did not deserve to have a special category created for it...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:462d01c47333$c581f010$a501280a@.phx.gbl...
> Hi,
> What does it really mean when lastwaittype = MISCELLANEOUS
> for all the processes in sysprocesses and when waittime is
> 0 all the time for blocked spids?
> --
> Many thanks,
> Oskar
>
>
|||0 means waittime < 1 ms or not waiting.
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:4a0601c47396$585c0210$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> OK, let me rephrase that: what does "catch all wait types"
> actually mean? The explanation given there seems very
> cryptic to me. And what about ubiquitous 0 values for
> waittime?
> --
> Sincerely,
> Oskar
>
> --
> --
> message
> MISCELLANEOUS
> is
|||Hi,
It is a bug, actually:
http://support.microsoft.com/?id=812915
Many thanks,
Oskar

>--Original Message--
>0 means waittime < 1 ms or not waiting.
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:4a0601c47396$585c0210$a301280a@.phx.gbl...
types"[vbcol=seagreen]
--[vbcol=seagreen]
--[vbcol=seagreen]
waittime
>
>.
>

lastwaittype = MISCELLANEOUS

Hi,
What does it really mean when lastwaittype = MISCELLANEOUS
for all the processes in sysprocesses and when waittime is
0 all the time for blocked spids?
Many thanks,
OskarCatch all wait types. From this source:
http://sqldev.net/misc/WaitTypes.htm
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:462d01c47333$c581f010$a501280a@.phx.gbl...
> Hi,
> What does it really mean when lastwaittype = MISCELLANEOUS
> for all the processes in sysprocesses and when waittime is
> 0 all the time for blocked spids?
> --
> Many thanks,
> Oskar
>
>|||Hi,
OK, let me rephrase that: what does "catch all wait types"
actually mean? The explanation given there seems very
cryptic to me. And what about ubiquitous 0 values for
waittime?
Sincerely,
Oskar

>--Original Message--
>Catch all wait types. From this source:
>http://sqldev.net/misc/WaitTypes.htm
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:462d01c47333$c581f010$a501280a@.phx.gbl...
MISCELLANEOUS[vbcol=seagreen]
is[vbcol=seagreen]
>
>.
>|||Catch all generally means it did not fit into any of the other defined
categories, and did not deserve to have a special category created for it...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:462d01c47333$c581f010$a501280a@.phx.gbl...
> Hi,
> What does it really mean when lastwaittype = MISCELLANEOUS
> for all the processes in sysprocesses and when waittime is
> 0 all the time for blocked spids?
> --
> Many thanks,
> Oskar
>
>|||0 means waittime < 1 ms or not waiting.
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:4a0601c47396$585c0210$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> OK, let me rephrase that: what does "catch all wait types"
> actually mean? The explanation given there seems very
> cryptic to me. And what about ubiquitous 0 values for
> waittime?
> --
> Sincerely,
> Oskar
>
>
> --
> --
> message
> MISCELLANEOUS
> is

lastwaittype = MISCELLANEOUS

Hi,
What does it really mean when lastwaittype = MISCELLANEOUS
for all the processes in sysprocesses and when waittime is
0 all the time for blocked spids?
--
Many thanks,
OskarCatch all wait types. From this source:
http://sqldev.net/misc/WaitTypes.htm
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:462d01c47333$c581f010$a501280a@.phx.gbl...
> Hi,
> What does it really mean when lastwaittype = MISCELLANEOUS
> for all the processes in sysprocesses and when waittime is
> 0 all the time for blocked spids?
> --
> Many thanks,
> Oskar
>
>|||Hi,
OK, let me rephrase that: what does "catch all wait types"
actually mean? The explanation given there seems very
cryptic to me. And what about ubiquitous 0 values for
waittime?
--
Sincerely,
Oskar
>--Original Message--
>Catch all wait types. From this source:
>http://sqldev.net/misc/WaitTypes.htm
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:462d01c47333$c581f010$a501280a@.phx.gbl...
>> Hi,
>> What does it really mean when lastwaittype =MISCELLANEOUS
>> for all the processes in sysprocesses and when waittime
is
>> 0 all the time for blocked spids?
>> --
>> Many thanks,
>> Oskar
>>
>
>.
>|||Catch all generally means it did not fit into any of the other defined
categories, and did not deserve to have a special category created for it...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:462d01c47333$c581f010$a501280a@.phx.gbl...
> Hi,
> What does it really mean when lastwaittype = MISCELLANEOUS
> for all the processes in sysprocesses and when waittime is
> 0 all the time for blocked spids?
> --
> Many thanks,
> Oskar
>
>|||0 means waittime < 1 ms or not waiting.
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:4a0601c47396$585c0210$a301280a@.phx.gbl...
> Hi,
> OK, let me rephrase that: what does "catch all wait types"
> actually mean? The explanation given there seems very
> cryptic to me. And what about ubiquitous 0 values for
> waittime?
> --
> Sincerely,
> Oskar
>
> >--Original Message--
> >Catch all wait types. From this source:
> >
> >http://sqldev.net/misc/WaitTypes.htm
> >
> >
> >--
> >
> >----
> --
> >----
> --
> >--
> >
> >Need SQL Server Examples check out my website at
> >http://www.geocities.com/sqlserverexamples
> >"Oskar" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:462d01c47333$c581f010$a501280a@.phx.gbl...
> >> Hi,
> >>
> >> What does it really mean when lastwaittype => MISCELLANEOUS
> >> for all the processes in sysprocesses and when waittime
> is
> >> 0 all the time for blocked spids?
> >>
> >> --
> >> Many thanks,
> >> Oskar
> >>
> >>
> >>
> >
> >
> >.
> >|||Hi,
It is a bug, actually:
http://support.microsoft.com/?id=812915
--
Many thanks,
Oskar
>--Original Message--
>0 means waittime < 1 ms or not waiting.
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4a0601c47396$585c0210$a301280a@.phx.gbl...
>> Hi,
>> OK, let me rephrase that: what does "catch all wait
types"
>> actually mean? The explanation given there seems very
>> cryptic to me. And what about ubiquitous 0 values for
>> waittime?
>> --
>> Sincerely,
>> Oskar
>>
>> >--Original Message--
>> >Catch all wait types. From this source:
>> >
>> >http://sqldev.net/misc/WaitTypes.htm
>> >
>> >
>> >--
>> >
>> >----
--
>> --
>> >----
--
>> --
>> >--
>> >
>> >Need SQL Server Examples check out my website at
>> >http://www.geocities.com/sqlserverexamples
>> >"Oskar" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:462d01c47333$c581f010$a501280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> What does it really mean when lastwaittype =>> MISCELLANEOUS
>> >> for all the processes in sysprocesses and when
waittime
>> is
>> >> 0 all the time for blocked spids?
>> >>
>> >> --
>> >> Many thanks,
>> >> Oskar
>> >>
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>sql

LastNonempty return null when filter time hierarchy

Hi there,

I am using SSAS 2005 SP2. Now I have a big problem. My scenario is that:

I have a measure, inventory and set its aggregateFunction to "LastNonempty".

I have a Time dimension and create a hierarchy for it. The hierarchy contains two levels, namely Year and Month.

Everything goes fine when I display the report using BIStudio. However, if I filter the Time hierarchy and set it only to display two months like 2007, 04 and 2007, 05, the measure become null. If I clear the filter and show all 2007's months the measure displays correctly.

Does anything also have that problem?

BTW, I also tried this scenario in MS example database "AdventureWorksDW". Set one measure to be lastnonempty, and create a time hierarchy. Then, set filter to show only two months. The null problem shows again. Hopefully someone can help me to solve this problem.

Thanks in advance.

Best regardly,

Tom

If you browse the [Measures].[End of Day Rate] value in Adventure Works (which has aggregate function to "LastNonEmpty"), selecting multiple months in the filter (for example, May and June 2004) seems to work fine - no Null's appear. Can you reproduce your problem with this standard measure?|||

Hello Puri,

Let me describe the scenario I created from AdventureWorkDW.

I have four dimension tables: DimGeography, DimCustomer, DimTime, DimProduct.

And only one fact table: FactInternetSales.

After the cube was created, I set the aggregatefunction of the measure "Freight" to "LastNonempty".

Besides, I create a time hierarchy in the dimension "Due Date". The hierarchy has two levels, namely "Calender Year" and "English Month Name".

Then I deploy the created cube. I brow it via BIStudio. I drag and drop freight to the measure part and time hierarchy to the upper dimension part. Everything works fine. It can show data of 2001 to 2004 and I can drilldown to show months belong to specific year.

Howerver, if I set the filter to the time hierarchy and only show data of "April and August" in 2003, then I can't drilldown to the month level.

It will show null if I click the "+" sign next to the "2003".

Best Regardly

Tom

|||

Hi Tom,

Without trying to reconstruct the cube you describe, one issue with your time hierarchy may be the use of "English Month Name" at the lower level - since the same month occurs under multiple years, this is not a natural hierarchy. You can refer to the "Month Name" attribute in the Date dimension of Adventure Works, to see how that is defined - note that "Calendar" is a natural hierarchy in the Date dimension.

Here, you might be better off first studying something which works, like the sample Adventure Works cube; rather than first building something which doesn't, then trying to figure out why...

|||

Dear Deepak,

I tried to use Adventure Works Sample Cube. In this cube, I create a new Time hierarchy which contains two levels, Calendar year and Month Name. Besides, I set the aggregateFunction of "Internet Order Quantity" from Internet Sales group to "LastNonEmpty". Then I deploy it and browse it in the BIStudio. I drag and drop the time hierarchy I created and Internet Order Quantity to the measure part. Everthing works fines. Howerver, if I set the filter to only shows "May, 2003 and June, 2003" the null problem still can't solve. At the Year level, it will show "CY 2003" and the value of Internet Order Quantity is "10". If I click the "+" sign, it shows nothing in the month level. Could you help me with this problem?

Best Regardly

Tom

LastNonEmpty + Time PrevMember don't aggregate correctly

I'm using the Account Intelligence and when I have a balance account modified with a mdx script in cube calculation using the Time prevmember it don't aggregates correctly. I think that it's a solver order problem.

I have a account dimension like that:

Balance (balance account)

Asset (balance account)

Computers (balance account)

Result (flow account)

Computers Investments (flow account)

In the cube calculation I have these formula:

Computers = (Computers, Time.Currentmember.Prevmember) + Computer Investments;

When I query these data I receive these:

2007 Jan Fev Mar

Balance 0 100 50 0

Asset 0 100 50 0

Computers 150 100 150 150

Result 150 100 50 0

Computers Investments 150 100 50 0

It's not aggregatin the (Computers, Time.Currentmember.Prevmember) in account hierarchy, any Idea why is that?

I'm using SQL Server 2005 Enterprise SP1.

I was using the follow Time Hierarchy:

Year

lastnonempty

Hello,

How can i implement lastnonempty for a dimension that is not a time dimension?

Example: I have a Client, Type and Time dimension and a facttable FactClient; and i want to apply the lastnonempty measure to the Client dimension not to the Time dimension. How can i do that?

If you need more information, please ask.

Thank you,

Vulcano

Sorry, i forgot the example:

The fact table:

Client Type Date Value

John Type1 1 Aug 2006 5

John Type2 5 Aug 2006 3

John Type2 25 Aug 2006 7

Mary Type1 3 Aug 2006 1

Mary Type2 7 Aug 2006 9

Mary Type1 15 Aug 2006 15

Eric Type1 20 Aug 2006 3

I want to get at the end of the month:

Month Aug 2006 John Type2 10

Mary Type1 15

Eric Type 1 3

Thank you,

Vulcano

|||

Given that the last entry for John is:

John Type2 25 Aug 2006 7

why is the month-end total for John 10, rather than 7?

Month Aug 2006 John Type2 10|||

Thanks, for your reply Deepak

The entry for John at the end of month is 10, because the last 2 entries for John have TYPE2 (3+7=10).

I need to take the last entries for a client that have the same TYPE.

Thank you

sql

last used stored proc

Is there a way to find out when the last time a stored proc was used? Or if
it has ever been used?David,
I'm not aware of any meta data field that tracks the last use of a stored
procedure. You can determine the creation date by using sysobjects table.
Profiler can be used to track the use of individual stored procedure. A
less impact option for infrequently used procs would be to insert a DEFAULT
VALUES record into a custom audit table i.e., SUSER_SNAME(), GETDATE(),
et.al. as part of the proc execution. In addition there are several
third-party auditing software packages available.
HTH
Jerry
"DAVID S" <DAVIDS@.discussions.microsoft.com> wrote in message
news:A748264D-FF0D-43F2-94B8-F15ABFD5AE0F@.microsoft.com...
> Is there a way to find out when the last time a stored proc was used? Or
> if
> it has ever been used?|||Hi,
No, By itself sql server will not store these information. You can set a
filter in profiler and redirect the log to a file.
Later you can open the file and look into the times SP is executed.
Thanks
Hari
SQL Server MVP
"DAVID S" <DAVIDS@.discussions.microsoft.com> wrote in message
news:A748264D-FF0D-43F2-94B8-F15ABFD5AE0F@.microsoft.com...
> Is there a way to find out when the last time a stored proc was used? Or
> if
> it has ever been used?

last used stored proc

Is there a way to find out when the last time a stored proc was used? Or if
it has ever been used?David,
I'm not aware of any meta data field that tracks the last use of a stored
procedure. You can determine the creation date by using sysobjects table.
Profiler can be used to track the use of individual stored procedure. A
less impact option for infrequently used procs would be to insert a DEFAULT
VALUES record into a custom audit table i.e., SUSER_SNAME(), GETDATE(),
et.al. as part of the proc execution. In addition there are several
third-party auditing software packages available.
HTH
Jerry
"DAVID S" <DAVIDS@.discussions.microsoft.com> wrote in message
news:A748264D-FF0D-43F2-94B8-F15ABFD5AE0F@.microsoft.com...
> Is there a way to find out when the last time a stored proc was used? Or
> if
> it has ever been used?|||Hi,
No, By itself sql server will not store these information. You can set a
filter in profiler and redirect the log to a file.
Later you can open the file and look into the times SP is executed.
Thanks
Hari
SQL Server MVP
"DAVID S" <DAVIDS@.discussions.microsoft.com> wrote in message
news:A748264D-FF0D-43F2-94B8-F15ABFD5AE0F@.microsoft.com...
> Is there a way to find out when the last time a stored proc was used? Or
> if
> it has ever been used?

last used stored proc

Is there a way to find out when the last time a stored proc was used? Or if
it has ever been used?
David,
I'm not aware of any meta data field that tracks the last use of a stored
procedure. You can determine the creation date by using sysobjects table.
Profiler can be used to track the use of individual stored procedure. A
less impact option for infrequently used procs would be to insert a DEFAULT
VALUES record into a custom audit table i.e., SUSER_SNAME(), GETDATE(),
et.al. as part of the proc execution. In addition there are several
third-party auditing software packages available.
HTH
Jerry
"DAVID S" <DAVIDS@.discussions.microsoft.com> wrote in message
news:A748264D-FF0D-43F2-94B8-F15ABFD5AE0F@.microsoft.com...
> Is there a way to find out when the last time a stored proc was used? Or
> if
> it has ever been used?
|||Hi,
No, By itself sql server will not store these information. You can set a
filter in profiler and redirect the log to a file.
Later you can open the file and look into the times SP is executed.
Thanks
Hari
SQL Server MVP
"DAVID S" <DAVIDS@.discussions.microsoft.com> wrote in message
news:A748264D-FF0D-43F2-94B8-F15ABFD5AE0F@.microsoft.com...
> Is there a way to find out when the last time a stored proc was used? Or
> if
> it has ever been used?

Last Update date/time

Hello.
Does SQL Server 2000 store somewhere the date/time that a table was last
modified? How about when the data in the table was last updated?
How can I view this information if so?
Thanks in advance,
Mike> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||You could use a TIMESTAMP attribute, but this has been turned into a table
modification version number in stead of the old DATE/TIME value once
supplied. It can tell you if a row has been modified before or after other
rows though.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWRWXgtNFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.sql