0

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.

Stavros Jon
  • 1,695
  • 2
  • 7
  • 17
Matías Romo
  • 100
  • 6

1 Answers1

1

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.

Dr. Mantis Tobbogan
  • 540
  • 8
  • 20
  • I haven't tried declaring the actual Query variable, no. The string parsing doubt came to me after reading [this](https://www.tek-tips.com/viewthread.cfm?qid=586534). I will try declaring the variable and update you. – Matías Romo Jan 31 '19 at 17:06
  • I tried adding it, still an error, though I believe it might have something to do with the concatenation, because I try to copy paste the output query into Netezza and it doesn't work either. Trying to debug atm. – Matías Romo Jan 31 '19 at 17:26
  • Found the answer, there was a formatting error in the code, but the Query declaration statement solved it as well, thanks! – Matías Romo Jan 31 '19 at 17:59
  • That is what I was thinking. Was it a problem with a carriage return or line feed by any chance? – Dr. Mantis Tobbogan Jan 31 '19 at 18:04
  • One of the lines was missing a parenthesis for the select joins. Fixed that, worked like a charm. – Matías Romo Jan 31 '19 at 19:25