error using SQLQuery in action

Topics: User Forum
Coordinator
Feb 5, 2010 at 7:06 PM
sqlconsumernet Today at 10:38 AM 

I have downloaded, compiled and registered this assembly. But when I try to use it as part of a "Action Expression" the parser complains saying the call syntax is incorrect.

Here's may exact code.

call SQLQuery.ExecuteSql(
"provider=sqlncli;server=localhost;database=AholdCubePOCv2;trusted_connection=yes"
,"SELECT * FROM dimDivStore");
GO

Errro message:

Action[DrillOut] > Component[Action expression:] : Query (1, 59) Parser: The syntax for 'call' is incorrect.

Coordinator
Feb 5, 2010 at 7:10 PM

The action expects you to specify an expression that evaluates to a string. The string should be a command that returns a rowset.

So try the following:

'call SQLQuery.ExecuteSql("provider=sqlncli;server=localhost;database=AholdCubePOCv2;trusted_connection=yes","SELECT * FROM dimDivStore")'

I surrounded the whole thing in a single quote (so the double quotes wouldn't have to be escaped).

Realize that if you run that action in Excel 2007, it will create another sheet with a QueryTable. If you edit the query behind the QueryTable, the end user will see that query. So that malicious end user could certainly change that to:

call SQLQuery.ExecuteSql("provider=sqlncli;server=localhost;database=AholdCubePOCv2;trusted_connection=yes","drop table dimDivStore")

So make sure you deploy the SQLQuery assembly to run under a user which does not have delete/update/drop/create/etc permissions.

Feb 7, 2010 at 9:26 PM

Hi furmangg,

Thanks for the quick reply, I tried your suggestion and everything worked as expected. I then tried to progress my experiment a little further by making the action a little more dynamic and adding the current drill down location to the action expression.

For example...

'call SQLQuery.ExecuteSql("provider=sqlncli;server=localhost;database=AholdCubePOCv2;trusted_connection=yes","SELECT * FROM dimDivStore WHERE WeekId' + [dimTimeScale].[Calendar Hierarchy].[Week Id].CurrentMember.Name + '")'

Which caused my action to disappear from the right-click context menu. As a developer I've learn't not expect anything to work first time round and to take more pragmatic appoach. However SSAS does not error or allow me to trace/debug the problem.

At a guess I imagine my dimension reference is returning null and concaternating the null to the string is evaluating to a null in my action expression. But this is just guest work, is there a way to develop my rowset action using a traditional debug approach?

Thanks,

sqlconsumernet

Coordinator
Feb 8, 2010 at 2:11 PM

Actions are a bit tricky to debug. The most helpful thing I've found is pulling that expression out into a query calculated measure and seeing how it does. For instance, try this query:

with member [Measures].[test] as
'call SQLQuery.ExecuteSql("provider=sqlncli;server=localhost;database=AholdCubePOCv2;trusted_connection=yes","SELECT * FROM dimDivStore WHERE WeekId' + [dimTimeScale].[Calendar Hierarchy].[Week Id].CurrentMember.Name + '")'
select [measures].[test] on 0,
[date].[Calendar].Members on 1
from YourCube

 

I suspect you should try the following instead, but hopefully the query above will give you an error message saying CurrentMember expects a hierarchy (not a level)... but hopefully you should get some message you can troubleshoot.

[dimTimeScale].[Calendar Hierarchy].CurrentMember.Name

Feb 11, 2010 at 9:06 AM

Thanks furmangg,

 

I will use your suggestion to aid my cube development.

Apr 30, 2010 at 8:19 PM

Hi, I haven't worked in SQLServer much. In our case, we have cubes built on Oracle. How can I execute SQL agains oracle database? Tx in advance

Coordinator
May 3, 2010 at 10:59 PM

@canelast - So just to clarify. Are you attempting to build an action in Analysis Services that executes a specified SQL query against Oracle using the SQLQuery() function from ASSP? If so you should just need to install the Oracle provider on your SSAS server and change the connection string to match the details required by the oracle provider. Then you would put in a query that Oracle would understand in the second parameter.

Sep 10, 2015 at 9:01 AM
Hi,

I have donloaded assembly & can use it properly as MDX. However I want to use assembly as Calculated Member on my OLAP Cube. It that possible?

Here is my code:

SQLQuery.ExecuteSql(
"provider=sqloledb;server=localhost;database=MyDatabase;trusted_connection=yes"
,"SELECT SomeFunction()")

It is working but returns NULL as result. When I executed as MDX it returns correct result.

Thanks,
Ozgur
Coordinator
Sep 10, 2015 at 10:15 AM
@OzgurE - yeah I'm not surprised that it would not work in a calculated member. It was never designed for that. It returns a .Net DataTable. Even if your SQL returns a single value it will still be returned as a table with one row and one column. A measure expects to return a scalar value (eg string, integer, decimal, etc)

You would have to compile your own function which returns a scalar value if you wanted this to work, but I would advise against it. Calling a stored proc in a measure is going to be REALLY, REALLY bad for performance. The results of .net stored procs don't get cached, they pay a penalty as the data has to be marshalled from the .net framework into native code and on top of that in this case you have to connect to SQL Server and execute a statement for every cell.

If you are looking for a "near real-time" measure you could try prototyping this, but I would suggest also looking at using ROLAP or a tabular with direct query mode.
Sep 10, 2015 at 11:03 AM
Hi,

Actually I was using previous version of SQLQuery assembly in a calculated member and it was working properly. I just wanted to upgrade to new version that contains some changes like ability to specify provider or name of the ExecuteSQLDecimal function haschanged etc. Previous version was good for me both performance & efficiency perspectives. Performance was good enough because there is not much cells (maybe 20) in mdx statement.

I cannot use ROLAP because regarding fact table contains millions of rows. It is too late to evaluate using Tabular and Rolap.

Is there any other way to execute sql statements in a calculated member?

Thanks,
Ozgur
Coordinator
Sep 10, 2015 at 8:38 PM
So we've only every released the one version of the SQLQuery sample code and it only has one function (ExecuteSQL) so I'm not sure where you got an "upgrade" from, but it was not from here as we don't have a ExecuteSQLDecimal function in our sample. If this is a custom version that you've extended yourself you could try debugging it as outlined here or you could look at adding some custom trace message (which you can view with SQL profiler) to output extra information on the progress and state of the function call to try to see where your issue is.

If you are still having trouble you'd need to post the code for this function and steps to reproduce the issue here so we can see what's going on.