Let me start by saying what the problem is before I go into possible solutions.
I have a pretty big SQL statement. 113 lines of code that returns an elegant and refined dataset ready to rock with some pivot tables. The resulting dataset is ~16,000 lines. The statement works in MS Query, so I feel good about the validity of the code. However even in SQL Server management studio the code takes ~1 minute to run. When I try and return the data to Excel 2007, the query times out and I get no data.
Currently, this is just done using MS Query, with no VBA code manually written.
My understanding is that there is no way to change the built-in 30 second timeout in Excel without writing VBA code. If I'm wrong on that, PLEASE let me know so I can stop beating my head against the wall.
So, now there's the question of how to make this work in VBA. Trying to search for this has just led to 50 pages of "Use MS Query."
1) Is there a way to increase the timeout globally?
2) is there a way to call a connection already defined by MS Query to refresh with a longer timeout?
3) Is there a "Heredoc" syntax like Perl that I can use to do my SQL statement so I don't have to escape 113 lines of SQL? Can I call this SQL from a .dqy file?