launch report builder from C# app

Hi friends
how can i launcg report builder from a C# app ?
i know i can use following url but need to know how to execute that url to bring up the report builder
http://<localhost>/reportserver/reportbuilder/reportbuilder.application

Thanks for your help
finally i found how to do that

WebBrowser wb = new WebBrowser();
wb.Navigate("http://<localhost>/reportserver/reportbuilder/reportbuilder.application");

that works nicely.
one more question on this one ,does anybody know how to select report model also by default instead of user choosing after launching report builder ?|||

Please see this blog post:

http://blogs.msdn.com/bobmeyers/archive/2006/01/24/517083.aspx

Note the issue mentioned in the comments.

|||

Thank you very much Bob.

I've been searching for a solution for this one. Thanks again.

Launch exe using trigger within DTS

The following has been posted on another thread and I would like to know if anyone can tell me if and how this could be done. I'm a complete novice when it come to DTS.

TIA.

"I would investigate having the .exe be part of a "job"
under DTS Local Packages and having the trigger start
that job - not really sure if this is possible."You can start an exe via
exec master..xp_cmdshell 'xxxx.exe'

You can start a dts package in the same way by using dtsrun.exe if you really want to use dts - but that would be starting an app to run an app.

I would not advise doing this from a trigger though. Better to put the request into a table and have a scheduled task run the exe.

Note that the exe must not have any user interaction as there is no display or input stream attached|||Originally posted by nigelrivett
You can start an exe via
exec master..xp_cmdshell 'xxxx.exe'



Thanks Nigel

Do I just add this at the end of the trigger as per below ? Pretty new to this stuff !

CREATE TRIGGER tr_owner_coord_email ON dbo.ownership
after insert as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
update ownership
SET coordemail = (select con.emailaddress
from contact as con with (nolock)
where coordinator = (con.forename + ' ' + con.surname))

exec master..xp_cmdshell 'xxxx.exe'|||Originally posted by nigelrivett
I would not advise doing this from a trigger though. Better to put the request into a table and have a scheduled task run the exe.


Take Nigels advice...

Do you know what a trigger does?

If you insert 1000 rows, it will kick off 1000 times...

Is that what you want?

Also you're not referencing the virtual inserted table...

You could probably make your update a default contraint...|||Originally posted by Brett Kaiser
Take Nigels advice...

Do you know what a trigger does?

If you insert 1000 rows, it will kick off 1000 times...

Is that what you want?

Also you're not referencing the virtual inserted table...

You could probably make your update a default contraint...

If you insert 1000 rows at once - trigger will fire once, if row by row - trigger will fire for every insert.|||There is not going to be heavy demand with the trigger. It will probably only be average of 60-80 rows inserted per day.

The other way I was handling this was with a timer in Vb but max interval on that is 60 seconds so even more overhead.

I would prefer to do it as Nigel suggested but need more detailed info being a newbie. I just wanted to know if there is a way the exe can be launched after the trigger had been executed. I am open to all suggestions and as was put in the original post someone thought a DTS package could launch the exe after the trigger. That way it would only be run 60-80 times per day rather than over 500 times pd using vb timer.|||Originally posted by snail
If you insert 1000 rows at once - trigger will fire once, if row by row - trigger will fire for every insert.

So True....my bad...|||PMJI, be careful withn the trigger.

While the trigger runs, the transaction is not yet commited. The database keeps exclusive locks on the row or page you are about to insert into. If you have a multi-statement transaction, then there are even more locks, preventing everyone else to access your records.

Now imagine you put some long-running command in the trigger. AFAIK, xp_cmdshell runs synchronously, i.e. it waits untill your EXE completes. All the while your records are locked. You may not worry about concurrency, if this is a single-user application or some background drip-feed.

But if you intend to connect from the EXE back into Sql Server, then that is a separate connection. The EXE won't see the record just being inserted by the trigger. If the EXE attempts to Select one of the locked records, you may end up waiting forever ( Exe waits for Trigger to release the locks, while Trigger waits for Exe to complete ). Sounds like fun.

As about integrity: it is nice to know you can roll back a multi-statement transaction, including the efefcts of triggers. But xp_cmdshell is outside the Rbdms, so it won't roll back at all.

I realise triggers are an attractive thought, to streamline the chain of command, and to reduce the number of moving parts. In practice, they don't perform this role too well.

Andrew Schonberger

Launch DTS with OLAP from .net application

Hi,

with reference to the following thread:

http://forums.microsoft.com/msdn/ShowPost.aspx?postid=740855&isthread=false&siteid=1&authhash=775a583435c503730bf96695fe78ffc77f04a290&ticks=632942355374528292

Which in summary is that I am trying to execute DTS packages containing OLAP tasks to process cubes from an application. It hangs and fails after an hour and some minutes.

the code :

Package2Class package = new Package2Class();

object pVarPersistStgOfHost = null;

package.LoadFromSQLServer(serverName,null,null,DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,null,null,null,packageName,ref VarPersistStgOfHost);

package.Execute();

I have launched the DTS on command prompt using DTSRUN on the computer I am trying to execute my code, and it executes marvellously.

DTSRUN can also execute a DTS on another physical server Alpha for instance which process a cube of AS2000 found on another physical server Beta for instance.

Since I need to check that each steps have been executed properly and that running DTSRUN from the application is not a possibility to do so, I need a way to be able to launch the DTS and get to know which steps is failing. the above code is supposingly a way but its failing on LoadFromSQLServer() method.

I am using SQL Server 2000 and AS2000 and .net 1.1 (modifying a legacy system to enable the above facility).

Any views please?

Thanks

Is there any error message?

If so, can you paste here its full text (replacing any confidential information like server names, IP addresses, user names, passwords etc.)?

|||There is no error page. The steps in the package are with status "failed". Thats it.|||

I am moving this to the "SQL Server Integration Services" forum (successor to DTS), which may be able to help troubleshooting.

|||

Failing on the LoadFromSQLServer method should be quick, and I would expect an exception.

The Execute on the other hand will not raise an exception. Use package logging and/or implement the events interface to get more detail of tasks that fail in the package.

If the LoadFromSqlServer fails, perhaps try another storage location just to see if you can isolate the problem for now.

|||

HTH.

Dim EventsSSIS As Eventos

EventsSSIS = New Eventos()

sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)

..

..

..

Public Class Eventos

Implements IDTSEvents

Sub OnExecutionStatusChanged(ByVal exec As Executable, ByVal newStatus As DTSExecStatus, ByRef fireAgain As Boolean) Implements IDTSEvents.OnExecutionStatusChanged

End Sub

Sub OnBreakpointHit(ByVal breakpointSite As IDTSBreakpointSite, ByVal breakpointTarget As BreakpointTarget) Implements IDTSEvents.OnBreakpointHit

End Sub

|||

I am using .net 1.1 and SQL server 2000. I do not have SSIS.

The solution is to use Stored procedure to launch it, this relieving this responsibility from the .net application.

there is a call from the stored proc called the LoadFromSQLServer that can be used for this purpose.

Thanks

latest order date

Hi,
I have an order table which contains the following fielde: 1). orderid (this
is the order number 2). clientid 3). orderdate.
I need to script so that I can find out those clientids which do not have
place an order for at least 90 days.
Can you tell me how to program it?Read the documentation. You want to select clientids which are NOT IN a
subquery that selects all clients that have ordered in the last 90 days. You
could also use a join where max order date is less than 90 days ago.
RR
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:B84E46A2-9291-4D17-A625-3DBADD9746B1@.microsoft.com...
> Hi,
> I have an order table which contains the following fielde: 1). orderid
(this
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>|||Something like this should give you the client and their last order date.
declare @.DaysSinceOrder as numeric
set @.DaysSinceOrder = 90
select
clientid ,
max(orderdate)
from "YourTableHere"
group by clientid
having max(orderdate) < getdate()-@.DaysSinceOrder
"qjlee" wrote:

> Hi,
> I have an order table which contains the following fielde: 1). orderid (th
is
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>|||Please post DDL, sample data and expected results
(http://www.aspfaq.com/etiquette.asp?id=5006 )
Since you have a ClientID column in your Orders table, I am guessing that
you have a Clients table somewhere. Here is a complete guess:
SELECT ClientID, ClientName
FROM Clients
WHERE ClientID NOT IN (SELECT ClientID FROM Orders WHERE DATEDIFF(d,
OrderDate, CURRENT_TIMESTAMP) <= 90)
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:B84E46A2-9291-4D17-A625-3DBADD9746B1@.microsoft.com...
> Hi,
> I have an order table which contains the following fielde: 1). orderid
> (this
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>

lastUpdatedTs and userID from Inserted table

I wrote the following trigger:

CREATE TRIGGER dbo.tru_Employee
ON Employee
AFTER update
AS

DECLARE @.lastUpdatedTs datetime
DECLARE @.table NVARCHAR(50)
DECLARE @.transID INT
DECLARE @.userID INT

SET @.table='Employee'

SELECT @.userID = lastUpdatedBy,
@.lastUpdatedTs=lastUpdatedTs
FROM inserted

INSERT INTO AUD_tracking VALUES(@.table,@.lastUpdatedTs,@.userID)
SET @.transID=@.@.IDENTITY

Every time this triger fires it always reads the same value for userID and lastUpdatedBy. It's like 'inserted' table is not even affected by my UPDATE statement while @.oldvalue and @.newvalue that I populated the other table with (Tracking details table) are just fine.

What could it be?
ThanksYour trigger fires once for each operation, even if many records were updated. Try this more common syntax:

INSERT INTO AUD_tracking (Table, LastUpdatedTs, UserID)
SELECT 'Employee', LastUpdatedTs, LastUpdatedBy
FROM Inserted

Tip: I like to add a field to my audit table where I can store the Primary Key of the affected records so that I also know what was altered. Sometimes, I even store the before and after values. It makes a thorough paper trail that has proven helpful in diagnosing problems and averting blame.

blindman|||Originally posted by blindman
Your trigger fires once for each operation, even if many records were updated. Try this more common syntax:


Thank you for your reply.

I am sorry for giving a confusing information. This was only a beginning part of the trigger. It is followed by the part for updating each and every column. Also I am creating two tables: one that I already mentioned and the other one with tracking details(oldvalue, newvalue, etc.).

As for the timestamp amd userID being the same, I just noticed that lastUpdatedTs and lastUpdatedBy doesn't change on update in the original table either. At least when I simply run update with QA.
So I guess I have to go back to this on Monday.

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

sql

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Month-to-Last-Child

I am trying to write a calculation to give a Last Month reference, I use the following MDX and it works fine for the Day level

[Time].[Month Comparison].DefaultMember,
ParallelPeriod(
[Time].[Date with Month].[Month], 1,
[Time].[Date with Month].CurrentMember
)

However for Month level, I do not want it to reference to the last month value directly. What I want is, for example, today is "7th May 2007", I want the Last Month Reference for "May 2007 " to be the sum of "1st Apr 2007" to "7th Apr 2007" instead of the sum of the whole "Apr 2007".

Does anyone have any idea on how to archive this? Thanks in advance!

Assuming that all days in a month till the last day with data should be included, something like:

Aggregate(

CrossJoin({[Time].[Month Comparison].DefaultMember},
PeriodsToDate([Time].[Date with Month].CurrentMember.Level,

ParallelPeriod([Time].[Date with Month].[Month], 1,
Tail(NonEmpty(Descendants([Time].[Date with Month].CurrentMember,
[Time].[Date with Month].[Day])).Item(0).Item(0))))))

Last Inserted row

I am trying to access the row which was just inserted into a table. Will the following statements be equivalent of each other?

STATEMENT 1:
SELECT JobNumber
from tblCustServiceHistoryHdr
where ServiceType='On-site' AND ServiceStatus = 'NEW' AND DateModified =(SELECT MAX(DateModified) from tblCustServiceHistoryHdr) /* i thought by using DateModified i would get the last modified row*/

STATEMENT 2:
SELECT JobNumber
from inserted
where ServiceType='On-site' AND ServiceStatus = 'NEW' /* i don't need the DateModified criteria here since i am extracting from the INSERTED table*/Would you like to know the last row inserted by your session, or by all sessions ?

About statement 2, remember that the pseudo-table "inserted" can contain many rows. In that case, select jobnumber from inserted is still not an exact answer to your question.

Maybe if you us a little more info about your objective, forum members might have some ideas to help you out.|||Basically there will be many ppl inserting to this table and we need to extract the specific rows that they have inserted and then update them to another table. This will be happening later on.

But for now I will only need to know the inserted row for my session.|||How about introducing a new column to your table which will hold the time stamp for new rows inserted?|||I thought the DateModified column acted like a timestamp...u know everytime there r updates to that particular row the DateModified column is altered using getdate().|||Why not put an Identity Seed field on your table ?

Then do the insert through a stored procedure and pass back the identity field value.

By the way, @.@.IDENTITY returns the last generated value for the newly inserted row.|||What do you want to know the inserted row for ?
Might help us with a solution|||Originally posted by gayamantra
Basically there will be many ppl inserting to this table and we need to extract the specific rows that they have inserted and then update them to another table. This will be happening later on.

But for now I will only need to know the inserted row for my session.
Still confused, your objective is to trigger some action for all the rows that were inserted in your table. To my point of view, your STATEMENT 2 is exactly what you need. I'm still confused with your idea of getting the LAST row inserted, as you will apparently want to do something for ALL rows inserted.

large table update

hello, i have a following problem: I have got 130 million row TABLEA
and I need to run this update:
UPDATE TABLEA
SET COLUMNA=(SELECT COLUMNB FROM TABLEB WHERE condition)

I suppose that for every row from TABLEA (80 million times) select statement must be executed, which is terribly slow. Isn't there a better way to write this update? Thank youCould you provide the WHERE clause in detail, pls?|||Originally posted by Coolberg
Could you provide the WHERE clause in detail, pls?

WHERE column from TABLEA=column FROM TABLEB

Large SQL update - effect on SQL 2005 transactional replication

I'm a newbie to Replication and recently setup the following.

Publisher and Distributor on the same SQL2005 server, then I've got 7 subscribers(SQL2000 servers) and I'm using push subscriptions. I'm replicating 5 SQl tables which don't have too many changes and these are scheduled to run every 3 hours. In a few days a large one off SQL update with add an additional 10,000 rows to one of the replicated tables. I was wondering what impact this would have on the above setup i.e are there any sort of limitations here. I'm assuming not but thought I would check. I'm thinking it will just cause additional overhead on the server, but the update is being applied when no users will be using the database.

Any feedback greatly appreciated.

Thanks

You shouldn't have any problems, just understand that if you do this in one large transaction, logreader agent won't pick up changes until the transaction has been completed/committed, and the distribution agent will not pick up the changes until after the logreader agent has completed.

Large Page Extensions

Hello
After a reboot on my SQL Server 2000 64 bits the following message appeared
on the Errorlog:
Cannot use Large Page Extensions: Failed to allocate 32MB
I searched Google and the Books On-Line but found nothing about it.
Could someone point to me some documentation about that message? Or explain
what does it means?
Thanks
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.org
On 64-bit platforms, SQL Server makes use of the 'large page' API support
provided by the operating system. You can read more about it here
http://msdn.microsoft.com/library/de...e_support.asp.
The error you're receiving is hinted at in the article, but is due to the
fact that SQL Server cannot get a contiguous region of 32 MB for the large
page region.
Hope that helps.
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Carlos Selonke" <CarlosSelonke@.discussions.microsoft.com> wrote in message
news:73B198C4-7B9B-4063-8866-97D4108B3F06@.microsoft.com...
> Hello
> After a reboot on my SQL Server 2000 64 bits the following message
> appeared
> on the Errorlog:
> Cannot use Large Page Extensions: Failed to allocate 32MB
> I searched Google and the Books On-Line but found nothing about it.
> Could someone point to me some documentation about that message? Or
> explain
> what does it means?
> Thanks
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org

Large Page Extensions

Hello
After a reboot on my SQL Server 2000 64 bits the following message appeared
on the Errorlog:
Cannot use Large Page Extensions: Failed to allocate 32MB
I searched Google and the Books On-Line but found nothing about it.
Could someone point to me some documentation about that message? Or explain
what does it means?
Thanks
--
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.orgOn 64-bit platforms, SQL Server makes use of the 'large page' API support
provided by the operating system. You can read more about it here
http://msdn.microsoft.com/library/d...ge_support.asp.
The error you're receiving is hinted at in the article, but is due to the
fact that SQL Server cannot get a contiguous region of 32 MB for the large
page region.
Hope that helps.
--
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Carlos Selonke" <CarlosSelonke@.discussions.microsoft.com> wrote in message
news:73B198C4-7B9B-4063-8866-97D4108B3F06@.microsoft.com...
> Hello
> After a reboot on my SQL Server 2000 64 bits the following message
> appeared
> on the Errorlog:
> Cannot use Large Page Extensions: Failed to allocate 32MB
> I searched Google and the Books On-Line but found nothing about it.
> Could someone point to me some documentation about that message? Or
> explain
> what does it means?
> Thanks
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org

Large Page Extensions

Hello
After a reboot on my SQL Server 2000 64 bits the following message appeared
on the Errorlog:
Cannot use Large Page Extensions: Failed to allocate 32MB
I searched Google and the Books On-Line but found nothing about it.
Could someone point to me some documentation about that message? Or explain
what does it means?
Thanks
--
Carlos Eduardo Selonke de Souza
http://carlos.geekbunker.orgOn 64-bit platforms, SQL Server makes use of the 'large page' API support
provided by the operating system. You can read more about it here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/memory/base/large_page_support.asp.
The error you're receiving is hinted at in the article, but is due to the
fact that SQL Server cannot get a contiguous region of 32 MB for the large
page region.
Hope that helps.
--
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Carlos Selonke" <CarlosSelonke@.discussions.microsoft.com> wrote in message
news:73B198C4-7B9B-4063-8866-97D4108B3F06@.microsoft.com...
> Hello
> After a reboot on my SQL Server 2000 64 bits the following message
> appeared
> on the Errorlog:
> Cannot use Large Page Extensions: Failed to allocate 32MB
> I searched Google and the Books On-Line but found nothing about it.
> Could someone point to me some documentation about that message? Or
> explain
> what does it means?
> Thanks
> --
> Carlos Eduardo Selonke de Souza
> http://carlos.geekbunker.org

Large number of rows issue

Hi ,

There is a table with the following structure

_
Date-Time of Operation | Message | details | Reason | Username | IP | MAC-Address
_

A user can fire query based on some condition on columns. The maximum no of rows that should be returned are 1 billion.

By default the data returned to the view is sorted on Date-Time. Further user can sort the data in the view on Message, details , reason , username , IP , Mac-Address.

Suppose i have a scenerio where the user first gets the 1 billion records, then some records are inserted in the database.Then user sort on IP column. Now for differnt sorting if i again fire the query new data will be obtained. Thus i will get different result.

One way is to maintain the data in my memory. But the number of records are huge,so this is not a good solution. So what should i do to get the disconnected data without actually getting everything in the memory.

Regards,
Sunil

One way would be to have a stored procedure to fire this information into another table, then have a view of that table. Hence you are in control of the "loading" of that data for reporting purposes.|||

Hi,

I had a similar case in my company.

The solution for the case was creating a second instance on a different server which keeps denormalized data partitioned according to a date field. And loading the most recent partition into memory.

Also the query should have to use the date field since it will use that criteria in the index.

The OLTP database server was using 4GB of RAM on the other hand this second instance was using 12 GB of RAM only for maintaining such reporting processes.

Perhaps you may create covering index on the table. But this will increase the size of data.

Eralper