SQL Server Performance

error in insert command into data mining model(nested table) help plz

Discussion in 'ALL SQL SERVER QUESTIONS' started by ehsan akbar, Mar 31, 2012.

  1. ehsan akbar New Member

    I have a data base (testmining) with 2 table in my engine service,
    customer and purchase with this structure:


    CREATE TABLE [dbo].[Customer](
    [Customer_id] [int] NOT NULL,
    [Last_name] [nvarchar](50) NULL,
    [First_name] [nvarchar](50) NULL,
    [Gender] [text] NULL,
    [Age] [int] NULL,
    [Income] [int] NULL,
    [Profession] [nvarchar](50) NULL,
    [HasChildren] [bit] NULL,
    [houseOwner] [nvarchar](50) NULL,
    [address] [nvarchar](50) NULL,
    [member_cart] [text] NULL,
    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
    (
    [Customer_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]




    purchase:CREATE TABLE [dbo].[Purchase](
    [Customer_id] [int] NULL,
    [Product_Name] [text] NULL,
    [Quantity] [int] NULL,
    [Unit_Price] [int] NULL,
    [Time] [nvarchar](50) NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Purchase] WITH CHECK ADD CONSTRAINT [FK_Purchase_Customer] FOREIGN KEY([Customer_id])
    REFERENCES [dbo].[Customer] ([Customer_id])
    GO

    ALTER TABLE [dbo].[Purchase] CHECK CONSTRAINT [FK_Purchase_Customer]
    GO


    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.

    thanks a lot
  2. Shehap MVP, MCTS, MCITP SQL Server

    Welcome to Forums,

    I sounds like the other Destination DB Server need to grant access of Ad-hoc distributed queries to it as below

    sp_configure'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure'Ad Hoc Distributed Queries', 1;

    GO

    RECONFIGURE

    Please work out it and please let me know your feedback

    GO
  3. ehsan akbar New Member

    thanks. i configured these option before .but it didn't work
  4. Shehap MVP, MCTS, MCITP SQL Server

    Therefore, I do prefer much to use SQL Login instead of SSPI authentication used currently and assure syntax is written well for select statements by valdiating them at the destination first to elaborate more any syntax errors exist there

Share This Page