C # LINQ Stored Procedure

Question

I have a C # Windows service that communicates with a SQL Server 2008 database through LINQ. In LINQ, I have several stored procedures. My service basically wakes up every couple of minutes and looks at the database for processing. During processing, a stored procedure is executed for each new record. After processing all the records, another stored procedure is executed. I have a very strange problem. For the first stored procedure (performed with each record), everything works fine. The procedure is called, it functions correctly, and the code continues to pass the call. For the second procedure (start one of all processed entries), the service simply freezes. I do not receive an error message, it is not a failure, it still works, but it never does anything until I restart the service. If I start the procedure manually in SQL Server Management Studio, it runs correctly and terminates. I hope someone has an idea what is going on here.

Inside the loop of each record:

if (Settings.Default.SQLSpatialEnabled) { try { if ((bool) f.sdrFaultType.TripFault) { DataContext.sp_locateFault ((int) f.ID); } } catch (Exception ex) { Logger.Logger.Trace ("Locate fault (" + f.ID + ") exception: " + ex.Message); } } 

After all entries:

 if (Settings.Default.SQLSpatialEnabled) { DataContext.sp_mapFaults (); Logger.Logger.Trace ("Faults Mapped"); } 

"Errors displayed" is never displayed in the log, and everything basically stops.

Answer 1

Several options are possible:

• really use a profiler to make sure your SP is running at all or not;

• if it is executing, it may be a blocking problem or a long processing time (different execution plans between manual and Linq execution?)

• if it is NOT running, add Logger.Logger.Trace immediately before DataContext.sp_mapFaults () to know exactly what you got there and wrap DataContext.sp_mapFaults () in a try-catch to see if any errors are occurring

Answer 2

You can run SQL Server Profiler to view the parameters that are passed to the procedure (provided that there are parameters) and see that it hangs.

Update

Verify that the Settings.Default.SQLSpatialEnabled value is true?

Try extracting all the other code and just see if SP works on its own?

Try wrapping the call in try ... catch, as in a loop. Perhaps they are throwing something and not reporting?

Answer 3

Are you using a static DataContext for all queries? Have you tried using a separate DataContext for each call in the loop?