Importing from PQ to PP not working when many columns in table (Excel 2010 32bits)

[olivierhbh]

Hello All,

I've been facing a new issue for a few days.
It's a bit related to an old problem Ozeroth solved for me back then (http://www.mrexcel.com/forum/power-...y-table-properties-show-fields-not-query.html)

Since then, I never had any problem, fetching data in PQ from SQL Server, setting the connection on default, clearing the SQL statement and importing the table into PP.

I recently started a new project and I've had this message since then:
Failed to retrieve data from "Table". Reason: Exception from HRESULT: 0x80040E57

This happens when importing the tables for the first time, and clicking on preview & filter.

After many attempts to solve the problem, I found out that if I reduce the number of columns, it works.
But the max number changes, sometimes it's 20, sometimes it's less than 10.
It's really out of my understanding...

I feel I'm doing something wrong since I never had this problem before. and my old files still work perfectly.

Any help would be greatly appreciated.

Olivier.

 

[ImkeF]

What does the task-manager say: Full RAM and CPU?

Table just might be too big. Reason for irregular behaviour (number of column) will probably lie in the fact that PQ always automatically executes other processes in the background as well. It might depend on them how much resources will be left to your "main query".

I'd send a frown to MS to get a confirmation or other explanation.

 

[olivierhbh]

34 columns and 1 row, same error. So not really heavy... Ok I'll try to get some help from MS.
If any other idea, would be really welcome.

 

[olivierhbh]

Ok, I partially solved it, from a raw sql query there was a lot of columns with undefined types. When I set a type to each column (which will now be my first action always, that I used to tell people to do it and I didn't) it works perfectly.

However. Once imported in PP, when I edit the PQ table, it always create a new connection in data/connections instead of just updating the one linked to the PQ table.

The reason I guess, is that I set the connection to command type default and command text blank, then it's not recognized.

So if I update a PQ table 5 times, I will have 5 new connections. Any idea on how to solve that?

Thanks,
Olivier.

 

[olivierhbh]

Solved it all!
If I leave the SQL statement in the command text from connection properties, it just updates it, reverts the command type to SQL but as the powerpivot connexion is Default, it's not a problem.
So all good!