Microsoft Query hangs

This is a "cross-post" thread. See reply below for those links. *Please* don't disregard as annoying spam though, I seriously appreciate any effort to assist me, through any means.

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.

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Sorry, I definitely didn't mean to break a rule of your forums. I have posted this question on several other boards. This should signify my desperation, not my desire to waste people's time. I 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-gene...ery-hangs.html

SOLUTION FOUND!



http://fluxenkind.blogspot.com/2008/...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

Thanks for posting back, PegLeg, I'm sure it will help others.

Good thinking Teylyn and thanks. I hate when I follow a link to an old thread and find the link is no longer in existance