Error using ASSP on new platform

Topics: Developer Forum, User Forum
Feb 13, 2016 at 11:52 AM
Upgraded to sql server 2014 and i followed installation instructions for ASSP on AS server

The drillthrough turns up on the actions list but when picked it throws an error in Excel (i translate from Danish):

the databasedriver lacks the necessary functions and cant be used with Excel. contact databaseadministrator or dealer of driver

i tried a trace on analysis services and there i see the drillthrough command executed and no error

i also tried creating a normal drillthrough action in the cube and that one Works without error in Excel

Any ideas ?
Coordinator
Feb 14, 2016 at 12:24 AM
The second line of your code has an extra parenthesis.

Just kidding. Please post the code you used and we will be happy to look at it.
Feb 16, 2016 at 9:12 PM
sorry for not posting code. didnt thinkabout it since it was code that has run for years on old sql server platform

i will pull it out and post

judging by the error it looks like an excel issue but i hope you can get closer when i pull out code. will tomorrow
Feb 16, 2016 at 9:21 PM
About platform its excel 2010 onsql server2014

We didnt install any additonal client drivers since cubes worked in excel with no problems

And the drillthrough actions in ASSP worked on sql server 2012
Feb 17, 2016 at 10:56 AM
https://www.dropbox.com/s/m4tb2qn69ktywa6/error.PNG?dl=0

Screenshot of error - sorry for Danish

code behind

'call ASSP.ExecuteDrillthroughAndFixColumns("'+
ASSP.GetCustomDrillthroughMDX(
"membervalue ([$Date].[Dato DK]),
NAME ([$FinanceDrillthrough].[DocumentNo]),
NAME ([$FinanceDrillthrough].[Description]),
NAME ([$Budget group].[Subcompany code]),
NAME ([$LDV Standard specification].[Standard specification]),
NAME ([$LDV Job].[LDV Job Description]),
NAME ([$LDV Delregnskab].[LDV Delregnskab]),
NAME ([$Frame].[Frame responsible]),
NAME ([$Account].[Account]),
NAME ([$Budget].[Job]),
[Finance].[Amount],
NAME ([$FinanceDrillthrough].[Currency Code]),
[Finance].[Currency amount],
NAME ([$FinanceDrillthrough].[MDS Delregnskabs Kode]),
NAME ([$Report Hierarchies].[Danida File No])",
[Accountgroup].[Disbursement Flag].&[True])+'")'
Feb 17, 2016 at 10:58 AM
Picture need to be downloaded to be visiable
Feb 18, 2016 at 8:29 AM
Do Visual Studio version have any impact ? Its sql server 2014 but only with VS 2012 ?
Coordinator
Feb 19, 2016 at 6:15 AM
Code looks good at first glance. Can you connect Profiler to SSAS and try that drillthrough action again? Please post the actual queries it runs and any errors you see.

Also is it a single server or a clustered server?
Feb 20, 2016 at 10:21 AM
ill send profiler info but it doesnt provide errors in profiler i believe-thats whyi worry its a front endissue

i believe its a single.ill follow up on that one too
Feb 22, 2016 at 6:16 PM
Hi furmangg


The profiler does show an error:

Internal error: The operation terminated unsuccessfully. Query (1, 6) Execution of the managed stored procedure ExecuteDrillthroughAndFixColumns failed with the following error: Exception has been thrown by the target of an invocation..

Query Text:
call ASSP.ExecuteDrillthroughAndFixColumns("drillthrough select ([Account].[Account].[All]
,[Account].[Group].[All]
,[Account].[Provision].[All]
,[Accountgroup].[Account].[All]
,[Accountgroup].[Accounttype].[All]
,[Accountgroup].[Annulled Commitment Flag].[All]

(...shortend due to length...)


,[Report Hierarchies].[X].[All]
,[X Filter].[Type].[All]
,[X Filter].[X Filter - Type].[All]) on 0 from [TILSKUD] return membervalue ([$Date].[Dato DK]),
NAME ([$FinanceDrillthrough].[DocumentNo]),
NAME ([$FinanceDrillthrough].[Description]),
NAME ([$Budget group].[Subcompany code]),
NAME ([$LDV Standard specification].[Standard specification]),
NAME ([$LDV Job].[LDV Job Description]),
NAME ([$LDV Delregnskab].[LDV Delregnskab]),
NAME ([$Frame].[Frame responsible]),
NAME ([$Account].[Account]),
NAME ([$Budget].[Job]),
[Finance].[Amount],
NAME ([$FinanceDrillthrough].[Currency Code]),
[Finance].[Currency amount],
NAME ([$FinanceDrillthrough].[MDS Delregnskabs Kode]),
NAME ([$Report Hierarchies].[Danida File No])")


Any thoughts?
Feb 22, 2016 at 6:20 PM
Also

The inner " drillthrough select ...."

Can be executed without error and has a non-empty resultset.
Coordinator
Feb 24, 2016 at 6:02 AM
What are the impersonation settings on the DLL. What user is it running under?

If you connect SSMS Object Explorer to SSAS and right click on the server node and choose properties and look for the server name what does it report? Then if you get on the box and open up SSMS and try to connect via the server name seen in the previous dialog does it succeed?
Feb 26, 2016 at 7:40 AM
I include screenshots

a) Impersonation = default. Is what user its running under the same or how do i check that ?

https://www.dropbox.com/s/92b8v8u3kyyj253/as%20server.PNG?dl=0

b) servername = cphu1s11 - see screenshot

https://www.dropbox.com/s/rbpik6ecopxegt4/assp.PNG?dl=0
Feb 26, 2016 at 7:57 AM
I wrote earlier that it posted error but i might have mistaken - i enclose profiler and it doesnt look like it posts errorr

https://www.dropbox.com/s/i54h2sd9t83u3e8/profiler.PNG?dl=0
Feb 26, 2016 at 8:04 AM
But i found out that in event viewer on client machine i see the error

https://www.dropbox.com/s/goz2jlifd615rfa/officealert.PNG?dl=0

Hope that Excel 2010 isnt a problem - will be big thing to replace
Coordinator
Feb 26, 2016 at 2:06 PM
Ah! You are on SQL2014 SP1 CU4. Upgrade to CU5. It fixed a bug that will impact that sproc!
Feb 26, 2016 at 2:15 PM
I hope its something like this but i actually think we are on cu6

I will follow up on that
Feb 27, 2016 at 1:02 PM
only on 4. we will upgrade and see if that fixes it.
Feb 29, 2016 at 8:13 AM
I'm a really happy sailor now. And i dont even own a boat

Tx for your input !