asked
Too long SQL in Excel VBA
Hi, I wrote query that is of about 56,514 characters in which I have to union 9 different temp tables into the tenth table which gives me final results after some calculations. I want to pull it in MS Excel using VBA because I have to use some inputs from sheet cells. Since Excel wouldn't allow me paste all the 16 pages in one Cell, I put the query in 16 different cells using Excel's ""&"" concatenation to keep the text strings under 255 chars. There are about 4,000 characters in each cell now concatenated @ intervals of less than 255 chars (or Excel wont let me have it). Now, when I use the below pasted macro to get the data, it throws me an Application error 1044 ~ which I persume is because of the string length. Macro code is: Sub Details() Dim sql sql = Sheets("Sheet1").Cells(1, 10) + Sheets("Sheet1").Cells(1, 11) + Sheets("Sheet1").Cells(1, 12) + Sheets("Sheet1").Cells(1, 13) + Sheets("Sheet1").Cells(1, 14) + Sheets("Sheet1").Cells(1, 15) + Sheets("Sheet1").Cells(1, 16) + Sheets("Sheet1").Cells(1, 17) + Sheets("Sheet1").Cells(1, 18) + Sheets("Sheet1").Cells(1, 19) + Sheets("Sheet1").Cells(1, 20) + Sheets("Sheet1").Cells(1, 21) + Sheets("Sheet1").Cells(1, 22) + Sheets("Sheet1").Cells(1, 23) + Sheets("Sheet1").Cells(1, 24) + Sheets("Sheet1").Cells(1, 25) Range("a10:e100") = "" Dim connString connString = "ODBC;DSN=SNLDB2;Description=DB2;UID=FarhanN;Trusted_Connection=Yes" Dim thisQT As QueryTable Set thisQT = QueryTables.Add(Connection:=connString, Destination:=Range("a10")) thisQT.BackgroundQuery = False thisQT.sql = sql thisQT.Refresh End Sub Please help me with fixing up this error!! I have tried the results of these 16 cells text formula in MS Query and it does provide me results; therefore, the query is good to go but I dont know what's wrong with the VBA thing as it cannot do thisQT.Refresh. Alternatively, is there a way out to run SQL query in MS Query and take values for variables from sheet cells? I wont be able to build up a 16 pages long query using MS Query's query wizard, though. Thanks in advance for your help.
Can't you just create a stored procedure and execute that?
I dont think I have enough rights on the server to create an SP. Also, in my next version of this query, I'll be taking almost all the input values (roughly 50+) from Sheet cells making it user friendly for some users who are even newbies to Excel. Can any other method be used? or Shall I share the query here?
I agree with GPO - your query seems a little long to be executed as dynamic SQL. But I get it; that's not what you're asking for. So, to your problem instead: The limitation for the CommandText property is 32767 characters. So if you have 4000 characters in each of 16 cells you have some serious work to do in order to reduce redundant space from your SQL string :)
Thanks a lot GPO and Magnus. I wanted to select both as my "accepted answers" but could select only one. Though, according to Magnus reply - I came to know about the max limit of commandText property and added three additional temp tables to cut down the query. It has started working in VBA now but I would still try getting my SP request approved to make the process easier for others. I'm not attaching my query here to avoid spamming but thanks again !!!
I agree that a Stored Procedure would be the best approach, but even if you do not have the rights to make a stored procedure, you could ask your sysadmin people to make an SP for you. This way you could have limit the SQL Injection attacks. Now, you need to validate all the input variables at application level. But if still you cannot have the Stored Procedure, you could break down the execution of your query into multiple steps, since, it involves temp tables already. If you are not able to do, then post your query.
The maximum permitted length for a querytable.commandtext is 32,767 chars. If you attempt to assign a larger string, you will trigger an object-defined error.
If you're willing to accept the pros and cons of global temp tables, you can execute your sql statement in different steps. Select from into ##9TempTables Then substitute the 9 temp table union queries with ##9TempTables in another vba query sent to sql server. You may not have the rights to do this and you'll need to cleanup your mess afterwards, but this could eliminate the need for your admin to manage a stored procedure.
2 People are following this question.
