Microsoft Query hangs

[peglegpete]

Hi, thanks for reading. Before I begin, let me say I have searched google a good amount before posting. I am posting with hope that someone has experience with this issue. With that said, lets get into it.


I have an SQL Server that has a query (a view stored on the server) which takes roughly 10-20 seconds to execute. This execution time is the same in Excel 2007 if I import data to worksheet/pivottable. It is also the same in Excel 2003 while importing data into a worksheet. It returns about 350 rows.

However, if I try to make a pivot table in 2003 based on this query, it is much slower. I can set it up fine, but refreshing this pivot table takes at least 10-20x as long to complete, in the order of 5-20 minutes, to sometimes taking so long that I give up and kill it by hitting escape a bunch.

It's important to note that I am forced to used Microsoft Query to set up a pivot table in Excel 2003. There is no other way, I believe.

While it is running, Excel just sits there saying "Waiting for data to be returned from Microsoft Query".

Here is the weirdest part. Once the refresh starts, Microsoft Query loads in the background, you can see it on the taskbar. I believe that is expected behavior, but if I click on MS Query and view its datatable, I can see all the data is sitting there.

So what is the hold up? MS Query has the data. Excel is waiting for it to be returned. What is going on? And remember from above, if I do a simple import data to worksheet, MS Query is not used and everything is fast.

So my question is does anybody have any tips/advice on this issue? I need a way to not use MS Query for a pivottable's data source or I need a way to make MS Query not freeze up. I don't get why MS Query needs to be involved in the first place since I am not doing any parameters or so forth.

Thanks in advance for any and all replies.

 

[peglegpete]

I should add that the most obvious solution to this problem is to import the data into a worksheet, and then create a pivot table based on that data.

This will not work for my situation because we need to be able to handle 65,000+ rows, so the data must be stored in the pivot itself.

Our users cannot use Excel 2007 to avoid the 65k row limit.

Regardless. I should be able to set up a pivot table based on a SQL Server table/view which only has 350 rows without waiting a century for it to complete, especially when I've proven it can complete in under 30 seconds in every other situation.

 

[peglegpete]

I should also add that I have posted this question on several other boards. This should signify my desperation, not my desire to waste people's time. I sincerely hope you understand.


http://www.vbaexpress.com/forum/showthread.php?p=179471
http://www.xtremevbtalk.com/showthread.php?t=305541
http://www.vbforums.com/showthread.php?p=3471934
http://www.mrexcel.com/forum/showthread.php?p=1873573
http://www.excelforum.com/excel-general/676016-microsoft-query-hangs.html

 

[peglegpete]

SOLUTION FOUND!



http://fluxenkind.blogspot.com/2008/01/workaround-excel-hangs-when-launching.html



It doesn't make a lot of sense, but it 100% works. My pivot table refreshes now without using MS Query at all. Everything is much faster and I can even see the row count as it comes in, so it's not blind faith that it is working.


:D

 

[Fazza]

hi, Pete.

Sounds like you have a solution. However I am writing to inform of a way other than MS Query to create the pivot table. In this example http://www.mrexcel.com/forum/showthread.php?t=315768 a pivot table is created from worksheets: with a different connection it could be from SQL Server or wherever.

Regards, Fazza

 

[peglegpete]

Thanks for your alternative solution, Fazza. I'll have a look into it!