April 1, 2012 at 8:26 am
I have a data base (testmining) with 2 tables in my engine service,
these 2 table are connected with customer_id column as a primary key in customer and foreign key in purchase
and i have a database (mining) in my analyses service on my sql server 2008 ,
so i create a mining model in my mining database on analyses services :
Create mining model MarketBasketModel
(
CustomerId long key,
Gender text discrete,
Income long continuous,
MemberCard text discrete,
Purchases table Predict_Only (
ProductName text key,
Quantity long continuous
)
)
Using Microsoft_Decision_Trees
so when i want to execute this command :
Insert into MarketBasketModel_Structure( CustomerId, Gender, Income, MemberCard,
Purchases( ProductName, Quantity) )
OpenRowset('SQLOLEDB', 'Server=EHSAN-PC;Integrated Security=SSPI',
'Shape
{ Select Customer_id, Gender, Income, member_cart From testmining.dbo.Customer }
Append (
{ Select Product_Name, Quantity From testmining.dbo.Purchase }
Relate testmining.dbo.Customer.Customer_Id to testmining.dbo.Purchase.Customer_id ) as Purchase')
i got this error :
OLE DB error: OLE DB or ODBC error: Syntax error or access violation; 42000.
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 2, 2012 at 11:28 pm
There's a number of errors in your syntax with quotes and parentheses in the wrong place. Try changing it like this:
Insert into MarketBasketModel_Structure( CustomerId, Gender, Income, MemberCard,
Purchases( ProductName, Quantity) )
Shape{
OpenRowset('SQLOLEDB', 'Server=EHSAN-PC;Integrated Security=SSPI',
'Select Customer_id, Gender, Income, member_cart From testmining.dbo.Customer’) }
Append (
{ OpenRowset('SQLOLEDB', 'Server=EHSAN-PC;Integrated Security=SSPI',
‘Select Product_Name, Quantity From testmining.dbo.Purchase’) }
Relate testmining.dbo.Customer.Customer_Id to testmining.dbo.Purchase.Customer_id ) as Purchase)
It's hard for me to test unless I create the same mining structure, but this should be closer to getting it to execute.
LinkedIn - http://www.linkedin.com/in/carlosbossy
Blog - http://www.carlosbossy.com
Follow me - @carlosbossy
April 3, 2012 at 5:51 am
thank you.when i execute this command i got this error
Parser: The following syntax error occurred during parsing: Invalid token, Line 1, Offset 1, '.
how can i rewrite this query with openquery ?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 3, 2012 at 1:54 pm
Larry, it appears you have a minor syntax issue with the current query so I would recommend you fix that to get it to run. It's probably just a matter of an apostrophe or comma missing or in the wrong place. Changing it to an OpenQuery still means you still have the same problem since you have to get the syntax right, create a linked server, etc.
LinkedIn - http://www.linkedin.com/in/carlosbossy
Blog - http://www.carlosbossy.com
Follow me - @carlosbossy
April 3, 2012 at 2:14 pm
i tried,but i couldn't find that ,so i found another simple example in the Internet ,and in this example the openrowset is changed to openquery and the syntax is correct and now i have this error
this is my dmx:
INSERT INTO MINING STRUCTURE [Market Basket]
(
[OrderNumber],[Products] (SKIP, [Model])
)
SHAPE {
OPENQUERY([AdventureWorksDW],'SELECT OrderNumber
FROM vAssocSeqOrders ORDER BY OrderNumber')}
APPEND
(
{OPENQUERY([AdventureWorksDW],'SELECT OrderNumber, Model FROM
dbo.vAssocSeqLineItems ORDER BY OrderNumber, Model')
}
RELATE OrderNumber to OrderNumber
) AS [Products]
and the error:
Either the 'ehsan-PC\ehsan' user does not have permission to access the 'AdventureWorksDW' object, or the object does not exist.
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 3, 2012 at 3:26 pm
You need to setup a linked server for this to work.
LinkedIn - http://www.linkedin.com/in/carlosbossy
Blog - http://www.carlosbossy.com
Follow me - @carlosbossy
April 4, 2012 at 2:28 pm
finally it worked.but i found another example in msdn website
execute these steps(one by one)
1:
CREATE MINING STRUCTURE [Market Basket]
(
OrderNumber TEXT KEY,
[Products] TABLE (
[Model] TEXT KEY
)
)
.............................................
2:
ALTER MINING STRUCTURE [Market Basket]
ADD MINING MODEL [Default Association]
(
OrderNumber,
[Products] PREDICT (
[Model]
)
) Using Microsoft_Association_Rules
......................................
3:
ALTER MINING STRUCTURE [Market Basket]
ADD MINING MODEL [Modified Assocation]
(
OrderNumber,
[Products] PREDICT (
[Model]
)
)
USING Microsoft_Association_Rules (Minimum_Probability = 0.1)
......................................
4:
INSERT INTO MINING STRUCTURE [Market Basket]
(
[OrderNumber],[Products] (SKIP, [Model])
)
SHAPE {
OpenRowset('SQLOLEDB', 'Server=EHSAN-PC;Integrated Security=SSPI','SELECT OrderNumber
FROM AdventureWorksDW.dbo.vAssocSeqOrders ORDER BY OrderNumber')
}
APPEND
(
{
OpenRowset('SQLOLEDB', 'Server=EHSAN-PC;Integrated Security=SSPI','
SELECT OrderNumber, Model FROM
AdventureWorksDW.dbo.vAssocSeqLineItems ORDER BY OrderNumber, Model')
}
RELATE OrderNumber to OrderNumber
) AS [Products]
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply