1

I am uploading a mdb file from asp.net upload control into my sql server database. When I am uploading file it is calling a stored procedure from my database in which I m executing my SSIS packages. Whenever I am calling my stored procedure after few seconds I m getting the exception with showing the path of my packages.

$exception {"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.\r\ndtexec /F \"C:\DTSX\DTSXPackage\DTSXPackage\Package.dtsx\""} System.Data.SqlClient.SqlException

As I read for solution. Read about Command timeout property of SQL. But still issue is same. When I m running stored procedure through SQL server it is running fine but when calling it through the asp.net code it giving exception.

stack  trace -

>

StackTrace " at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)\r\n
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)\r\n at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()\r\n at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()\r\n at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()\r\n
at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)\r\n at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)\r\n at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)\r\n at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)\r\n at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)\r\n at DataAccessLayer.EspaceDatasetDataContext.ETL_IMPORT_09_01(Nullable1 userId, String clientMachineIP, String loadType, Nullable1 instId, Nullable1 bIsIgnoreErrors, Nullable`1& rcout)

stored procedure calling method:-

public int? RunDTSxProcess(long userID, string clientMachineIP, string loadType, long instID, bool ignoreErrors)
        {
            try
            {
                int? result = 0;
                var run = database.storedproc(userID, clientMachineIP, loadType, instID, ignoreErrors,ref result);
                return result;
            }
            catch (Exception)
            {

                throw;
            }
        }
  • What is the code calling this and how are you setting your CommandTimeout, could you show both bit of code ? How long is it taking to throw this exception? – Mr_road Mar 01 '18 at 12:08
  • I read about CommandTimeout property but as I am using linq to sql class i didnt set that property. –  Mar 01 '18 at 12:11
  • after near to 1min it throwing exception –  Mar 01 '18 at 12:12
  • i mentioned method where i am calling my stored procedure –  Mar 01 '18 at 12:22
  • How about setting command timeout in ssis – TheGameiswar Mar 01 '18 at 12:32
  • I didnt set any command timeout in ssis also not connection timeout . It is default. It is possiblr to set command timeout in LINQ To SQL class file –  Mar 01 '18 at 12:44
  • when I am executing stored procedure from sql server it is taking 12:42 min for complete execution. –  Mar 01 '18 at 13:59
  • is database your MainContext variable? – Mr_road Mar 01 '18 at 14:00

1 Answers1

0

I think you may be looking to do something with MainContext for your linq to sql as detailed in this answer.

taken from the above answer: 

using (MainContext db = new MainContext())
{
    db.CommandTimeout = 3 * 60; // 3 Mins
}
Mr_road
  • 554
  • 6
  • 25
  • can i set the timeout inside of my method which i mentioned above –  Mar 01 '18 at 15:00
  • I set CommandTimeout=13*60 in my method but after this timeout it still showing me the same exception –  Mar 02 '18 at 07:12