We use AMO to process our cubes. They're partitioned daily.

Everything works great with one exception. Let me explain.

We have 30 days to process. The first 20 days process fine. During the
processing of the 21st day an error occurs. The transaction rolls everything
back. This is not what we want...we want the first 20 days that processed
fine to commit.

My question is...how can this be done in AMO?

Example code:




Public Sub CreatePartition(ByRef sProcessingType, ByRef sNewPartitionName,
ByRef sCalendarDWID, ByRef sTimeOfDayDWID)

Dim oNewPartition As Partition

Dim oNewPartitionExists As Partition

Dim oClonePartition As Partition

Dim sQueryText As String

Dim sPartitionPrefix As String

Dim BeginTime As DateTime

Dim sSQLQuery As String

Try

oClonePartition = objMeasureGroup.Partitions.FindByName("template")

BeginTime = Now

InsertCubeProcessingLog("Process Partition Executing...", 0, 0, BeginTime,
"12/31/9999", objDatabase.Name, objCube.Name, objMeasureGroup.Name,
"Partition", sNewPartitionName, sQueryText, sProcessingType, sCalendarDWID,
sTimeOfDayDWID)

oNewPartition = oClonePartition.Clone

oNewPartition.ID = sNewPartitionName

oNewPartition.Name = sNewPartitionName

objMeasureGroup.Partitions.Add(oNewPartition)

oNewPartition.Source = New QueryBinding(objDatabase.DataSources(0).ID,
sQueryText)

oNewPartition.Update()

oNewPartition.Process(ProcessType.ProcessFull)

sSQLQuery = "set EventDescription = 'Process Partition', issuccess = 1,
recordactive = 1, eventendtime = '" & Now & "' where eventdescription =
'Process Partition Executing...' and eventendtime = '12/31/9999' and
eventstarttime = '" & BeginTime & "'" & " and partitionname = '" &
sNewPartitionName & "' and cubename = '" & objCube.Name & "' and objectname
= '" & objMeasureGroup.Name & "' and databasename = '" & objDatabase.Name &
"'"

UpdateCubeProcessingLog(sSQLQuery)

Catch

Dim ErrorReplace As String = Err.Description

ErrorReplace = ErrorReplace.Replace("'", "")

sSQLQuery = "set EventDescription = 'Process Partition Failed: " &
Err.Number & " - " & ErrorReplace & "', issuccess = 0, recordactive = 0,
eventendtime = '" & Now & "' where eventdescription = 'Process Partition
Executing...' and eventendtime = '12/31/9999' and eventstarttime = '" &
BeginTime & "'" & " and partitionname = '" & sNewPartitionName & "' and
cubename = '" & objCube.Name & "' and objectname = '" & objMeasureGroup.Name
& "' and databasename = '" & objDatabase.Name & "'"

UpdateCubeProcessingLog(sSQLQuery)

strKeepProcessing = "False"

End Try

End Sub

In this MSDN paper on AS 2005 processing, The Transaction attribute of
the Batch XMLA element is discussed - this sounds like what you want to
control. In terms of AMO, I'm not sure what it maps to; but the Server
object has BeginTransaction, CommitTransaction and RollbackTransaction
methods.

http://msdn.microsoft.com/library/de.../en-us/dnsql90
/html/sql2k5_asprocarch.asp

T. K. Anand
Microsoft Corporation

June 2005

Applies to:
SQL Server 2005 Analysis Services

Summary: Processing is the operation in which the Analysis server reads
data from the relational data source and populates the cubes,
dimensions, mining models, etc. This whitepaper describes the Analysis
Services 2005 processing architecture in detail and provides guidance on
how and when to use the various processing controls.
...
The Transaction attribute is a boolean (true by default) that indicates
whether the batch is transactional or not. If Transaction=true, then all
commands in the batch are executed in a single transaction, i.e., all or
nothing. If Transaction=false, then each command is executed in a
separate transaction.
...

http://msdn2.microsoft.com/zh-cn/lib...sservices.serv
er_methods.aspx

Public Methods

Name Description

BeginTransaction Starts a transaction on the server.

CommitTransaction Commits the changes made in the current
transaction.

RollbackTransaction Rolls back the current transaction on the
connection to the server.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

The sample code you pasted does not appear to include anything that
would initiate a transaction. However there this code is obviously being
called from somewhere else, it could be the calling routine that is
causing this behaviour.

Have you tried tracing the server to see what XMLA commands are being
sent? It might give you some more hints as to what is going on.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <eooQvBDrGHA.1796 (AT) TK2MSFTNGP03 (DOT) phx.gbl>, jesperzz (AT) hotmail (DOT) com
says...

it's being called from a sql server job...


"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> wrote

I'm calling the following query from a job. My guess is that it's being
called through an explicit transaction. I'd like to call it without doing
so. Is there anyway to turn the transaction off?

All my transactions are handled within the stored procedure.



Call

AMO.ProcessMeasureGroupProd

(

"Sales Hourly",

"Sales",

"vFact_HourlyActivitySummaryCurrent",

"Data Source=SINDB04\SINDB042005;Integrated Security=SSPI;Initial
Catalog=GDW;",

"Production"

)

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

In the CreatePartition sub, assuming that the AMO server object ican be
referenced by variable objServer, could you wrap the partition
processing step in a transaction, like:

objMeasureGroup.Partitions.Add(oNewPartition)

oNewPartition.Source = New QueryBinding(objDatabase.DataSources(0).ID,
sQueryText)

oNewPartition.Update()
objServer.BeginTransaction()
oNewPartition.Process(ProcessType.ProcessFull)
objServer.CommitTransaction()
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Yes, but there must be other VB.NET code that is connecting to the AS
server and getting a reference to objMeasureGroup before calling
CreatePartition().

I was thinking along similar lines to Deepak - that you might be able to
take explicit control of the transactions. I was just wondering if there
may have been any other code higher up the call chain that may have been
doing anything with regard to transactions.

Another possibility (I have not tested this, it's just a guess) is that
you may need to call Update() at a higher level, possibly on
objMeasureGroup in order to get the new partition committed to the cube
before processing it in order to stop the process from rolling back on a
failure.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <#UamPLOrGHA.4540 (AT) TK2MSFTNGP05 (DOT) phx.gbl>, jesperzz (AT) hotmail (DOT) com
says...

Yup, there is other VB.NET code connecting to the server.




Public Function ConnectASServer(ByRef sASConn, ByRef sASDatabase, ByRef
sASCube, ByRef sASMeasureGroup, ByVal SQLConn, ByVal EnvironmentType) As
String

Dim sFailedStep As String

Dim strConnectionMade As String

sFailedStep = "Server Connection Failed"

strConn = SQLConn

strEnvironmentType = EnvironmentType

strConnectionMade = "False"

Try

'Connects to the AS Server

objServer.Connect(sASConn)

sFailedStep = "SSAS database Connection Failed"

'Connects to the AS database

objDatabase = objServer.Databases.GetByName(sASDatabase)

sFailedStep = "Cube Connection Failed"

'Connects to the Cube

objCube = objDatabase.Cubes.GetByName(sASCube)

sFailedStep = "Measure Group Connection Failed"

'Connects to the Measure Group

objMeasureGroup = objCube.MeasureGroups.GetByName(sASMeasureGroup)

strConnectionMade = "True"

Catch

Dim ErrorReplace As String = Err.Description

ErrorReplace = ErrorReplace.Replace("'", "")

InsertCubeProcessingLog("Connection Failed: " & Err.Number & " - " &
ErrorReplace, 0, 0, Now, Now, sASDatabase, sASCube, sFailedStep,
"Connection", "", "", "", "", "")

strConnectionMade = "False"

End Try



Return strConnectionMade

End Function





"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> wrote

I can't see any obvious issues, I tried to reproduce your issue on the
Adventure Works database, but I could not see anything out of the
ordinary. Deepak's suggestion of explicitly beginning and ending the
transactions would be the next thing I would try.

If you still have issues, as I mentioned earlier, it would be
interesting to trace the processing using Profiler. I would be looking
to see if there were any transactions or batches around the Process
commands.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <uCWIIU0rGHA.4888 (AT) TK2MSFTNGP05 (DOT) phx.gbl>, jesperzz (AT) hotmail (DOT) com
says...

I think I know what the problem is.

When I run the code within .NET, it works fine.

However, in production I'm running it in a SQL Server job as an Analysis
Services Query Step. At times the step itself fails (not due to code, but
network failures), and when this happens nothing commits properly. My guess
is that the step is wrapping a transaction around the actual CALL query, and
what that fails, it rolls everything back. It's just a hunch.

That brings on the question...how can I call that without a transaction, or
how canI throw the query inside a SSIS package?





"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> wrote