Scheduled Task SQL timeout, but not when running manually

I have a problem where I have to run a C# Console application via a scheduled task. The scheduled task calls a Powershell script which sets some variables (like a connection string) and calls a C# console application which connects to a database with those arguments.

The problem is that when the scheduled task is triggered by the time trigger, I see from the logs the application starting as normal, trying to execute a query, but failing with a SQl timeout.

Manually running the application from the Scheduled Task Window (right-click>Run) works fine, even seconds after it failed when running automatically. This behaviour is so consistent (triggered failed, manual runs) that I don't think it's the database.

It's also not the startup directory, user rights or differences in the arguments, the application is starting, it's just failing at database access.

What can be the cause of this behaviour and how do I remedy it?

Some settings of the task:

• Run with highest privileges: ON
• Run whether the user is logged in or not
• Do not store password: OFF
• User account is the same as the logged in user
• Trigger not stopped when running to long
• Startup directory and script location correct (application is starting and logging, just failing at database access. Manual Right-click>Run works always)

Stack trace of the error:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TrySetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader

Thanks for your help!

I am sure I remember having the same issue with timeout occurring while trying to get table META data.

I may well be very wrong here, but if I do remember correctly, this was down to the failure of the column type detection by trying to parse a date in the wrong (or unexpected format). I have a vague recollection of this having to do with mismatch between the operating system Regional Settings, current user regional settings and the SQL Server Language setting.

Should your query return any date fields?

I think this was resolved by ensuring that as soon as the database was connected, the following query was executed to force the DB connection to use dates is ISO format:

        using (SqlCommand sql = new SqlCommand("SET DATEFORMAT YMD", _conn))
        {
            sql.ExecuteNonQuery();
        }

It would follow that when you run the task interactively, it will connect to the database and the format will be in your active regional setting, however when run automatically by via Scheduled Tasks, it only impersonates your user but does not carry the associated regional settings, it uses those of the OS instead.