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

[Former Member]

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.

[JohnL · Accepted solution]

I think you need to install the 64-Bit OLEDB Provider for ODBC (MSDASQL) on the app server.

[http://www.microsoft.com/downloads/en/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en#Overview]

You also need to add the following text into the connection string:

Provider=MSDASQL;

i.e.

XLODBC
1
DRIVER=SQL Server;Provider=MSDASQL;SERVER=BPCWIN08;UID=Administrator;Tru
sted_Connection=Yes;WSID=BPCWIN08;DATABASE=Apshell_75SP3
SELECT TIME FROM TESTTABLE

TIME

[JohnL · Accepted solution]

Sorry that 64-Bit OLEDB Provider for ODBC (MSDASQL) is only for Windows 2003 servers.

You can still try adding the Provider=MSDASQL to your connection string.

Can we see your connection string?

Thanks,

John

[Former Member]

Hi John,

Please ignore the above ... your previous post was the solution!

I had mistakenly put down that my App Server is Win 2008 & it's Win 2003. The DB server is Win 2008. Shows that it sometimes helps to actually log onto the physical machine!

I applied your suggested MS download & it works ... I get records back.

I have now run into another issue ... parameters (my selections) are getting ignored ... but I'll open another question for that.

Thanks a lot.

Peter

[Former Member]

Hi John, Thanks for the reply.

No, adding the provider info didn't help. Note that both the "before" & "after" work fine in Excel.

Here's my Query:

Before:

XLODBC

1

DRIVER=SQL Server;SERVER=TTSJHBSQLRS00\TTSFINBPC;UID=BPCDrillthrough;PWD=BPCDrillthrough;APP=2007 Microsoft Office system;WSID=TTSBPCS01;DATABASE=Telesure;Trusted_Connection=No

SELECT tblUsrDrillThroughAccpacc.ACCOUNT, tblUsrDrillThroughAccpacc.CASHBACK, tblUsrDrillThroughAccpacc.CATEGORY, tblUsrDrillThroughAccpacc.CHANNEL, tblUsrDrillThroughAccpacc.BUSINESSINSURANCE, tblUsrDrillThroughAccpacc.COSTCENTRE, tblUsrDrillThroughAccpacc.DATASRC, tblUsrDrillThroughAccpacc.ENTITY, tblUsrDrillThroughAccpacc.PRODUCTS, tblUsrDrillThroughAccpacc.REGION, tblUsrDrillThroughAccpacc.RPTCURRENCY, tblUsrDrillThroughAccpacc.TIME, tblUsrDrillThroughAccpacc.VAPANDOTHERBUSINESS, tblUsrDrillThroughAccpacc.SIGNEDDATA, tblUsrDrillThroughAccpacc.ACCDESCRIPTION, tblUsrDrillThroughAccpacc.DEPTDESCRIPTION, tblUsrDrillThroughAccpacc.PERIOD, tblUsrDrillThroughAccpacc.SOURCE, tblUsrDrillThroughAccpacc.DESCRIPTION, tblUsrDrillThroughAccpacc.REFERENCE, tblUsrDrillThroughAccpacc.POSTSEQ, tblUsrDrillThroughAccpacc.BATCHENTRY FROM Telesure.dbo.tblUsrDrillThroughAccpacc tblUsrDrillThroughAccpacc

ACCOUNT CASHBACK CATEGORY CHANNEL BUSINESSINSURANCE COSTCENTRE DATASRC ENTITY PRODUCTS REGION RPTCURRENCY TIME VAPANDOTHERBUSINESS SIGNEDDATA ACCDESCRIPTION DEPTDESCRIPTION PERIOD SOURCE DESCRIPTION REFERENCE POSTSEQ BATCHENTRY

After:

XLODBC

1

DRIVER=SQL Server;SERVER=TTSJHBSQLRS00\TTSFINBPC;UID=BPCDrillthrough;PWD=BPCDrillthrough;APP=2007 Microsoft Office system;WSID=TTSBPCS01;DATABASE=Telesure;Trusted_Connection=No;Provider=MSDASQL

SELECT tblUsrDrillThroughAccpacc.ACCOUNT, tblUsrDrillThroughAccpacc.CASHBACK, tblUsrDrillThroughAccpacc.CATEGORY, tblUsrDrillThroughAccpacc.CHANNEL, tblUsrDrillThroughAccpacc.BUSINESSINSURANCE, tblUsrDrillThroughAccpacc.COSTCENTRE, tblUsrDrillThroughAccpacc.DATASRC, tblUsrDrillThroughAccpacc.ENTITY, tblUsrDrillThroughAccpacc.PRODUCTS, tblUsrDrillThroughAccpacc.REGION, tblUsrDrillThroughAccpacc.RPTCURRENCY, tblUsrDrillThroughAccpacc.TIME, tblUsrDrillThroughAccpacc.VAPANDOTHERBUSINESS, tblUsrDrillThroughAccpacc.SIGNEDDATA, tblUsrDrillThroughAccpacc.ACCDESCRIPTION, tblUsrDrillThroughAccpacc.DEPTDESCRIPTION, tblUsrDrillThroughAccpacc.PERIOD, tblUsrDrillThroughAccpacc.SOURCE, tblUsrDrillThroughAccpacc.DESCRIPTION, tblUsrDrillThroughAccpacc.REFERENCE, tblUsrDrillThroughAccpacc.POSTSEQ, tblUsrDrillThroughAccpacc.BATCHENTRY FROM Telesure.dbo.tblUsrDrillThroughAccpacc tblUsrDrillThroughAccpacc

ACCOUNT CASHBACK CATEGORY CHANNEL BUSINESSINSURANCE COSTCENTRE DATASRC ENTITY PRODUCTS REGION RPTCURRENCY TIME VAPANDOTHERBUSINESS SIGNEDDATA ACCDESCRIPTION DEPTDESCRIPTION PERIOD SOURCE DESCRIPTION REFERENCE POSTSEQ BATCHENTRY