Microsoft Query hangs

**PegLegPete** · 03-16-2009, 02:42 PM

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.

**PegLegPete** · 03-16-2009, 02:43 PM

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.

**VBA Noob** · 03-16-2009, 02:53 PM

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!

**PegLegPete** · 03-16-2009, 03:00 PM

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

**PegLegPete** · 03-16-2009, 06:58 PM

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

**shg** · 03-16-2009, 07:13 PM

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

**teylyn** · 03-17-2009, 04:18 PM

This is the text that lives under the link above.
Thought I'd catch this and keep it here, since the blog may not be around forever...

Thursday, January 10, 2008
Workaround: Excel hangs when launching MSQuery

This seems to be a problem for a lot of people, as there are at lot of pages asking how to fix this issue. The issue is that when you want to create a new query or edit a query that pulls data into Excel, Excel will attempt to launch MSQuery and hang, only displaying the message "waiting for data to be returned from microsoft query".

Here is a very simple workaround:

1) First of all, create your query in MSQuery directly, then save the query somewhere.

2) Go to Excel, select Data --> Import External Data --> Import Data

3) In the "Select Data Source" dialog box, do not select the odbc connection to the db, instead select the query you created in MSQuery.

That's it, the data will now import correctly, and the profile will save into your Excel worksheet as normal. Just thought I'd share this as I haven't seen a solution posted anywhere.

Posted by -J at 10:39 AM