Skip to Content
0
Former Member
Oct 18, 2011 at 02:10 PM

BPC 7.5 MS Drill-Through error "cannot connect database or open result"

100 Views

Hi all, hope someone can help.

I have set up drill-through as per the BPC 7.0 MS Administration guide (I can't find a BPC 7.5 MS Administration guide!), and no matter what I try, when clicking on the "Drill-through" button I get the error "cannot connect database or open result".

I have searched here and on Google and the only suggestion I find is to ensure I'm not using trusted connections ... I'm not.

My setup:

2 Servers:

Database: Win 2008 R2 64bit, SQL 2008 R2 Enterprise 64bit (DS & AS)

Application: Win 2008 R2 64bit, SQL 2008 R2 Enterprise 64 bit (RS & IS), BPC 7.5 SP6 patch1

Client: MS Office 2007

Setup:

The Drill-down target table is a table on my database server, in my application database.

A SQL user has been created with full read-access to the table. This has been tested via SQL Studio Manager and MS Query via Excel. both work fine.

An Excel query was created, using NO filters (to make sure this wasn't the issue). and saved in ...\webfolders\applicationset\application\queryfiles\BPC_Drillthrough.dqy

The "Drillkey" property was added to the "DataSrc" (we want to limit the Drillthrough to one of our data sources) dimension, with a length of 20 and the InApp property ticked. The value "DrillAccpac" was added to our Accpac member.

The "Drill-through" table was edited via the "Web Admin Tasks" "Edit Drill-through table" task, with the following values

DrillKey: "DrillAccpac"

FileName:"BPC_Drillthrough.dqy"

MaxRows: 100000 (was 1000, but I fiddled thinking it might be the issue)

UserID and Password the same as the SQL user ID defined earlier.

Attempted:

1.) Replicated this on a dev box ... works perfectly ... the only difference is the dev box is a single server & 32bit SQL & OS (I have no control over this).

2.) Attempted using a trusted connection. ... no luck.

3.) User different connectors ("SQL Server" & "SQL Server Native Client 10.0") ... no difference

I peeked into tblLogs and found that there are 4 basic errors relating to drillthrough, as follows (I've removed a lot of extra text):

DrillThrough::ConnectDatabase

System.Runtime.InteropServices.COMException (0x800A0EA9): Provider is not specified and there is no designated default provider.

DrillThrough::GetResultSet

System.NullReferenceException: Object variable or With block variable not set.

DrillThrough::ReturnHTML

System.NullReferenceException: Object variable or With block variable not set.

DrillThrough::ReturnXMLAllData

System.NullReferenceException: Object variable or With block variable not set.