Query timeout error Excel VBA

Question

I'm trying to run a SQL Server query via Excel VBA which gives me below error. I have tested this query in SQL Server Management Studio & it working perfectly fine. It takes about 4 mins to run in SSMS, but fails via VBA. I have already tried increasing the timeout up to 600 secs & setting it to zero as well.

Surprising part is if I debug my VBA & copy the script from it (since it's generated @ runtime based on different values) even that run perfectly after pasting in SQL Server Management Studio?

What am I missing here? Any pointers would be highly appreciated...

PS: if I run another query with same piece of code, it runs correctly!

I suggest that you should optimize your query which takes 4 minutes. For that you should see execution plan and if you need help optimizing post a different question or modify the current one to include the query — DhruvJoshi
– DhruvJoshi, Commented Nov 20, 2017 at 13:59
At which minute are you getting the timeout, if you set it to 600 seconds? — Vityata
– Vityata, Commented Nov 20, 2017 at 14:02
Most probably you are setting the timeout to the connection and not to the query itself, as 600 seconds is quite a lot of time. Show a bit of code but in general see -> stackoverflow.com/a/19780639/5448626 — Vityata
– Vityata, Commented Nov 20, 2017 at 14:04
I am shocked that somebody thought this was worthy of an upvote. Seriously this kind of question is worthless without the query at the absolute minimum. — Sean Lange
– Sean Lange, Commented Nov 20, 2017 at 14:43
@Vityata - bang on , that solved the problem, it wasn't about the time required to connect to database but time required to execute the query (command), so "DBConn.CommandTimeout = 0" solved the problem. — sk8er_boi47
– sk8er_boi47, Commented Nov 21, 2017 at 12:07

sk8er_boi47 · Accepted Answer · 2017-11-21 12:12:49Z

3

Using: DBConn.CommandTimeout = 0 solved the problem for queries that take longer for execution & cannot be optimised further.

Thx a ton @Vityata for pointing me to right direction =)

answered Nov 21, 2017 at 12:12

sk8er_boi47

3831 gold badge4 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query timeout error Excel VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related