0

I am running a stored procedure against a SQL server database during an insert operation. When I attempt to insert multiple rows within a loop, I get the following error when the stored procedure is subsequently executed:

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out

On One attempt, the exception was not thrown, but a null value was inserted into the column whereas it should have been an integer generated by the stored procedure.

Here is my stored procedure:

ALTER procedure [dbo].[CreateOrRetrieveDataScope] (@dataScopeTypeId int ,@clientId nvarchar(8) ,@areaId int = NULL, @unitId bigint = NULL ,@wheelPosition int = NULL) with recompile as

declare @DataScopeId int

Select_DataScopeId:
    set @DataScopeId = (select Id from DataScopes 
    where DataScopeTypeId = @dataScopeTypeId 
        and ClientId = @clientId 
        and (AreaId is null or (AreaId = @areaId))
        and (UnitId is null or (UnitId = @unitId))
        and (WheelPosition is null or (WheelPosition = @wheelPosition)))

if @DataScopeId is null begin
    begin try
        insert into DataScopes(DataScopeTypeId, ClientId, AreaId, UnitId, WheelPosition) values (@dataScopeTypeId, @clientId, @areaId, @unitId, @wheelPosition);
        goto Select_DataScopeId         
    end try
    begin catch
        goto Select_DataScopeId
    end catch
end 

select @DataScopeId

Basically, here we attempt to create a data scope id value if it doesn't exist. If it already exists we just return it from the proc. I tested the code for two (almost) simultaneous inserts to ensure that there won't be duplicates. The second client should always receive the value if it has been inserted.

And here is the line of code in my windows service that executes it:

newJob.DataScopeId = db.CreateOrRetrieveDataScope(1, areaMap.Area.ClientID, areaMap.AreaID, null, null).FirstOrDefault().Value;

The stored proc is executed for multiple rows in a table in the database. Essentially, new jobs are created from rows in another table.

So far I have tried watching the activity monitor as it executes the operation, but I couldn't glean anything exceptional there. I also tried to set the number of connections to 0 (unlimited) in SQL server management studio.

EDIT: Taking another look at the activity monitor, it appears that my stored procedure is suspended.

Andre Lashley
  • 275
  • 4
  • 18
  • 1
    the exception means your proc needed more than 30 sec to execute (if i remember the timespan correctly) – A ツ Apr 01 '15 at 21:49
  • Got it, so perhaps I should try increasing the timeout on the db context temporarily? If it makes any difference, "select" is the command in the activity monitor next to the suspended status. – Andre Lashley Apr 01 '15 at 21:51
  • i know how to set it on a sql command but have no idea how to change it in ef – A ツ Apr 01 '15 at 21:53
  • The answer to this question shows an example: http://stackoverflow.com/questions/6232633/entity-framework-timeouts – Andre Lashley Apr 01 '15 at 21:56
  • I'll give that a shot and then update this post. – Andre Lashley Apr 01 '15 at 21:56
  • 1
    As a side note / shot in the dark, the CATCH block wrapping the INSERT command will trigger an infinite loop if the INSERT fails. The catch will send the sproc to the beginning, which will try to insert again, which will fail again, sending it back to the beginning, try to insert, fails, back to beginning... – Guest Apr 01 '15 at 22:01
  • 1
    as another side note, i'd debug that proc bec if you hit the timeout it runs too long (whatever it does) – A ツ Apr 01 '15 at 22:12
  • Debugging the proc showed that the insert was failing due to an invalid parameter, I've fixed the call and to ensure I don't enter an infinite loop condition I'll handle the error and break out of the proc. Thanks guys! – Andre Lashley Apr 01 '15 at 22:39

0 Answers0