OLE DB provider "MSOLAP" linked svr "Errors in the back-end database data provide

Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.

I'm generally following theTips and Tricks article.

"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.

e.g.

SELECT * FROM OPENQUERY(DMServer,

'select … FROM Modell PREDICTION JOIN OPENQUERY…')

The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".

Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query

SELECT * FROM OPENQUERY(DMServer,

'SELECT

t.[CardTransactionID],

t.[PostingDate],

[Misuse Abuse Profile].[Even Dollar Purchase],

PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,

PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,

t.[BillingAmount]

FROM

[Misuse Abuse Profile]

PREDICTION JOIN

OPENQUERY([Athena Dev],

''SELECT

[CardTransactionID],

[PostingDate],

[BillingAmount],

[AccountNumber],

[SupplierStateProvinceCode],

[MerchantCategoryCode],

[PurchaseIDFormat],

[TransactionTime],

[TaxAmountIncludedCode],

[Tax2AmountIncludedCode],

[OrderTypeCode],

[MemoPostFlag],

[EvenDollarPurchase]

FROM

[dbo].[vMisuseAbuseProfile]

'') AS t

ON

[Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND

[Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND

[Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND

[Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND

[Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND

[Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND

[Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND

[Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND

[Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND

[Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]

')

In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".

It appears the default Analysis Services Data Source wizard selection of SQL Native Client Data Source provider cannot prepare an OPENQUERY if passed from a linked server? I changed the Datasource to Provider=SQLOLEDB.1 and the query now works.

|||this is very odd - we will look into it - thanks for reporting.|||

Hi Bill,

Could you give more details about the setup. Is the SQL server where you defined the linked server DMTest on the same machine with the Analysis Server it refers too? And also is the SQL server refered on the inner openquery datasource [Athena Dev] on a different machine than the machines with AS and SQL server with added linked server?

Thanks,

Dana Cristofor