Huge SQL Statement, VBA, Timeouts

  • 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?

  • Re: Huge SQL Statement, VBA, Timeouts

    You can change the Timeout for the query but it requires you to change the Registry... Its something like HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\QueryTimeout depending on how you're connecting to the data source.

    If you've got such a big sql statement you should consider creating a stored procedure in the database. Big statements are never good calling from excel, you're better off breaking it down into chunks in SQL Server

  • Re: Huge SQL Statement, VBA, Timeouts

    Agree with Craig - use stored procedure if poss.

    I see there is an Excel ODBCTimeout property that you could try eg.

    Application.ODBCTimeout = 100

    To convert SQL can use a bit of gen.

    Paste SQL in Sheet1 A1, run code, result in Sheet2.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!