4

I have a database in Analysis Services on a remote server. This contains a data source for another database located on another remote server.

I am trying to write a connectivity test using C# which will check the database connection between the two databases.

I have been unable to do this using ADOMD.NET. I'm currently looking at using SMO to do this but I haven't had any luck so far.

I would greatly appreciate any advice or suggestions.

Update:
After further research, I have come up with the below test (Please note that I intend to add more try..catch blocks and Assertions later).

Also, this uses C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.AnalysisServices.DLL to access the Server, Database and DataSource classes.

class ConnectivityTests
{
    // Variables
    String serverName = "";
    String databaseName = "";
    String dataSourceName = "";

    [Test]
    public void TestDataSourceConnection()
    {
        // Creates an instance of the Server
        Server server = new Server();
        server.Connect(serverName);

        // Gets the Database from the Server
        Database database = server.Databases[databaseName];

        // Get the DataSource from the Database
        DataSource dataSource = database.DataSources.FindByName(dataSourceName);

        // Attempt to open a connection to the dataSource.  Fail test if unsuccessful
        OleDbConnection connection = new OleDbConnection(dataSource.ConnectionString);
        try
        {
            connection.Open();
        }
        catch (OleDbException e)
        {
            Assert.Fail(e.ToString());
        }
        finally
        {
            connection.Close();
        }

    }

I believe that this test is sufficient for my testing (Once I've added some more try..catch blocks and Assertions). If the test passes, it means there are no connectivity issues between my machine and both servers, which implies that there shouldn't be any connectivity issues between the servers.

However, I have been unable to work out how to test the connection between the two servers directly and I am interested if anyone knows a way of doing this.

acatt
  • 487
  • 3
  • 10
  • 1
    What seems to be the problem? Your code is very similar to what I've been using when testing some connectivity issues. At the moment I'm using AdomdConnection from Microsoft.AnalysisServices.AdomdClient but I've also used the Server class that you are using. – Antti Simonen Oct 02 '12 at 04:29
  • @AnttiSimonen The code I added works fine. It tests the connectivity between my machine & the DBServer and my machine & the data warehouse. What I was interested in is if there is a way to test the connection between the DBServer and the data warehouse. I don't think this is actually possible (without placing files on the DBServer). – acatt Oct 02 '12 at 10:24
  • @AnttiSimonen I'm glad to see that someone else is testing for connectivity issues using a similar method. Thanks! – acatt Oct 02 '12 at 10:26

1 Answers1

1

The best solution I have come across to doing this connectivity test is below: Please note that this requires the Microsoft.AnalysisServices.DLL to be added as a reference.

class ConnectivityTests
{
    // Variables
    String serverName = "";
    String databaseName = "";
    String dataSourceName = "";

    [Test]
    public void TestDataSourceConnection()
    {
        try
        {

            // Creates an instance of the Server
            Server server = new Server();
            server.Connect(serverName);

            // Gets the Database from the Server
            Database database = server.Databases[databaseName];

            // Get the DataSource from the Database
            DataSource dataSource = database.DataSources.FindByName(dataSourceName);

            // Attempt to open a connection to the dataSource.  Fail test if unsuccessful
            OleDbConnection connection = new OleDbConnection(dataSource.ConnectionString);

            connection.Open();
        }
        catch (Exception e)
        {
            Assert.Fail(e.ToString());
        }
        finally
        {
            connection.Close();
        }

     }
}
acatt
  • 487
  • 3
  • 10
  • 1
    It works for me when AS is in my local SQL instance. However, what if you want to connect to SSAS deployed in remote sql server? Here's my remote server name: `OLAPServer.Connect("serverIP:Port\\instanceName");` – Hamid Mosalla Sep 10 '17 at 09:16