none
Executing DMX from SQL Server (TSQL)

    Question

  • Hi,

       I am trying to execute a DMX from TSQL and getting an error. Following are the details:

    I have created a Association Rules Mining model named ShopCart deployed on my local machine.

    I have created a Linked server named Linked_AS from my SQL Server relational engine.

    I am trying to execute the following query:

    SELECT * 
    FROM OPENQUERY(LINKED_AS, 'select PredictAssociation([ShopCart].[z Ord Details MBA])as [ProductName] from [ShopCart] 
    NATURAL PREDICTION JOIN (SELECT(SELECT ''Aleve Liquid Gels'' AS [Product Name]) AS [z Ord Details MBA]) AS T;')

    I get the following error: 

    The OLE DB provider "MSOLAP" for linked server "LINKED_AS" supplied invalid metadata for column "ProductName". The data type is not supported.

    Please help me to fix this issue.

    Thanks,

    Pavan

    Tuesday, March 11, 2014 6:19 AM

Answers

  • Not working is good; it throws an error, because OPENQUERY expects a fix string, you can't use a variable here.

    The only way I see to solve this is to use dynamic SQL in this manner (untested!):

    DECLARE @sql nvarchar(2000);
    DECLARE @ProductName VARCHAR(1000) = 'Aleve Liquid Gels'
    
    SET @sql = 
    'SELECT * 
     FROM OPENQUERY(LINKED_AS, 
     ''SELECT FLATTENED 
       PredictAssociation([ShopCart].[z Ord Details MBA],
                          INCLUDE_STATISTICS)as [ProductName] 
       from [ShopCart] 
       NATURAL PREDICTION JOIN (SELECT(SELECT ''''' + @ProductName + ''''' AS [Product Name]) AS [z Ord Details MBA]) AS T;'')';
    
    
    print @sql
    
    EXEC sp_executesql @sql


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by pavan_s Tuesday, March 11, 2014 9:36 AM
    Tuesday, March 11, 2014 9:27 AM

All replies

  • Hello Pavan,

    I guess SQL Server can't handle the hierarchical format of the result; try it with FLATTENED like

    SELECT FLATTENED ... FROM ...

    to get a non-hierarchical result; see SELECT (DMX)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, March 11, 2014 8:16 AM
  • That is a brilliant suggestion, Olaf. That worked !!. Can your please help me to parameterize the above query that looks something like this

    DECLARE @ProductName VARCHAR(1000) = 'Aleve Liquid Gels'
    
    SELECT * 
    FROM OPENQUERY(LINKED_AS, 'SELECT FLATTENED PredictAssociation([ShopCart].[z Ord Details MBA],INCLUDE_STATISTICS)as [ProductName] from [ShopCart] 
    NATURAL PREDICTION JOIN (SELECT(SELECT '''+@ProductName+'''AS [Product Name]) AS [z Ord Details MBA]) AS T;')

    This code is not working. Please help me out.

    Thanks,

    Pavan

    Tuesday, March 11, 2014 9:02 AM
  • Not working is good; it throws an error, because OPENQUERY expects a fix string, you can't use a variable here.

    The only way I see to solve this is to use dynamic SQL in this manner (untested!):

    DECLARE @sql nvarchar(2000);
    DECLARE @ProductName VARCHAR(1000) = 'Aleve Liquid Gels'
    
    SET @sql = 
    'SELECT * 
     FROM OPENQUERY(LINKED_AS, 
     ''SELECT FLATTENED 
       PredictAssociation([ShopCart].[z Ord Details MBA],
                          INCLUDE_STATISTICS)as [ProductName] 
       from [ShopCart] 
       NATURAL PREDICTION JOIN (SELECT(SELECT ''''' + @ProductName + ''''' AS [Product Name]) AS [z Ord Details MBA]) AS T;'')';
    
    
    print @sql
    
    EXEC sp_executesql @sql


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by pavan_s Tuesday, March 11, 2014 9:36 AM
    Tuesday, March 11, 2014 9:27 AM
  • Thanks Olaf. That works great !!.
    Tuesday, March 11, 2014 9:37 AM