2

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?

enter image description here

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sk8er_boi47
  • 383
  • 1
  • 4
  • 15
  • 1
    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 Nov 20 '17 at 13:59
  • At which minute are you getting the timeout, if you set it to 600 seconds? – Vityata Nov 20 '17 at 14:02
  • 1
    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 -> https://stackoverflow.com/a/19780639/5448626 – Vityata Nov 20 '17 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 Nov 20 '17 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 Nov 21 '17 at 12:07
  • @marc_s - Thx a ton for correcting the question, i'm sure that helped get me the right solution. – sk8er_boi47 Nov 21 '17 at 12:11
  • @sean Lange - no need to overreact mate, I already mentioned the query generated during runtime did run without any issues manually so it was pretty evident that query wasn't the problem. – sk8er_boi47 Nov 21 '17 at 12:11
  • I didn't overreact. Your question provided zero details about the query and you are asking why it timed out. To be honest, changing the timeout duration is fixing the symptom instead of the problem. You need to optimize your query and it will benefit everyone. – Sean Lange Nov 21 '17 at 14:11

1 Answers1

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 =)

sk8er_boi47
  • 383
  • 1
  • 4
  • 15