Too long SQL in Excel VBA


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
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.
more ▼

asked Jan 01, 2012 at 05:41 AM in Default

FarhanN gravatar image

5 2 2 2

(comments are locked)
10|1200 characters needed characters left

5 answers: sort newest
Can't you just create a stored procedure and execute that?
more ▼

answered Jan 01, 2012 at 10:20 AM

GPO gravatar image

2.9k 35 38 42

+1 : I wish I could give more.......
Jan 01, 2012 at 02:40 PM Kev Riley ♦♦
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?
Jan 01, 2012 at 10:33 PM FarhanN
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered May 15 at 11:01 PM

Jeff O gravatar image

Jeff O
142 5

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered May 12 at 11:48 AM

saolin gravatar image


(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jan 01, 2012 at 11:59 PM

Sacred Jewel gravatar image

Sacred Jewel
1.7k 2 4 5

(comments are locked)
10|1200 characters needed characters left

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 :)
more ▼

answered Jan 01, 2012 at 11:29 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

Okay - I'll do the SP thing and send it to sysadmin. Thanks all for the help. I will buzz again if I couldn't get the SP thing approved or breaking down the query to multiple steps.
Jan 02, 2012 at 06:06 AM FarhanN
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 !!!
Jan 16, 2012 at 03:49 AM FarhanN
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 01, 2012 at 05:41 AM

Seen: 3554 times

Last Updated: May 15 at 11:01 PM