All,

I could use some help with the following:

I am trying to link excel powerpivot (excel 2013) to our database (with OLEDB driver for 11.1 with 32bit). However I keep getting the message that an operation is cancelled due to another operation which is not working.

If i make for example 2 excel files with 1 file getting all the data from our database in a traditional way (OLEDB driver and importing data from external source) and i make 1 file as the "power pivot" file i can link the 2 without any problems.

Is there a known issue with the OLEDB driver in combination with powerpivot for excel 2013?

thanks in advance, if more information is required please let me know.

I also noted that queries are getting an additional "N" for example date = N"10/01/2014"

Kind regards, Johan

asked 20 Jan '14, 05:35

johan's gravatar image

johan
31115
accept rate: 0%

edited 20 Jan '14, 05:38


http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4d87e007-ae98-41a0-b980-b39b23c57ba6/are-oledb-data-types-restricted-in-powepivot?forum=sqlkjpowerpivotforexcel

Check out the above discussion. Changing the order of timestamp & numeric fields in the SQL select statements resolves the problem.

"TIMESTAMP field followed by a numeric field produces the error. If a char field is between this works!"

link

answered 20 Jan '14, 23:44

Rafick's gravatar image

Rafick
2393311
accept rate: 33%

Hi Rafick,

Thanks for your answer. I will see what i can do in my SQL statements. This is however a shame, importing a full table will not work in this way.

Thanks and i'll keep you posted on my progress...

(20 Jan '14, 23:57) johan

I've been playing around with this using the sample data imported into ADS from the MS tutorial.

I was able to duplicate this on some tables. Just playing around it seemed to be a combination of having both timestamp/date and money fields selected.

It's not the best answer, but I wanted to share my experience with you. Are the tables it is failing for in your instance similar?

(BTW, the "N" formatting, is ok. ADS begun supporting this notation in version 11)

link

answered 20 Jan '14, 18:00

Edgar%20Sherman's gravatar image

Edgar Sherman
5.4k13185
accept rate: 25%

Hi Edgar,

I have checked and indeed it seems to be timestamp/date/money field related. I have been able to import a table which doesn't have any columns of these kind.

Do you have a solution on how to fix this (or can you point me into a direction)? the time/date/money field are import for my report.

I hope you can help,

Thanks!

(20 Jan '14, 23:25) johan

All,

Thanks for your help. I have been able to work around the issue. I am now making a query myself and taking into account the "timestamp fields". Unfortunatly importing a complete table with the standard functionality of PowerPivot is not possible.

Kind regards, Johan

link

answered 21 Jan '14, 10:19

johan's gravatar image

johan
31115
accept rate: 0%

Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×16

Asked: 20 Jan '14, 05:35

Seen: 2,248 times

Last updated: 21 Jan '14, 10:19

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.