This project has moved and is read-only. For the latest updates, please go here.

SQLQuery problems - error: "function does not exist"

Topics: Developer Forum, Project Management Forum, User Forum
Aug 31, 2010 at 3:31 PM

Hi,

I'm using SQL2008 AS and DB, downloaded and registered the correct DLL on my SSAS instance and am able to use certain functions like GetDefaultDrillthroughMDX and GetCustomDrillthroughMDX without issue (both of them ran well in a rowset action expression).

However I really need to access the database in my rowset action drilldown (long, boring story) and need to use the SQLQuery.ExecuteSql method.  Here's my syntax for the action expression:

'call SQLQuery.ExecuteSql("provider=sqlncli;server=usbolm79\sql2008;database=humanresources_dw;trusted_connection=yes","SELECT top 1 * from bc_dim_date")'
When I drill down using Excel 2007 I get this error:  "The '[SQLQuery].[ExecuteSql]' function does not exist"

What am I doing wrong?  urgent please...need help!

  

Aug 31, 2010 at 7:28 PM

never mind on this one, I couldn't get it to work but I found another assembly that did: http://www.bidn.com/blogs/BrianKnight/ssis/67/calling-an-external-query-from-mdx



Sep 1, 2010 at 3:24 PM
Edited Sep 1, 2010 at 3:24 PM

Sorry for the slow reply. Note in the ASSP project, the SQLQuery assembly is a separate assembly. Note it's a separate download in the Downloads tab. This was done purposefully so that you could make sure to set permissions appropriately. (Be 100% sure not to run the assembly as the service account or else a smart user could drop a database easily. And make sure not to use SQL security as a smart user will be able to see the password.)

Marked as answer by furmangg on 11/10/2014 at 10:33 AM
Sep 1, 2010 at 7:10 PM

oh man, I didn't even see that...I just grabbed the top assembly download which is ASSP.  Thanks for the help, I'll check it out.

Nov 7, 2014 at 7:28 PM
I created The DLL using the following in .NET
using System;
using System.Data;
using System.Data.OleDb;
namespace ASSP
{
public class SQLQuery
{
    public static DataTable ExecuteSQL(string connectionString, string sql)
    {
        OleDbConnection conn = new OleDbConnection(connectionString);

        DataTable dt = new DataTable("Results");
        OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
        da.Fill(dt);
        return dt;
    }
}
}

Then I Imported The DLL in SSAS solution
And in SSAS Action Expression I used the following
"Call ASSP.SQLQuery.ExecuteSQL('Provider=SQLNCLI;server=LocalHost;database=SD;trusted_connection=yes','exec [dbo].StoredProcedure');"

Then I Deployed My Cube solution
And in the Analysis Server , I set the Assembly properties to Impersonation=Default and Permission Set=Unrestricted

Then I go to excel 2010 and right Click on my data Then Click on Additional Actions , I get An Error
Query (1,6) The '[ASSP].[SQLQuery].[ExecuteSQL]' Function Does not Exist.
Nov 8, 2014 at 12:16 AM
@hnnmulu - see my answer to your identical question here https://asstoredprocedures.codeplex.com/discussions/571546#post1320113
Marked as answer by furmangg on 11/10/2014 at 10:33 AM