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:
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?
answered Jan 01 '12 at 10:20 AM
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.
answered Jan 01 '12 at 11:59 PM
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 :)
answered Jan 01 '12 at 11:29 PM