Solved

How to verify an ADO connection to an OLAP Server is ok?

Posted on 2007-04-09
3
947 Views
Last Modified: 2013-11-16
Hi there,

Short Story:
    Is there a way to return the system time of an OLAP server using a MDX expression?
    I am looking for the equivalent of "SELECT GETDATE()".

Long Story:
    Basically I am trying prove I have a valid and open connection to a OLAP Server that I am about to execute a MDX expression against.
    My App is dumb in the sense that I don't know the DB Schema, I am only trying to execute an expression that I will soon after pass off as a recordset.
    My code is probably going to be something like this....

    Public Function ConnectionOK(MyCon As ADODB.Connection) As Boolean
    On Error GoTo EH
    Dim lTryCount As Long
          lTryCount = 0
    Try_Again:
          lTryCount = 1 + lTryCount
        'if this next line executes without error, then assume the connection valid
          MyCon.Execute "SELECT GETDATE()" 'or what ever the MDX equivalent happens to be
          ConnectionOK = True
    LEAVE:
    Exit Function
    EH:
          If lTryCount = 1 Then
                Set MyCon = Nothing
                Set MyCon = New ADODB.Connection
                MyCon.ConnectionString = "Provider=MSOLAP;Integrated Security=SSPI;" & _
                            "trusted_connection=yes;" & _
                            "Persist Security Info=True;" & _
                            "Initial Catalog=" & m_strDBName & _
                            ";Data Source=" & m_strDataSource
                Resume Try_Again
          Else
                ConnectionOK = False
                Resume LEAVE
          End If
    End Function
0
Comment
Question by:soco180
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
jkaios earned 500 total points
ID: 18878922
You can try something like this:

Private Sub Command1_Click()
   Dim ok as boolean
   ok = OpenConn(myCon)
End Sub


Public Function OpenConn(cn As ADODB.Connection) As Boolean

   On Error GoTo Err_Handler
   
   If (cn.State = adStateOpen) Then
      OpenConn = True
      Exit Function
   End If
   
   With cn
      .Provider = "MSOLAP"
      .Properties("Data Source") = m_strDataSource
      .Properties("Initial Catalog") = m_strDBName
      .Properties("Integrated Security") = "SSPI"
      .Properties("Trusted_Connection") = "Yes"
      .Open
   End With
   
   OpenConn = (cn.State = adStateOpen)
   
   Exit Function
Err_Handler:
   OpenConn = False

End Function
0
 
LVL 2

Author Comment

by:soco180
ID: 18882499
Hi JKaios, thanks for your help...However I am trying to avoid relying on the cn.State as a rule. That is why I am trying to figure out an MDX statement that I can execute without having to know the Cube Schema.

On SQL Server I executed a simple T-SQL statement "Select GetDate()" on my connection. If that failed I would re-init my connection and try again.

Now I plan to call the ConnectionOk function each time I am about to execute an actual MDX statement for real data.


If I knew how to re-title this thread topic I would call it "Need simple MDX statement independent of Cube Schema".
0
 
LVL 2

Author Comment

by:soco180
ID: 18907244
Because it is my fault that I didn't ask the correct question and I didn't give the question a proper title, I am accepting JKAIOS answer because it is a valid answer to the actual question I posted.

I will turn around and post a new question that goes like this...

"Is there a MDX statement that can be executed without knowing Cube Schema, something like this "SELECT GETDATE()" in T-SQL? It can be any MDX statement as long as it is fast and completely independent of the Cube Schema."
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question