Out-of_process error when trying to process a dimension

In my dsv I have a table called OrderDetail. This table has a salescodeid column in it. I have a second table called SalesCodeDetail. There is a relationship between these two tables based on the salescodeid. The Id is the primary key in the SalesCodeDetail table. In my cube I have a dimension of OrderDetail. When I try to add the salescode description from the salescodeDetail table as an attribute in my OrderDetail dimension I get this error.

Here is a more detailed description of this same problem.

I have a fact table that is joined to a dimension table 'Customer' by the customer number. Customer Number is the primary key in the 'Customer' view. I have a second Dimension table called 'SalesRep' which is referenced to the fact table through the customer. The join is SalesRepId, which is primary key on the SalesRep view, and SalesRepId which is on the 'Customer' view but not a key. Also, this relationship between SalesRep and customer does exist in the DSV.

When I try to process this relationship I get the following error: 'OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000.'

The SQL behind the processing is doing an 'OPENROWSET' which is causing the problem. If I take the 'OPENROWSET' out of the query when I run in Query Analyzer, the query runs fine.

So, I need to modify the way AS is creating this processing query.

|||

So, let me check my understanding of the problem. You have a fact table that references the Customer dimension. The Customer dimension holds a SalesRep key which points to the SalesRep dimension. The fact table is assigned a referenced relationship to the SalesRep dimension using the Customer dimension as the intermediate dimension in that relationship. Is that correct?

If so, do you have the SalesRep dimension flagged to be materialized?

Either way, could you post the SQL generated that is causing the error?

Thanks,
Bryan Smith

|||

Yes, you're understanding the problem correctly. Yes, the SalesRep dimension is flagged to be materialized.

Here is the SQL

SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS [dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]
FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],
OPENROWSET
(
N'SQLNCLI.1',
N'',
N'[dbo].[vw_OMSBPCustomer]'
)
AS [dbo_vw_OMSBPCustomer_2]
WHERE
(

(
[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]
)
)

Now if I take this exact SQL and run it in query analyzer I get basically the same error:

Msg 7430, Level 16, State 3, Line 1

Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.

If I remove the OPENROWSET section as below

SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice]

AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],

[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM]

AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],

[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId]

AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],

[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS

[dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]

FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],

-- OPENROWSET

-- (

-- N'SQLNCLI.1',

-- N'',

-- N'[dbo].[vw_OMSBPCustomer]'

-- )

--

[omswrite].[dbo].[vw_OMSBPCustomer] AS [dbo_vw_OMSBPCustomer_2] ****different database here

WHERE

(

(

[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]

)

)

The customer table and the fact table are in different databases, not sure if that is causing some problems, but don't think it should.

|||

Deselecting the Materialize option should eliminate the problem becase the join will not be performed. If performance drops when querying the Sales Rep data, you may want to try using the .NET provider on your data sources.

B.

|||

This worked. Thanks very much for taking the time to help!

sql

Out-of_process error when trying to process a dimension

In my dsv I have a table called OrderDetail. This table has a salescodeid column in it. I have a second table called SalesCodeDetail. There is a relationship between these two tables based on the salescodeid. The Id is the primary key in the SalesCodeDetail table. In my cube I have a dimension of OrderDetail. When I try to add the salescode description from the salescodeDetail table as an attribute in my OrderDetail dimension I get this error.

Here is a more detailed description of this same problem.

I have a fact table that is joined to a dimension table 'Customer' by the customer number. Customer Number is the primary key in the 'Customer' view. I have a second Dimension table called 'SalesRep' which is referenced to the fact table through the customer. The join is SalesRepId, which is primary key on the SalesRep view, and SalesRepId which is on the 'Customer' view but not a key. Also, this relationship between SalesRep and customer does exist in the DSV.

When I try to process this relationship I get the following error: 'OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000.'

The SQL behind the processing is doing an 'OPENROWSET' which is causing the problem. If I take the 'OPENROWSET' out of the query when I run in Query Analyzer, the query runs fine.

So, I need to modify the way AS is creating this processing query.

|||

So, let me check my understanding of the problem. You have a fact table that references the Customer dimension. The Customer dimension holds a SalesRep key which points to the SalesRep dimension. The fact table is assigned a referenced relationship to the SalesRep dimension using the Customer dimension as the intermediate dimension in that relationship. Is that correct?

If so, do you have the SalesRep dimension flagged to be materialized?

Either way, could you post the SQL generated that is causing the error?

Thanks,
Bryan Smith

|||

Yes, you're understanding the problem correctly. Yes, the SalesRep dimension is flagged to be materialized.

Here is the SQL

SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS [dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]
FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],
OPENROWSET
(
N'SQLNCLI.1',
N'',
N'[dbo].[vw_OMSBPCustomer]'
)
AS [dbo_vw_OMSBPCustomer_2]
WHERE
(

(
[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]
)
)

Now if I take this exact SQL and run it in query analyzer I get basically the same error:

Msg 7430, Level 16, State 3, Line 1

Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.

If I remove the OPENROWSET section as below

SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice]

AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],

[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM]

AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],

[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId]

AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],

[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS

[dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]

FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],

-- OPENROWSET

-- (

-- N'SQLNCLI.1',

-- N'',

-- N'[dbo].[vw_OMSBPCustomer]'

-- )

--

[omswrite].[dbo].[vw_OMSBPCustomer] AS [dbo_vw_OMSBPCustomer_2] ****different database here

WHERE

(

(

[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]

)

)

The customer table and the fact table are in different databases, not sure if that is causing some problems, but don't think it should.

|||

Deselecting the Materialize option should eliminate the problem becase the join will not be performed. If performance drops when querying the Sales Rep data, you may want to try using the .NET provider on your data sources.

B.

|||

This worked. Thanks very much for taking the time to help!

Outer Join in AS2005 cube

I have three tables for the 'Store' dimension...

Store City State

When I include the state name from the State table in the dimension and process the cube, I am missing many stores. Those stores missing don't have a state code in the city table. So, because the cube is generating an inner join between the three tables, I am missing some stores. I need to model an 'outer join' in the relationship between City and State tables. How can I do this in AS 2005?

You can create a named query (in the DataSourceView editor) to include the 3 tables with the wanted outer/inner joins (the named query is the "SELECT ... FROM ..." statement). Then build the 'Store' dimension on it.

Adrian Dumitrascu

Out of Line Bindings for ProcessUpdate

Hello,

Is it possible to ProcessUpdate a dimension from spcecific rows in my rational DB ?

I saw (very few examples) the use of ProcessAdd with "Out of Line Bindings" for dimensions, but no one talks about ProcessUpdate. Why is that ? Is it possible ?

I haven't try it myself, but don't see why this should be a problem. Out of line binding is a property of the Process command and should work with any type of processing (there are some exceptions such as proactive caching, Linked and Rolap dimensions).