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
Error while trying to get predictions from relational DB
ChandraP
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.
Eivind Gussiås Løkseth
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 queryset
@v_dbquery = char(39) + -- Raman: need to escape the enclosing quotes when sending to AS as wellchar
(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 wellset
@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
darknessangel
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.