AMO: Hanging on Partition.Update

I am using AMO to manage partitions.

I am trying to create a partition then use the Update method to create the new partition.. snippet of code below:

65 //create the new partition

66 Partition newPartition = mg.Partitions.Add(partitionName, partitionName);

67 newPartition.StorageMode = StorageMode.Molap;

68 newPartition.Source = new QueryBinding(db.DataSources[0].ID, bindingQuery);

69 XmlaWarningCollection warnings = new XmlaWarningCollection();

70

71 newPartition.Update(UpdateOptions.Default,UpdateMode.Create,warnings);

72 //TODO: Deal with warnings

Every time I run it, it just hangs on the Update method. It seems to be looping.. the query execution will only stop when I restart the SSAS Service.

No errors, no log entries, no Event Log entries.... nada, zilch, vacuum, nothing......

Any ideas anyone?

BTW: This is the SP2 CTP.. I think this might be a bug...|||

It sounds like a bug, it should not hang indefinitely. It would be a good idea to post this on the connect site if it is not already there.

In the mean time, have you tried calling Update() at the cube level, not on the partition object? This is the pattern that the AmoAdventureWorks sample uses (one of the AMO samples distributed with SQL Server), they don't actually call update on the partitions or measure groups, calling update at the cube level causes the changes to all the child objects to get persisted.

|||OK.. I'll give that a whirl... I do seem to be spending an awful lot of time at the connect site lately...|||

Changed the code to the following:

65 //create the new partition

66 Partition newPartition = mg.Partitions.Add(partitionName, partitionName);

67 newPartition.StorageMode = StorageMode.Molap;

68 newPartition.Source = new QueryBinding(db.DataSources[0].ID, bindingQuery);

69 XmlaWarningCollection warnings = new XmlaWarningCollection();

70

71 cube.Update(UpdateOptions.ExpandFull,UpdateMode.CreateOrReplace);

72 //newPartition.Update(UpdateOptions.Default,UpdateMode.Create,warnings);

73 //TODO: Deal with XmlaWarningCollection warnings

But the answer is still no.. it hangs on the update cube method too.. I can see a CommandBegin in the trace with the following XMLA:

<Create AllowOverwrite="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
...

and the details for the new partition is indeed included in the XMLA...but nothing happens .. it is still hanging... no errors, no logs.. the CPU for the SSAS service is zero with the memory stationary.

When I try to look at anything to do with the SSAS database in the SQL Server Management studio, it just hangs.

This is getting serious!

|||

This is probably a silly question, but can I just confirm that you are in fact using the Enterprise edition of SQL Server? Partitions are an Enterprise only feature and if you try to deploy them to a Standard edition server you will get unpredictable results.

I don't know if you have tried this yet, but I have seen one other case where we had symptoms similar to this, simple "alter" statements were causing the server to "hang". We were lucky that this was a dev environment and deleting the database first and then fully re-deploying appeared to fix this, but I have not yet been able to identify what triggered this behaviour.

|||

This is currently the developer edition, SP2 CTP which I am using as a local dev environment. I don't yet have a proper server environment in which to run tests.

From my understanding developer edition has alll the functionality of the Enterprise edition (?)

|||

You are correct. The Developer edition has the same functionality as the Enterprise edition.

I don't know if this is possible, but could you create a partition manually and trace the xmla that is generated using profiler and then run your program and trace that and see if the xmla that is produced is different? That might help highlight something that you might need to add or change to get things working.

ambiguous?

ok, im trying to create this query

Code Snippet

select distinct j.eventid
from oppositeleptons j
where dbo.invmass(j.l1Ee,j.l2Ee,j.l1px,j.l2px,j.l1py,
j.l2py,j.l1pz,j.l2pz,91.1882)<10;

GO

and it throw me this error message

Msg 4121, Level 16, State 1, Procedure EvInvMass, Line 3
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.invmass", or the name is ambiguous.

the fact is.... theres no DBO column in oppositeleptons, so why this ambiguity?
the problem is that the function "dbo.invmass" does not exist

Check the owner of the function.. it needs to be called as
<owner>.invmass
where <owner> usually is dbo but it depends who created it and how.

Alternative to cursor

Hello,
I would like to find and alternative to using a cursor and updating my
table with the calculated values. The following code snippet is from my
store procedure.
-- Calculate and Update LEWeightedTerm and LEWeightedRR
DECLARE LECursor CURSOR FOR
SELECT TransactionID, Term, BusinessUnitID, LEAlloc, CalcODRBefore
FROM stage_DealTransaction
WHERE COBDateID = @.COBDateID AND LEWeightedTerm is null AND
LEWeightedRR is null
OPEN LECursor
DECLARE @.tranID AS int
DECLARE @.term AS int
DECLARE @.LEAlloc AS float
DECLARE @.BUID AS varchar(15)
DECLARE @.ODR AS Varchar(2)
DECLARE @.LEWT AS float
DECLARE @.LEWRR AS float
DECLARE @.LEAllocSum AS float
DECLARE @.rate AS float
FETCH NEXT FROM LECursor
INTO @.tranID, @.term, @.BUID, @.LEAlloc, @.ODR
While (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SET @.LEAllocSum = 1
IF (@.BUID IS NOT NULL)
BEGIN
SELECT @.LEAllocSum = SUM(ISNULL(LEAlloc, 0)) FROM DealTransaction
WHERE BusinessUnitID = @.BUID AND COBDateID = @.COBDateID
END
IF (@.LEAllocSum = 0)
BEGIN
SET @.LEAllocSum = 1
END
IF (@.LEAlloc IS NULL)
BEGIN
SET @.LEAlloc = 0
END
SET @.LEWT = (@.LEAlloc * ISNULL(@.term, 0)) / @.LEAllocSum
SET @.rate = CONVERT(float, ISNULL(@.ODR, '41'))
SET @.LEWRR = (@.LEAlloc * @.rate) / @.LEAllocSum
UPDATE stage_DealTransaction SET LEWeightedTerm = @.LEWT ,
LEWeightedRR = @.LEWRR WHERE TransactionID = @.tranID AND COBDateID =
@.COBDateID
END
FETCH NEXT FROM LECursor INTO @.tranID, @.term, @.BUID, @.LEAlloc, @.ODR
END
CLOSE LECursor
DEALLOCATE LECursor
I would like to find a way to optimize it so that the updates can be
done in bulk rather than one record at a time.
Any feedback would be highly appreciated.
Thank you,
Zubinezubine@.gmail.com wrote:
> Hello,
> I would like to find and alternative to using a cursor and updating my
> table with the calculated values. The following code snippet is from
> my store procedure.
>
Could you summarize what you need this to do rather than making us analyze
the cursor code?
It always help to provide DDL and sample data as well. See
www.aspfaq.com/5006 for suggestions
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On 18 Nov 2005 07:04:41 -0800, zubine@.gmail.com wrote:

>Hello,
>I would like to find and alternative to using a cursor and updating my
>table with the calculated values. The following code snippet is from my
>store procedure.
Hi Zubine,
Try the following:
UPDATE s
SET LEWeightedTerm = COALESCE (s.LEAlloc * s.Term), 0)
/ COALESCE(NULLIF(dt.SumLEAlloc, 0), 1),
LEWeightedRR = COALESCE (s.LEAlloc *
CAST(COALESCE(s.CalcODRBefore, '41') AS float)), 0)
/ COALESCE(NULLIF(dt.SumLEAlloc, 0), 1)
FROM stage_DealTransaction AS s
LEFT JOIN (SELECT BusinessUnitID,
SUM(LEAlloc) AS SumLEAlloc
FROM DealTransaction
WHERE dt.COBDateID = @.COBDateID
GROUP BY BusinessUnitID) AS dt
ON dt.BusinessUnitID = s.BusinessUnitID
WHERE s.COBDateID = @.COBDateID
AND s.LEWeightedTerm IS NULL
AND s.LEWeightedRR IS NULL
Depending on how your tables and data look, you might be able to replace
the LEFT JOIN with an INNER JOIN, and that might speed up the execution
as well.
Note that the stattement above is untested. See www.aspfaq.com/5006 if
you prefer a tested reply.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

alternative for crystal NEXT function

Hi,
Is there any alternative in SSRS 2000 for the NEXT function of Crystal. Or
can someone provide a code snippet or logic for the same.
--
Afaq ChoonawalaI am sorry, my Crystal knowledge is a little vague at this moment :-) Can
you tell us what this function does?
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Afaq" <Afaq@.discussions.microsoft.com> wrote in message
news:00748057-0DC5-4EE3-8C6D-A31EEEF1CA98@.microsoft.com...
> Hi,
> Is there any alternative in SSRS 2000 for the NEXT function of Crystal. Or
> can someone provide a code snippet or logic for the same.
> --
> Afaq Choonawala|||u can check the value any column in the next record while still being on the
current record
"Teo Lachev [MVP]" wrote:
> I am sorry, my Crystal knowledge is a little vague at this moment :-) Can
> you tell us what this function does?
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "Afaq" <Afaq@.discussions.microsoft.com> wrote in message
> news:00748057-0DC5-4EE3-8C6D-A31EEEF1CA98@.microsoft.com...
> > Hi,
> >
> > Is there any alternative in SSRS 2000 for the NEXT function of Crystal. Or
> > can someone provide a code snippet or logic for the same.
> > --
> > Afaq Choonawala
>
>|||There is no alternative for forward or random navigation. There is a
Previous function but it is an opposite of what you are trying to
accomplish. I hope a future release of RS would give us access to the
dataset before it is "bound" to the region, e.g. similar to binding ASP.NET
user controls. For the time being, consider bringing the the next record
value in the current record by transforming data at the data source.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Afaq" <Afaq@.discussions.microsoft.com> wrote in message
news:408D700E-F282-467E-8B89-85D779958F4D@.microsoft.com...
>u can check the value any column in the next record while still being on
>the
> current record
>
> "Teo Lachev [MVP]" wrote:
>> I am sorry, my Crystal knowledge is a little vague at this moment :-) Can
>> you tell us what this function does?
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "Afaq" <Afaq@.discussions.microsoft.com> wrote in message
>> news:00748057-0DC5-4EE3-8C6D-A31EEEF1CA98@.microsoft.com...
>> > Hi,
>> >
>> > Is there any alternative in SSRS 2000 for the NEXT function of Crystal.
>> > Or
>> > can someone provide a code snippet or logic for the same.
>> > --
>> > Afaq Choonawala
>>|||Not sure if you will find this useful for your situation or not, but I am in
the process of converting our reports from Crystal Reports to SQL Server
Reporting Services and I came up with the following alternative for the NEXT
function in our situation.
Many of our reports consist of multiple groups with subtotals and grand
totals in the group footers. On some of the CR reports, we have a
conditional page break which forces a page break only if the NEXT group value
is equal to the current value. For example: {@.Bank_Name/Bank_HEADER} = Next
({@.Bank_Name/Bank_HEADER})
This prevents a group footer from appearing on a single page by itself.
My SSRS solution is to place all the group footer information in the
innermost group footer, but within their own individual table rows with the
innermost group footer's Page break at End property checked. Then I define
the table row's hidden property to an expression which evaluates the row
number of the group with the row count of the group, such as
=(RowNumber("table1_Group2")<>CountRows("table1_Group2")).
My Group 1 and Report Footers row hidden properties have the following
respective expressions:
=(RowNumber("table1_Group1")<>CountRows("table1_Group1"))
=(RowNumber("table1")<>CountRows("table1"))
As for the aggregate functions in each of the table rows, I apply the same
scope values to achieve the Group 2, Group 1, and report totals as in:
=Count(Fields!Formula_Acct_Num.Value, "table1_Group2")
=Count(Fields!Formula_Acct_Num.Value, "table1_Group1")
=Count(Fields!Formula_Acct_Num.Value, "table1")
= Sum(Fields!Formula_Balance.Value, "table1_Group2")
= Sum(Fields!Formula_Balance.Value, "table1_Group1")
= Sum(Fields!Formula_Balance.Value, "table1")
Enjoy!
"Teo Lachev [MVP]" wrote:
> There is no alternative for forward or random navigation. There is a
> Previous function but it is an opposite of what you are trying to
> accomplish. I hope a future release of RS would give us access to the
> dataset before it is "bound" to the region, e.g. similar to binding ASP.NET
> user controls. For the time being, consider bringing the the next record
> value in the current record by transforming data at the data source.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "Afaq" <Afaq@.discussions.microsoft.com> wrote in message
> news:408D700E-F282-467E-8B89-85D779958F4D@.microsoft.com...
> >u can check the value any column in the next record while still being on
> >the
> > current record
> >
> >
> > "Teo Lachev [MVP]" wrote:
> >
> >> I am sorry, my Crystal knowledge is a little vague at this moment :-) Can
> >> you tell us what this function does?
> >>
> >> --
> >> HTH,
> >> ---
> >> Teo Lachev, MVP, MCSD, MCT
> >> "Microsoft Reporting Services in Action"
> >> "Applied Microsoft Analysis Services 2005"
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >> "Afaq" <Afaq@.discussions.microsoft.com> wrote in message
> >> news:00748057-0DC5-4EE3-8C6D-A31EEEF1CA98@.microsoft.com...
> >> > Hi,
> >> >
> >> > Is there any alternative in SSRS 2000 for the NEXT function of Crystal.
> >> > Or
> >> > can someone provide a code snippet or logic for the same.
> >> > --
> >> > Afaq Choonawala
> >>
> >>
> >>
>
>