error Openrowset

my DMX query is:

INSERT INTO [Analyze Shipments]
(
SKIP,
[Store Id],
[Vendor Name],
[Product Type],
[Days Since Last Shipped],
[Quantity]
)
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\data mining va XML\0212025_0212279\DATABASE\Access\SavingsMart\SavingsMart.mdb;Persist Security Info=False',
'SELECT DISTINCT
"vw_Shipments"."ShipmentID"
AS "Shipment Id",
"vw_Shipments"."DaysSinceLastShipped"
AS "Days Since Last Shipped",
"vw_Shipments"."StoreID"
AS "Store Id",
"vw_Shipments"."Quantity"
AS "Quantity",
"vw_Shipments"."VendorName"
AS "Vendor Name",
"vw_Shipments"."ProductType"
AS "Product Type"
FROM "vw_Shipments"')

and error is:

Error (Data mining): This server is not configured to support the 'microsoft.jet.oledb.4.0' provider used in the OPENROWSET clause.

Can you help me,please?

Moving to DM forum.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

You should make sure that:

- Ad hoc open rowset queries are supported by the server (the AllowAdHocOpenRowsetQueries server property)

- the specified OLE DB provider, Microsoft.Jet.OLEDB.4.0 is included in the list of supported providers (the AllowedProvidersInOpenRowset server property)

More details: http://msdn2.microsoft.com/en-us/library/ms175443.aspx

|||

thank you.

I checked AllowAdHocOpenRowsetQueries and AllowedProvidersInOpenRowset is SQLOLEDB.1
but run my DMX query:

INSERT INTO [Analyze Shipments]
(
SKIP,
[Store Id],
[Vendor Name],
[Product Type],
[Days Since Last Shipped],
[Quantity]
)
OPENROWSET('SQLOLEDB.1','Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;
Initial Catalog=Mart;Data Source=hong',
'SELECT DISTINCT
"vw_Shipments"."ShipmentID"
AS "Shipment Id",
"vw_Shipments"."DaysSinceLastShipped"
AS "Days Since Last Shipped",
"vw_Shipments"."StoreID"
AS "Store Id",
"vw_Shipments"."Quantity"
AS "Quantity",
"vw_Shipments"."VendorName"
AS "Vendor Name",
"vw_Shipments"."ProductType"
AS "Product Type"
FROM "vw_Shipments"')

and error is:
Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Analyze Shipments_Structure ~MC' cube from the data2 database.

can you help me?

|||

INSERT INTO does column mapping by ordinal, i.e. the order of the columns in the INSERT INTO clause has to be the same as in the source data query. In your DMX statement the order is:

SKIP,
[Store Id],
[Vendor Name],
[Product Type],
[Days Since Last Shipped],
[Quantity]

In your source data query, the order is

ShipmentID
DaysSinceLastShipped
StoreID
Quantity
VendorName
ProductType

which means you are doing things like putting the Vendor Name into the Days Since Last Shipped column, which would give type conversion errors - if you're lucky. If you were unlucky, and you happened to mismatch columns to places where they actually fit, it would have worked and you would have a meaningless model.

sql