Error in SQL Server stored procedure, timeout period elapsed

1
votes

I am trying to use a stored procedure in which I am sending single parameter which I sent through ADO.NET C# code.

Now the problem is the stored procedure is getting called from my dev environment and when I deploy it to production I am getting exception of timeout elapse and it never reaches to the stored procedure (I have added logs to check if stored procedure is actually hit and at what stage it causes timeout).

Exception: 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, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ProjectName.ViewModel.ClassViewModel.<>c__DisplayClasse.b__d()

I have no clue what is happening over production server. Please guide me over this issue.

There are a lot of questions for same error message but I am not able to find similar scenario for issue, like stored procedure not getting executed and still it causes timeout error.

I would have included stored procedure but it is very basic stored procedure.

Please help :)

Thanks, Nikhil Chavan.

1
c#sql-serverstored-procedures
try to run stored procedure directly without using your application and then try to find out what is going out wrong by commenting different parts of query mean to say which one is taking long time to run on server - rashfmnb
Stored procedure runs if I execute it from sql server management studio and it hardly takes 3-4 secs. The problem is stored procedure is not executed at all when I am trying to use it with ADO.NET code. - Nikhil Chavan
try using this connection string DataSource=server;InitialCatalog=databaseUserId=username;Password=password;Connect Timeout=0, 0 means for unlimited time out - rashfmnb
@rashfmnb this will just hang the application forever if an attempt is made to connect to the wrong server - Panagiotis Kanavos
Yes, I can't risk on production server.... Sorry I can't set infinite Timeout :) - Nikhil Chavan

1 Answers

1
votes

Have you tried to run your stored procedure in SQLServer and check if it runs successfully?

Have you tried setting the SqlCommand.CommandTimeout Property?

Have you checked your connection string if it is pointing to the correct DB and it is reachable?