none
Connect to OLEDB database connection RRS feed

  • Question

  • I am trying to pull data from a PivotTable in Excel but I would imagine the best way to do this would be to access the data source directly. 

    In Excel, it appears that the connection string is:

    Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=XXXX XXX XXXX Reporting;Data Source=ustcaXXX\nabi;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

    I checked this with the .odc file and it appears to be the same connection information:

    <odc:Connection odc:Type="OLEDB">
       <odc:ConnectionString>Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=XXXX XXX XXXX Reporting;Data Source=ustcaXXX\nabi;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error</odc:ConnectionString>
       <odc:CommandType>Cube</odc:CommandType>
       <odc:CommandText>XIMS XXX</odc:CommandText>
       <odc:AlwaysUseConnectionFile/>
      </odc:Connection>

    Anyway, when I use the following code I get an error (after about 5 seconds of delay): "The following system error occurred:  No such host is known."

                OleDbConnection conn = new OleDbConnection("Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=XXXX XXX XXXX Reporting;Data Source=ustcaXXX\nabi;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error");
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }

    One thing I noticed is that it is using Windows Authentication when accessed in Excel, which leads me to believe I may have to specify that this is a "Trusted Connection".  The error message puzzles me though.

    Friday, May 2, 2014 3:09 PM

Answers

  • I am going to say something that you may have already noticed, but you have failed to escape the "\" in the connection string. The text "ustcaXXX\nabi" would be interpreted as the name of a server that contains a newline in between "ustcaXXX" and "abi", instead of the instance "nabi" in the server "ustcaXXX".

    Either add an @ before the opening double quotes, or duplicate the "\":

    OleDbConnection conn = new OleDbConnection(@"Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=XXXX XXX XXXX Reporting;Data Source=ustcaXXX\nabi;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error");

    Never mind the "trusted connection". This is what "Integrated Security=SSPI" means.

    Saturday, May 3, 2014 8:45 PM