Error while trying to get predictions from relational DB

I am trying to get predictions and insert them into a DB table.

Following is the code I am using , but I am getting an error saying

'

An error occurred while preparing the query

,

Pl. help.

begin

declare @.v_query varchar(5000);
declare @.full_query varchar(5000);
declare @.v_dbquery varchar (200);

set @.v_dbquery = char(39)+'SELECT
[ProspectAlternateKey],
[FirstName],
[LastName],
[MaritalStatus],
[Gender],
[YearlyIncome],
[TotalChildren],
[NumberChildrenAtHome],
[HouseOwnerFlag],
[NumberCarsOwned]
FROM
[dbo].[ProspectiveBuyer]'+char(39);

set @.v_query = 'SELECT
[TM_Cluster].[Bike Buyer],
t.[ProspectAlternateKey],
PredictProbability([TM_Cluster].[Bike Buyer])
From
[TM_Cluster]
PREDICTION JOIN
OPENQUERY([Adventure Works DW],@.v_dbquery) AS t
ON
[TM_Cluster].[Marital Status] = t.[MaritalStatus] AND
[TM_Cluster].[Gender] = t.[Gender] AND
[TM_Cluster].[Yearly Income] = t.[YearlyIncome] AND
[TM_Cluster].[Total Children] = t.[TotalChildren] AND
[TM_Cluster].[Number Children At Home] = t.[NumberChildrenAtHome] AND
[TM_Cluster].[House Owner Flag] = t.[HouseOwnerFlag] AND
[TM_Cluster].[Number Cars Owned] = t.[NumberCarsOwned]'
-- print @.v_query

set @.full_query = 'select * from openquery (DMserver,'+char(39)+ @.v_query +char(39)+')' ;

print @.full_query;

EXEC (@.full_query);

end

Are you able to execute other DMX/MDX queries against the linked server to AS?

If not, you may need to make sure the MSOLAP provider is allowed in-proc on your SQL Server 2005 database engine instance. You can do this via SQL Server Management Studio - connect to the database engine, navigate to Server Objects -> Linked Servers -> Providers in the Object Explorer, right-click on MSOLAP to select Properties and check the "Allow inprocess" provider option.

|||

Yes, I was able to run DMX queries against the linked server.

For that purpose I had to check 'Allow Inprocess' as well as I had to go to linked server properties and in server option I had to set Rpc = True.

|||

You had some syntax errors that cause the query to fail (first in T-SQL and then in DMX) - see my comments marked "Raman:" in the corrected query below ( this one ran successfully on my machine):

begin

declare @.v_query varchar(5000);

declare @.full_query varchar(5000);

declare @.v_dbquery varchar (500); -- Raman: you had varchar(200) which was too small for the query

set @.v_dbquery = char(39) + -- Raman: need to escape the enclosing quotes when sending to AS as well

char(39)+'SELECT

[ProspectAlternateKey],

[FirstName],

[LastName],

[MaritalStatus],

[Gender],

[YearlyIncome],

[TotalChildren],

[NumberChildrenAtHome],

[HouseOwnerFlag],

[NumberCarsOwned]

FROM

[dbo].[ProspectiveBuyer]'+char(39)+char(39); -- Raman: need to escape the enclosing quotes when sending to AS as well

set @.v_query = 'SELECT

[TM Clustering].[Bike Buyer],

t.[ProspectAlternateKey],

PredictProbability([TM Clustering].[Bike Buyer])

From

[TM Clustering]

PREDICTION JOIN

OPENQUERY([Adventure Works DW],'

+ @.v_dbquery + -- Raman: @.v_dbquery needs to appended - you had it in the query string where it will not get substituted

')AS t

ON

[TM Clustering].[Marital Status] = t.[MaritalStatus] AND

[TM Clustering].[Gender] = t.[Gender] AND

[TM Clustering].[Yearly Income] = t.[YearlyIncome] AND

[TM Clustering].[Total Children] = t.[TotalChildren] AND

[TM Clustering].[Number Children At Home] = t.[NumberChildrenAtHome] AND

[TM Clustering].[House Owner Flag] = t.[HouseOwnerFlag] AND

[TM Clustering].[Number Cars Owned] = t.[NumberCarsOwned]'

--print @.v_query

set @.full_query = 'select * from openquery (DMserver,'+char(39)+ @.v_query +char(39)+')' ;

print @.full_query;

EXEC (@.full_query);

end