OLE DB provider "MSOLAP" for linked server "LICUBE" returned message "Prepare is not safe during execution of the RegExFilter stored procedure.".

Nov 28, 2011 at 4:36 AM

Hi There,

 I have registered the ASSp dll in the safe mode with service account impersonation and i have my SQL,Analysis services running in service account.I am trying to access it from the RDBMS db like this :

with cubejobs(jid, Title, Employer, CityState, Date) as (select convert(int, convert(nvarchar(50), olap."[$Job].[Job Id]")), convert(nvarchar(200), olap."[$Title].[Title]"), convert(nvarchar(200), olap."[$Employer].[Employername]"), convert(nvarchar(50),olap."[$Job Location].[City]"), convert(nvarchar(10),olap."[$Calendar].[Date]") from openquery ( LICUBE,'DRILLTHROUGH MAXROWS 10 SELECT ( [Measures].[Canon Number Of Openings]) ON COLUMNS FROM (select ( ( {[Calendar].[Date].&[2011-09-25T00:00:00]:[Calendar].[Date].&[2011-11-23T00:00:00]}, { [Job Location].[Canon Country ID].[6]}, ASSP.RegExFilter([Location].[Location].Children, ''S:(WY)(\||$)'', [Location].[Location].CurrentMember.Name) ) ) ON COLUMNS FROM [LICUBE] )RETURN [$Job].[Job Id], [$Title].[Title],[$Employer].[Employername], [$Job Location].[City], [$Calendar].[Date]') olap) select jf.JobId, cj.Employer, cj.Title, cj.CityState [Location], cj.Date [JobDate],jf.MetaJobText [JobText] from JobsFT jf (NOLOCK) inner join cubejobs cj on cj.jid = jf.JobID ORDER by cj.DATE desc

 

But i am getting the following error:
OLE DB provider "MSOLAP" for linked server "LICUBE" returned message "Prepare is not safe during execution of the RegExFilter stored procedure.".
OLE DB provider "MSOLAP" for linked server "LICUBE" returned message "Execution of the managed stored procedure RegExFilter failed with the following error: Microsoft::AnalysisServices::AdomdServer::AdomdException.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "DRILLTHROUGH MAXROWS 10 SELECT ( [Measures].[Canon Number Of Openings]) ON COLUMNS FROM (select ( ( {[Calendar].[Date].&[2011-09-25T00:00:00]:[Calendar].[Date].&[2011-11-23T00:00:00]}, { [Job Location].[Canon Country ID].[6]},{ ASSP.RegExFilter([Location].[Location].Children, 'S:(WY)(\||$)', [Location].[Location].CurrentMember.Name)} ) ) ON COLUMNS FROM [LICUBE] )RETURN [$Job].[Job Id], [$Title].[Title],[$Employer].[Employername], [$Job Location].[City], [$Calendar].[Date]" for execution against OLE DB provider "MSOLAP" for linked server "LICUBE".

Can anyone please let me know how to fix this?

 

Nov 29, 2011 at 1:33 AM

There are actually 2 issues here. One is that it appears that the linked server is sending a prepare request before it sends the actual query. We can fix this by adding a SafeToPrepare attribute to the string filter functions and deploying a new release.

The second issue is that the SELECT part of a DRILLTHROUGH query can only return one cell and your query looks like it will return multiple cells. I fixed the SafeToPrepare in the code and tried a local test with the following query:

DRILLTHROUGH SELECT
  {[Measures].[Internet Sales Amount]} ON COLUMNS
, {ASSP.Like([Customer].[Customer].Members
   ,"Aa% A. %"
   ,[Customer].[Customer].CurrentMember.Name)} ON ROWS
FROM [Adventure Works];

And got this error:

OLE DB provider "MSOLAP" for linked server "LINKED_OLAP" returned message "Drillthrough failed because the SELECT clause returned more than one cell.".

So even if we do deploy the fix, I still do not think your queries will work.