Huge SQL Statement, VBA, Timeouts

LostInDaJungle · Aug 22nd 2012

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?

Craig Ottley · Aug 25th 2012

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

carlmack · Aug 26th 2012

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.

Code

Application.ODBCTimeout = 100

To convert SQL can use a bit of gen.

Code

Public Sub ConvertSQLStrToVBA()
Dim LastRow As Long
Dim RowCounter As Long
Dim StartStr As String
Dim EndStr As String
Dim SubStartStrRows As Long
Dim RowStr As String
Dim AutoArrayCounter As Long
Dim counter As Long
Dim AutoArray(1000) As String


For counter = LBound(AutoArray) To UBound(AutoArray)
        AutoArray(counter) = ""
Next counter


StartStr = Chr(34) & "  "
EndStr = "  " & Chr(34) & " & _"


Sheet2.Range("A1:Z65000").ClearContents
LastRow = Sheet1.Range("A65000").End(xlUp).Row




AutoArray(1) = "Dim SQLStr as String"
AutoArray(2) = "SQLStr = " & Chr(34) & " " & EndStr
SubStartStrRows = 2


For RowCounter = 1 To LastRow
    RowStr = Sheet1.Range("A" & RowCounter).Value
    If RowCounter <> LastRow Then
        AutoArray(RowCounter + SubStartStrRows) = StartStr & RowStr & EndStr
    Else
        AutoArray(RowCounter + SubStartStrRows) = StartStr & RowStr & " " & Chr(34)
    End If
Next RowCounter




RowCounter = 0
For AutoArrayCounter = LBound(AutoArray) To UBound(AutoArray)
        RowCounter = RowCounter + 1
        Sheet2.Range("A" & RowCounter).Value = AutoArray(AutoArrayCounter)
Next AutoArrayCounter
End Sub

Display More

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

Huge SQL Statement, VBA, Timeouts

Participate now!

Share