2

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?

3
  • 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, 2015 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. Apr 15, 2015 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 Apr 15, 2015 at 19:33

1 Answer 1

1

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

Invoke-SQLcmd $SQL -QueryTimeout 65535 -ConnectionTimeout 0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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