Hi,

I'm trying to port an existing piece of VB6 code that works fine on SSAS2000
onto SSAS2005.
This code uses ADOMD to perform an MDX select statement by opening an
ADOMD.CellSet object.
It then performs an Update Cube statement on the same data and then displays
the CellSet updated value

again.

Both select and Update fonctions work fine except that the value in the
cellset is not updated. When I

close and reopen the connection, I can control that the cube has been
correctly updated.

I enclose here a piece of sample close which illustrates.

Is there any way to get this corrected (eg by setting some conenction
property) or is this a (un)kown

issue/limitation ?

Many thanks in advance
Rgds,
Francois

---------------------------------------------------------------------------------------------

' Form contains the folowing components:
' Label : Label1
' Button cmdLoad : performs the Select statement
' TextBox txtNew : use to type in the new value
' Button cmsSave : performs the Update cube

Private m_CellSet_Cube As ADOMD.Cellset
Private m_ConMdx As ADODB.Connection
Private m_ReportQuery As String


Private Sub cmdLoad_Click()

On Error GoTo gest_err

Label1.Caption = "Initializing"
DoEvents
m_ReportQuery = "..."


Set m_ConMdx = New Connection

m_ConMdx.ConnectionString = "Provider=MSOLAP;Data Source=spantik;"
m_ConMdx.Open
m_ConMdx.DefaultDatabase = "Analysis Services Project1" 'm_DatabaseName

Set m_CellSet_Cube = New ADOMD.Cellset
Set m_CellSet_Cube.ActiveConnection = m_ConMdx

Label1.Caption = "Executing query"
DoEvents

m_CellSet_Cube.Open m_ReportQuery, m_ConMdx

Label1.Caption = CStr(m_CellSet_Cube(0).Value)
DoEvents

Exit Sub

gest_err:

Label1.Caption = "Query Nok"

End Sub


Private Sub cmdUpdate_Click()

Dim Updatecommand As ADODB.Command
Dim cs As ADOMD.Cellset
Dim MdxString As String

Label1.Caption = "Performing update"

Set Updatecommand = New ADODB.Command

MdxString = "Update Cube ... USE_EQUAL_ALLOCATION"

Set Updatecommand.ActiveConnection = m_CellSet_Cube.ActiveConnection
Updatecommand.CommandText = MdxString
Updatecommand.ActiveConnection.BeginTrans
Updatecommand.Execute
Updatecommand.ActiveConnection.CommitTrans
Label1.Caption = "updated local value is :" &
CStr(m_CellSet_Cube(0).Value)

End Sub

Can you run SQL Profiler against AS and SQL Server and see if it shows any
events at the point of committing the transaction to the writeback table?

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Francois Babin" <FrancoisBabin (AT) discussions (DOT) microsoft.com> wrote

Akshai,

The Profiler on AS shows a '14 - CommitTransaction' event with following
details :
<CommitTransaction
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"/>

The Profiler on SQL shows different Insert events into the WriteBack table
(let me know if you want me to copy the details here)

The strange thing is that the update Cube action is performed correctly : if
I close the connection and start a new one, I will see the correct value. It
is only the CellSet that is not refreshed.
Is there any attribute to the OLE DB connection that can help ?

thx&rgds,
Francois


"Akshai Mirchandani [MS]" wrote: