x

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

asked Jan 01 '12 at 05:41 AM in Default

FarhanN gravatar image

FarhanN
5 2 2 2

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

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

answered Jan 01 '12 at 10:20 AM

GPO gravatar image

GPO
1.9k 32 35 39

+1 : I wish I could give more.......
Jan 01 '12 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 '12 at 10:33 PM FarhanN
(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 '12 at 11:59 PM

Sacred Jewel gravatar image

Sacred Jewel
1.6k 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 '12 at 11:29 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

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 '12 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 '12 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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x341
x108
x20

asked: Jan 01 '12 at 05:41 AM

Seen: 2660 times

Last Updated: Jan 01 '12 at 09:50 PM