Re: SSAS2005 and ADOMD : update cube problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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:

> 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:BE94C870-8435-4933-AB7C-9B3BED873B8A@xxxxxxxxxxxxxxxx
> > 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
> >
>
>
>
.

Relevant Pages

  • Re: Simple Insert Into...
    ... it is in a control on the form; and is unbound (it's actually a combo ... box that is used as a dropdown list, values taken from a different table; SQL ... I need this value to assign a unique ID to the entire recordset being ... In your form's Sub, you'll wind up with something like the following: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Simple Insert Into...
    ... You can store unbound control data in a variable, ... in the sql. ... Dim strSQL As String ... In your form's Sub, you'll wind up with something like the following: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Make Table Query
    ... Sub GetExcelFiles(strAccess as string, strDir as string) ... ' strDir - name of the directory to get Excel files. ... Looking at your reply I think I can insert the SQL piece into code ...
    (microsoft.public.access.queries)
  • Re: SQL Profiler
    ... > ich verwende SQL Server 2000. ... > die mir mit SET STATISTICS ON; ... > Ich habe im SQL Profiler unter "Leistung" Execution Plan, ...
    (microsoft.public.de.sqlserver)
  • Re: ADO.NET query execution much slower than SQL Management Studio
    ... Stats being compiled on SQL Server ... If you are dynamically adding statements in a sproc, ... compare this to the profiler command (meaning what is actually run in the ... Duration: 440 ...
    (microsoft.public.dotnet.framework.adonet)