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