ON DELETE CASCADE problem for same table column constraint

Hello,
I would like to have table with foreign key referencing to the column of the
same table. And I want to specify ON DELETE CASCADE to this column, e.g.:
create table category (
ID INTEGER IDENTITY(1,1) NOT NULL,
PARENT_ID INTEGER NULL,
NAME VARCHAR(100) NOT NULL,
CONSTRAINT CAT_PK PRIMARY KEY (ID),
CONSTRAINT CAT_FK FOREIGN KEY (PARENT_ID)
REFERENCES CAT(ID) ON DELETE CASCADE
)
But I receive error:
--
Error: java.sql.SQLException: Introducing FOREIGN KEY constraint 'CAT_FK'
on table 'category' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints., SQL State: S1000, Error Code: 1785

It means I cannot specify ON DELETE CASCADE clause.
HOW CAN I ENSURE CASCADE DELETING FOR ALL RECORDS (ALL CATEGORIES WITH BELONGED SUBCATEGORIES)?
IS THERE POSSIBILITY TO USE STORED PROCEDURE FOR THIS?
CAN YOU POST AN EXAMPLE PLEASE?

Thank you in advance
best regards,
Julian LegenyThis is a restriction in all versions of SQL Server that supports cascading constraints. You will have to implement the cascade action using triggers or in your SPs that perform the data modifications.

OLE DB Source and Isolation level

Is there a way to define Connection Manager with Read Uncommited isolation level? I do not want to specify (nolock) in all my commands and instead want to give a generic defenition at the Connection level.

Is this possible?

if i'm not mistaken, transaction isolation levels can only be set within a t-sql transaction.

OLE DB provider "MSOLAP" linked svr "Errors in the back-end database data provide

Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.

I'm generally following theTips and Tricks article.

"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.

e.g.

SELECT * FROM OPENQUERY(DMServer,

'select … FROM Modell PREDICTION JOIN OPENQUERY…')

The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".

Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query

SELECT * FROM OPENQUERY(DMServer,

'SELECT

t.[CardTransactionID],

t.[PostingDate],

[Misuse Abuse Profile].[Even Dollar Purchase],

PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,

PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,

t.[BillingAmount]

FROM

[Misuse Abuse Profile]

PREDICTION JOIN

OPENQUERY([Athena Dev],

''SELECT

[CardTransactionID],

[PostingDate],

[BillingAmount],

[AccountNumber],

[SupplierStateProvinceCode],

[MerchantCategoryCode],

[PurchaseIDFormat],

[TransactionTime],

[TaxAmountIncludedCode],

[Tax2AmountIncludedCode],

[OrderTypeCode],

[MemoPostFlag],

[EvenDollarPurchase]

FROM

[dbo].[vMisuseAbuseProfile]

'') AS t

ON

[Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND

[Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND

[Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND

[Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND

[Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND

[Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND

[Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND

[Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND

[Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND

[Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]

')

In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".

It appears the default Analysis Services Data Source wizard selection of SQL Native Client Data Source provider cannot prepare an OPENQUERY if passed from a linked server? I changed the Datasource to Provider=SQLOLEDB.1 and the query now works.

|||this is very odd - we will look into it - thanks for reporting.|||

Hi Bill,

Could you give more details about the setup. Is the SQL server where you defined the linked server DMTest on the same machine with the Analysis Server it refers too? And also is the SQL server refered on the inner openquery datasource [Athena Dev] on a different machine than the machines with AS and SQL server with added linked server?

Thanks,

Dana Cristofor

OLE DB provider "MSOLAP" linked svr "Errors in the back-end database data provide

Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.

I'm generally following theTips and Tricks article.

"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.

e.g.

SELECT * FROM OPENQUERY(DMServer,

'select … FROM Modell PREDICTION JOIN OPENQUERY…')

The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".

Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query

SELECT * FROM OPENQUERY(DMServer,

'SELECT

t.[CardTransactionID],

t.[PostingDate],

[Misuse Abuse Profile].[Even Dollar Purchase],

PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,

PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,

t.[BillingAmount]

FROM

[Misuse Abuse Profile]

PREDICTION JOIN

OPENQUERY([Athena Dev],

''SELECT

[CardTransactionID],

[PostingDate],

[BillingAmount],

[AccountNumber],

[SupplierStateProvinceCode],

[MerchantCategoryCode],

[PurchaseIDFormat],

[TransactionTime],

[TaxAmountIncludedCode],

[Tax2AmountIncludedCode],

[OrderTypeCode],

[MemoPostFlag],

[EvenDollarPurchase]

FROM

[dbo].[vMisuseAbuseProfile]

'') AS t

ON

[Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND

[Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND

[Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND

[Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND

[Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND

[Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND

[Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND

[Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND

[Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND

[Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]

')

In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".

It appears the default Analysis Services Data Source wizard selection of SQL Native Client Data Source provider cannot prepare an OPENQUERY if passed from a linked server? I changed the Datasource to Provider=SQLOLEDB.1 and the query now works.

|||this is very odd - we will look into it - thanks for reporting.|||

Hi Bill,

Could you give more details about the setup. Is the SQL server where you defined the linked server DMTest on the same machine with the Analysis Server it refers too? And also is the SQL server refered on the inner openquery datasource [Athena Dev] on a different machine than the machines with AS and SQL server with added linked server?

Thanks,

Dana Cristofor