sqlsql-serverexcelvbanzsql

Long Query Execution through VBA concatenation

I'm setting up an automated excel vba workbook that extracts data through a Query, while getting the parameters from Excel cells.

So far it has worked on simple Queries and SPs but I'm trying to pass a very long query and it seems Excel Functions is truncating the string to 1043 chars.

*The query has 1426 chars.

I've taken 3 different approaches to this problem.

  1. Executing a SQL stored procedure (from vba) rather than a query, the problem is one of the input parameters is a list of ids, which is variable in length, and very long to pass as an SP parameter, so it hasn't worked even when assigning Varchar(Max) as type.

  2. Executing the query into a new table, and extracting the full table into excel. While this does work it makes it necessary to update the id list manually, hence it's not functional enough.

  3. Passing the SQL string directly to the Excel function as a concatenation of strings, but it throws the error which I believe relates to the string truncate.

My code is as follows:

Private Sub CommandButton1_Click()

Dim organization As Integer  'Business Unit
Dim material As String  'List of IDs

organization = Sheets("Hoja1").Range("B3").Value   'Extract B3
material = Sheets("Hoja1").Range("B4").Value     'Extract B4

'Parsing the query into statements to be concatenated 
Connection
With ActiveWorkbook.Connections("NZSQL Test").ODBCConnection
sql1 = 'statement1 
sql2 = 'statement2
sql3 = 'statement3
sql4 = 'statement4
sql5 = 'statement5
sql6 = 'statement6

Query = sql1 & sql2 & sql3 & sql4 & sql5 & sql6
Sheets("Hoja1").Range("B2") = Query 'This is to beused as revision directly 
in ODBC platform
.CommandText = Query
ActiveWorkbook.Connections("NZSQL Test").Refresh
End With
End Sub

The resultset I'm getting when pasting the B2 query in SQL:
/* ERROR [42000] ERROR: '...' 'Returns a part of the SQL string */

The string here is truncated to 1043 chars. However, I have tried the same approach from Python using pyodbc and it works perfectly.

Solution

  • Have you tried explicitly declaring your Query variable as a string with your other declarations in the sub?

    Dim Query As String

    According to here, a string can:

    • A variable-length string can contain up to approximately 2 billion (2^31) characters.
    • A fixed-length string can contain 1 to approximately 64 K (2^16) characters.

    Hopefully that helps.