Friday, May 07, 2010 2:04 PM
we are trying our OLEDB Provider (own developed for SAP tables) with PowerPivot and it seems that some of the OLEDB datatypes are not supported.
When I try to import a certain table I get this message:
The following system error occurred:
The operation has been cancelled.
The same table works on the "preview and filter" page well!
When I am using only string columns from this table it also works.
So I think the problem will arise with some OLEDB datatypes (DBTYPE_DBTIMESTAMP, DBTYPE_NUMERIC).
Any Ideas or documentations?
Monday, May 10, 2010 9:11 PM
Hi Andi - can you export this table using SSIS (or any other tool) into CSV file? This looks more as data issue.
If you can post here table definition and your database version - I will try to reproduce your error.
Wednesday, May 12, 2010 1:58 PM
sorry for the delay - I have tested a lot of things.
First - I can not give you a table definition because my OLEDB provider extracts data from a SAP system (= source).
And it is definitly NOT a data issue, because the error occurs when gathering the meta data for the colums - before any data is fetched.
The problem with PowerPivot looks like this:
- it only occures if I am extracting a numeric (DBTYPE_NUMERIC) AND a timestamp (DBTYPE_DBTIMESTAMP) type together
- after reading the column metadata (interface IColumnsInfo::GetColumnInfo) i get this error:
The following system error occurred:
- Just numeric columns work well / Just timestamp columns work well / The Preview with both combined also WORKS!?!
- The whole thing works very well in SSIS packages no problems with those types
The only thing I can post is the data of my ATLCOLUMNINFO structure, which is provided by my OLEDB Provider (interface IColumnsInfo::GetColumnInfo)
Ordinal Name Type Precision Scale Offset ColumnSize 0 __BOOKMARK__ DBTYPE_UI4 10 255 0 4 1 CONNID DBTYPE_NUMERIC 4 0 4 19 2 FLDATE DBTYPE_DBTIMESTAMP 16 3 23 16
The Flags for Ordinal 1 and 2 are set to DBCOLUMNFLAGS_ISNULLABLE | DBCOLUMNFLAGS_MAYBENULL | DBCOLUMNFLAGS_ISFIXEDLENGTH | DBCOLUMNFLAGS_WRITEUNKNOWN.
If you are not able to help me - where could I get support for this issue?
Thanks on advance,
Wednesday, May 12, 2010 4:21 PM
Andi, thanks for the detailed information. Obviously you can use SSIS package to prepare your data but I understand that this is inconvenient. Let me do some research on this error and I will get back to you.
Monday, May 17, 2010 12:39 PM
seems PowerPivot was designed by a different team than Excel 2010.
When I try to import my data via my OLEDB Provider into an "normal" Excel 2010 Sheet this works perfect:
Also a preview in PowerPivot works perfekt
- but when Importing the whole thing I get a not very meaningful error:
Monday, May 17, 2010 2:14 PM
the funny thing is that the table wizard design dialog shows the correct data (NUMERIC and TIMESTAMP field):
But importing those field together I will receive the error of my previous post.
We want to release a seeding version of our OLEDB provider with the release of Excel 2010 powerpivot - so we have to hurry!
Thanks on advance,
Monday, May 17, 2010 3:16 PM
Hi Andy, can you try to cast CONNID as integer? PowerPivot is using column compression and will attempt to compress these values based on the columns content. Looks like numeric 19 is currently storing integer value which will fit into 4 bytes integer. Same is for the FLDATE field - can you convert it to the DATE from Timestamp? These will be changes to your query only.
Tuesday, May 18, 2010 9:53 AM
I can not convert the data types by sql because the backend of my SQL statement is a SAP system which understands only Open SQL.
But anyway this could just be a work around for this case - but imagine really big decimal types with a lot of digits - they never fit into a four byte integer.
I tried to implement a mapping for small DECIMAL types to (DBTYPE_UI4, DBTYPE_UI8 and DBTYPE_R4, DBTYPE_R8 for types with scale) and instead of DBTYPE_TIMESTAMP using a DBTYPE_DBDATE or DBTYPE_DBTIME.
But this did NOT work either!
And now - hold on tight!
It is only the ordinal number of the date columns that matters! When pushing them at the end of the columns everything works fine!
I just undo my changes - and it also works with NUMERIC types in between.
Hey guys this really seems to be a bug in PowePivot!
Tuesday, May 18, 2010 1:16 PM
Hi there again,
The import of NUMERIC and DATETIME fields work fine with Microsoft's own OLEDB Provider for SQL Server.
So I used MS RowsetViewer to figure out how "MS OLEDB Provider for SQL Server" will build up its ColumnInfo and build up mine in excatly the same way.
But I am always facing the error described before: columns of type DBTYPE_TIMESTAMP (or DBTYPE_DBDATE or DBTYPE_DBTIME)
MUST be at the end of a row that the extraction work.
Could this be an untested part of Microsoft PowerPivot? Has Microsoft added a special handling for its own Providers into PowerPivot?
Please help me!
Wednesday, May 19, 2010 2:26 PM
Hi Andi, are there any NULLs in the Date fields? Can you filter them out in your original query and reproduce the error?
I don't believe there are any changes to the providers, but PowerPivot has it's own logic to compress and store data in memory. It will be important to know if this issue related to the data in columns (NULLs etc.)
Thursday, May 20, 2010 1:40 PM
I tried a SQL in which I permited any NULL values:
SELECT CONNID, -- NUMERIC BOOKID, MANDT, -- CHAR CARRID, FLDATE, -- DATE ORDER_DATE FROM SBOOK WHERE BOOKID < 100 AND FLDATE IS NOT NULL AND ORDER_DATE IS NOT NULL
Remind: The dates are at the end of my selection list ==> This works!
Now I push em to the front:
SELECT FLDATE, -- DATES ORDER_DATE, CONNID, -- NUMERIC BOOKID, MANDT, -- CHARS CARRID FROM SBOOK WHERE BOOKID < 100 AND FLDATE IS NOT NULL AND ORDER_DATE IS NOT NULL
And *bam* I get this error: The following system error occurred: The operation has been cancelled.
By the way - this error occures BEFORE any data is fetched - so this seems to be a metadata issue!
I figured out that PowerPivot ist NOT able to import these in OLEDB defined standard types or to import them with their complete Precision and Scale:
My only workaround ist to map the types in my OLEDB provider to fitting smaller types like DBTYPE_UI4 etc. or to map them to strings (which is very poor).
Here a few basic questions:
- Why does PowerPivot not support all OLEDB standard types (defined in Microsofts own standard)?
- If PowePivot does not support all types - why they do not provide a message which columns will not work and why?
- Why does PowerPivot not ignore unsupported columns (at the moment the whole import canceled)?
Thursday, May 20, 2010 3:35 PM
Hi Andy, thank you for detailed analysis. Just to double-check:
This statement :
FLDATE, -- DATES
CONNID, -- NUMERIC
MANDT, -- CHARS
WHERE BOOKID < 100 AND FLDATE IS NOT NULL AND ORDER_DATE IS NOT NULL
Works anywhere else , but fails in PowerPivot?
Can you try to replace Microsoft OLEDB provider for SQL Server with any alternative one? We need to rule out problem with provider.
Thursday, May 27, 2010 2:51 PM
meanwhile I have done a lot of research and tests.
Yes - this selection works perfekt in preview (table import wizard) of PowerPivot and also in SSIS packages.
I tested an alternativ OLEDB provider - Postgres DB (OpenSource) and a commercial OLEDB Provider for Postgres DB.
When I build up the same table metadata and filled it with exactly the same data - it works :(
Ok - so I build the metadata of our OLEDB provider 1:1 to the metadata of Postgres OLEDB provider (using RowsetViewer to compare the all rowsets, etc.)
But it finally does not work - in my OLEDB provider the data will only be imported when all timestamp fields are at the end.
Any ideas what I can do ?
Is a test software for OLEDB providers availlable?
When I use my OLEDB provider in a C# sample project it works perfect and all data is returned correct.
Thanks for any help,
Thursday, May 27, 2010 6:11 PM
HI Andi, can you please post metadata which were generated for Postgres and then modified (failing) ones?
I tried with all kind of Date columns in SQL 2008 and was not able to reproduce the issue. I was using SQL Server Native Client 10.0
Friday, May 28, 2010 1:37 PM
you can not reproduce this error with MS OLEDB providers.
The error occurs when PowerPivot gathers and validates my metadata. But I cannot find our what is wrong because I only get the messag:
The following system error occurred: The operation has been cancelled.
Below I post the schema rowsets of my provider and Postgres OLEDB provider.
The only difference is that some values of my provider returns 0 instead of a null value. But I think this might not matter.
I am working against a black box and it will help me to know which interfaces and schemarowsets are called by the PowerPivot Import-Task.
My Providers Columns Schemarowset
TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_FLAGS IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION SFLIGHT MANDT 1 120 True DBTYPE_WSTR 3 6 0 0 0 SFLIGHT CARRID 2 120 True DBTYPE_WSTR 3 6 0 0 0 SFLIGHT CONNID 3 120 True DBTYPE_NUMERIC 0 0 4 0 0 SFLIGHT FLDATE 4 120 True DBTYPE_DBTIMESTAMP 8 8 0 0 0 SFLIGHT PRICE 5 120 True DBTYPE_NUMERIC 0 0 20 2 0 SFLIGHT CURRENCY 6 120 True DBTYPE_WSTR 5 10 0 0 0 SFLIGHT PLANETYPE 7 120 True DBTYPE_WSTR 10 20 0 0 0 SFLIGHT SEATSMAX 8 120 True DBTYPE_I4 0 0 10 0 0 SFLIGHT SEATSOCC 9 120 True DBTYPE_I4 0 0 10 0 0 SFLIGHT PAYMENTSUM 10 120 True DBTYPE_NUMERIC 0 0 22 2 0 SFLIGHT SEATSMAX_B 11 120 True DBTYPE_I4 0 0 10 0 0 SFLIGHT SEATSOCC_B 12 120 True DBTYPE_I4 0 0 10 0 0 SFLIGHT SEATSMAX_F 13 120 True DBTYPE_I4 0 0 10 0 0 SFLIGHT SEATSOCC_F 14 120 True DBTYPE_I4 0 0 10 0 0
Postgres Columns Schemarowset
TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_FLAGS IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION SFLIGHT MANDT 1 120 True DBTYPE_WSTR 3 6 SFLIGHT CARRID 2 120 True DBTYPE_WSTR 3 6 SFLIGHT CONNID 3 120 True DBTYPE_NUMERIC 4 0 SFLIGHT FLDATE 4 120 True DBTYPE_DBTIMESTAMP 8 8 SFLIGHT PRICE 5 120 True DBTYPE_NUMERIC 20 2 SFLIGHT CURRENCY 6 120 True DBTYPE_WSTR 5 10 SFLIGHT PLANETYPE 7 120 True DBTYPE_WSTR 10 20 SFLIGHT SEATSMAX 8 120 True DBTYPE_I4 10 SFLIGHT SEATSOCC 9 120 True DBTYPE_I4 10 SFLIGHT PAYMENSTSUM 10 120 True DBTYPE_NUMERIC 22 2 SFLIGHT SEATSMAX_B 11 120 True DBTYPE_I4 10 SFLIGHT SEATSOCC_B 12 120 True DBTYPE_I4 10 SFLIGHT SEATSMAX_F 13 120 True DBTYPE_I4 10 SFLIGHT SEATSOCC_F 14 120 True DBTYPE_I4 10
And here is the metadata of the other interface "IColumnsInfo::GetColumnInfo"
Ordinal Name Type Precision Scale Offset ColumnSize
0 __BOOKMARK__ DBTYPE_UI4 10 255 0 4
1 MANDT DBTYPE_WSTR 255 255 4 3
2 CARRID DBTYPE_WSTR 255 255 12 3
3 CONNID DBTYPE_NUMERIC 4 0 20 19
4 FLDATE DBTYPE_DBTIMESTAMP 19 0 39 16
5 PRICE DBTYPE_NUMERIC 20 2 55 19
6 CURRENCY DBTYPE_WSTR 255 255 74 5
7 PLANETYPE DBTYPE_WSTR 255 255 86 10
8 SEATSMAX DBTYPE_I4 10 255 108 4
9 SEATSOCC DBTYPE_I4 10 255 112 4
10 PAYMENTSUM DBTYPE_NUMERIC 22 2 116 19
11 SEATSMAX_B DBTYPE_I4 10 255 135 4
12 SEATSOCC_B DBTYPE_I4 10 255 139 4
13 SEATSMAX_F DBTYPE_I4 10 255 143 4
14 SEATSOCC_F DBTYPE_I4 10 255 147 4
Friday, May 28, 2010 3:41 PM
Hi Andy, thank you! It will take me some time to build, I will update you on my results.
Best regards, Vlad.
Monday, May 31, 2010 8:52 AM
I have experimented with the order of the date (DBTYPE_TIMESTAMP).
In pseudo SQL code the following came out:
SELECT <TIMESTAMP>, <WSTR>, <NUMERIC> FROM ... => works
SELECT <WSTR>, <TIMESTAMP>, <NUMERIC> FROM ... => works NOT
SELECT <WSTR>, <NUMERIC>, <TIMESTAMP> FROM .... => works
SELECT <WSTR>, <TIMESTAMP>, <WSTR>, <NUMERIC> FROM ... => works
Conclusion: TIMESTAMP field followed by a numeric field produces the error. If a char field is between this works!
I thought maybe there will be data overriden through wrong offsets or lengths - but the method where data is loaded is never called.
So this must be a pure metadata issue. Maybe this information leds to a solution.
Monday, June 07, 2010 2:33 PM
Hi Andi, I'm trying to reproduce the issue in my lab, which version of SAP are you using?
Also can you give me details about your client software, drivers etc?
Thank you, Vlad.
Wednesday, June 09, 2010 7:12 AM
this sounds good! At the moment I am building a Dummy OLEDB Provider to reproduce this issue without a SAP system and other software.
How may I provide you the dummy provider and description to reproduce?
Thursday, June 10, 2010 5:27 PM
Hi Andi, you can use vlad_social@hotmail.<NOSPAM>com
Please, remove <NOSPAM>.
I already have access to SAP, it should not take long until I will be able to repro your issue.
Friday, June 11, 2010 7:01 AM
I sent you a dummy provider to reproduce this case with a SQL Server as backend database.
Good luck, Andi
Friday, June 11, 2010 2:30 PM
Hi Andi, thank you I received your email. WIll update you on my results later today.
Best regards, Vlad.
Monday, June 14, 2010 2:32 PM
Sorry, it took me longer than I expected when I tried SQL provider I got this error:
"OLE DB or ODBC error: Conversion failed when converting the nvarchar value 'AA' to data type int.; 22018.
An error occurred while processing the 'Query' table.
The operation has been cancelled."
My query looked as follows:
"SELECT FLDATE, CONNID, MANDT, CARRID
WHERE CONNID < 100 AND FLDATE IS NOT NULL "
I will check into this error, but at this point it does look like metadata issue. However when I run this query in Management Studio and then tried to reproduce the error again query was executed without any errors.
With the dummy provider I was able to reproduce the problem.
Tuesday, June 15, 2010 11:33 AM
I do not have a conversion error for my queries (but I do not use any WHERE statements there). Maybe the dummy provider has a gap there.
I am proud that you can reproduce my case and "feel my pain".
I hope for suggestions to avoid this metadata problem - because we want to release our product with the launch of Office 2010 in Europe.
Waiting for help...
Tuesday, June 22, 2010 1:57 PM
Hello Andi, after making decent number of tests I can tell that issue is 100% reproducible with your provider, but I cannot reproduce it with anything else. The first error I got might be related to the low memory conditions on my VM, and I never was able to reproduce it again.
So here is the plan: attach PowerPivot to the debugger and check metadata being sent in "working" and "failing" queries.
I suspect that something is not transferred properly when first field is of the Date type.
If you want, you can email me symbols for your demo provider and I will take look on it too.