I have a SQL agent job set to execute a SQL query against a single database and then save each row into a different file with powershell before zipping the files up and emailing me.

In one of my SQL instances this job runs just fine without any modification. In another instance the execution of the query times out after 5 seconds. I've looked and I can't find any timeout settings on the server or in the script that are remotely that low.

I tried modifying the connection string and sqlcommand both to have timeout values of 600 and for some reason this is still timing out after an extremely short period of time. I've tried running the query via SSMS and the results are returned in about 15 seconds.

What else can I look into to try and troubleshoot this?

  • Can you post the query ? If it is a SP, then you can use WITH RECOMPILE. Try running a server side trace and capture user error message with statement started and completed, sp started and sp completed. – Kin Shah Apr 15 '15 at 19:05
  • 1
    Can you post your powershell code? I know you mentioned sqlcommand but some cmdlet's such as invoke-sqlcmd had minor bugs with timeout settings, and this would be nice to confirm what's happening. – Ali Razeghi Apr 15 '15 at 19:16
  • Kin - this does not use a SP. Ali - I've cleared the code of a few things and it ended up being a little long -> pastebin.com/qwakP3yM – Kimmels n' Bits Apr 15 '15 at 19:33

Here is what I use in my PowerShell scripts that I experience issues for timeout.

Invoke-SQLcmd $SQL -QueryTimeout 65535 -ConnectionTimeout 0

Reference: https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.