locked
Awkward connection troubles (VBA, Cellset, Win2003 versus XP), help needed RRS feed

  • Question


  • PROBLEM
    What is needed to get a VBA application on XPsp2 to connect to AS2005 and succesfully execute an MDX ?


    DESCRIPTION
    We developed a custom front-end in MSExcel2003 that gets its data from an AS2005 cube and a SQL2005 db.  The code is written in VBA.  Development was done on a win2003 with MSSQL2005 installed.  The developed VBA-framework connects with this local instance of db and cube, but also with a similar setup on another Win2003.  It works like a charm.
    The VBA-project has (among others) these references:

    (1)
    Microsoft ActiveX Data Objects 2.8 Library
    C:\Program Files\Common Files\System\ado\msado15.dll  2.82.1830.0 (srv03_sp1_rtm.050324-1447)

    - and -

    (2)
    Microsoft ActiveX Data Objects (Multi-dimensional) 2.8 Library
    C:\Program Files\Common Files\System\ado\msadomd.dll  2.82.1830.0 (srv03_sp1_rtm.050324-1447)


    The end-users will run the application on XP sp2.  This is where the trouble begins.
    Merely "deploying" the Excel (i.e. distribute the Excel) is not enough.  The application cannot connect to the server.  We installed "Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider" (and MSXMLV6.0 that it needs).  It can now succesfully connect, but the Cellset.Open command never returns.  It leaves the application hanging. We get the same behaviour with a quick and dirty VB6 application that opens a connection and executes an MDX statement (attached below).

     

    The XP machine has the latest ADO and ADOMD objects but the versions are *not* the same as those on the Win2003 machines:

    (1)
    Microsoft ActiveX Data Objects 2.8 Library
    C:\Program Files\Common Files\System\ado\msado15.dll  2.81.1128.0 (xpsp_sp2_gdr.061226-0034)

    - and -

    (2)
    Microsoft ActiveX Data Objects (Multi-dimensional) 2.8 Library
    C:\Program Files\Common Files\System\ado\msadomd.dll  2.81.1128.0 (xpsp_sp2_gdr.061226-0034)


    What is very awkward is that there is one (1!) MDX statement that does return a result.  Change a letter and it doesn't work anymore and the VBA-code will hang.  This inspired Christopher (who works in my team) to write this post:
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1554215&SiteID=1

    The problem occurs on every XP machine we tried it on.  We tried connecting to other AS servers too: same problem there.

    Is there something extra to install that we overlooked ?
    Is there something in particular that we can investigate ?
    Deployment date appears on the horizon and this is something we definitely have to tackle soon!

     

    Thanks for any suggestions!
    Tom


    ADDENDUM: the VBA-test-code

    Code Snippet

       Dim adoCon As ADODB.Connection
        Dim adomdCS As ADOMD.Cellset
        Dim sCon As String
        Dim sMDX As String

       sCon = "Datasource=ASServer; Provider=msolap; Initial Catalog=ASDB;" 'as simple as possible
       
        Set adoCon = New ADODB.Connection
        adoCon.Open sCon   'debugger shows valid open connection
       
        Set adomdCS = New ADOMD.Cellset
        Set adomdCS.ActiveConnection = adoCon
       
        sMDX = ReadTextFile("test.mdx")  'read the mdx from file
        adomdCS.Source = sMDX
       
        '!next statement does not return!
        adomdCS.Open
       
        DumpData adomdCS   'dumps the cell values to debug window and file
       
        adomdCS.Close
        adoCon.Close

     

     

     

    Friday, May 4, 2007 12:56 PM

Answers

  •  

    Found it!

    The XP machines have a firewall installed, the Win2003 development machines not.

    Now it is just a matter of figuring out what ports need to be opened and in which direction.

    Immediate candidates are 2382 & 2383.  But I guess there is more going on because the very first "cellset.open" returns an error but next calls (with modified MDX) hang.

    Cheers,

    Tom

     

    Tuesday, May 8, 2007 2:20 PM