Sql-server – SQL Server 2008 R2 : Unexpected timeout with SQL Agent Job/powershell

powershellsql-server-2008-r2sql-server-agent

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?

Best Answer

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

Related Solutions

Sql-server – SQL Agent powershell context reference

If you look in SQL Server BOL, SQL Server Agent provides a set of "tokens" that it will substitute into both the job step command text and the output file (the later will prevent the GUI "view" button from working). These tokens seem to work for any type of step except T-SQL.

https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps#sql-server-agent-tokens

So, if you have a SQL 2008 PowerShell step, you can start it with:

$sqlInstance = "$(ESCAPE_DQUOTE(SRVR))"

You may need to use MACH (machine name) and INST (just instance name) instead, because with the default instance SRVR == MACH, but with named instances SRVR == MACH\INST.

Sql-server – SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

The problem is that the PowerShell session is using the SqlServer provider by default. I added this statement into the script before the Out-File cmdlet:

Set-Location c:

This changed the provider to the FileSystem provider, and allowed the Out-File cmdlet to correctly find the file share and create the file.

This article contrasts how SQLPS loads in SQL Server 2008 R2 vs. SQL Server 2012/2014. The SQL Provider is loaded using the 'Add-PSSnapin' cmdlet in SQL Server 2008 R2, while starting with SQL Server 2012, the 'Import-Module' cmdlet is used.

The author states: "The outcome of loading the module [with 'Import-Module'] is simply changing to the PSDrive you just loaded." Evidently, the 'Add-PSSnapin' cmdlet left the provider for its SQLPS session unchanged (as FileSystem). That change in cmdlet use, and the resulting session behavior, was what caused my problem.

Best Answer

Related Solutions

Sql-server – SQL Agent powershell context reference

Sql-server – SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

Related Question