problem when trying to insert a polygon in SQL Server table with MapInfo Easyloader

Question

Hi All,

I'm using SQL Server 2008 R2 SP1 and the latest version of MapInfo (11.0.4). 

With a tool called EasyLoader I'm trying to insert a polygon in a table of SQL Server that contains an insert trigger. The trigger is doing a quite long geographic query finding and saving in a new table all the points from an existent table that fall inside the polygon inserted.

Everything was working fine till 1 month ago, then it stopped working without any apparent reason.

What's happening now is that EasyLoader can insert the polygon if there is no trigger or if the code inside the trigger is a fast query (such as update a field), but when the query starts to involve a long process, like the geographic one described before, after about 30-45 seconds the program stops saying that the inserting was successful when instead is not.

If I'm inserting the polygon from another table using only SQL Server management studio, the trigger works smoothly, so it's not a problem of the coding, but it seems almost that a sort of timeout during the query kicks EasyLoader out.

Any idea?

Thanks,

Elisa

Answer 1

Very hard to say without seeing either the INSERT queries that are being executed or the logic of your trigger, but it sounds perfectly possible that the EasyLoader app has a timeout that assumes that a statement will have completed within a certain number of seconds - 30-45 seconds is a very long time to insert one row of data! I'm not sure why, in itself, this would cause the insert to be rolled back though - can you run a trace or look at the system DMVs to see what's happening?

As for possible solutions (short of tuning your trigger)... does the long-running query really have to be done in a trigger at the point the row is first inserted? Could it be called asynchronously (perhaps using Service Broker), or as a scheduled batch operation?

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 2

Thanks for the reply!

I haven't post the code of the trigger because the main problem is that the entire process was working just fine about 1 month ago and then it just stopped without any apparent reason. The code was written by a SQL Server expert and it has been successfully tested several time.

Only things changed in the meantime on the Server have been the usual automatic windows updates. Very weird.

I'm quite willing to try and run it asynchronously , but I need the system to go and retrieve the data automatically after an user has inserted the polygon, and not being an expert of SQL Server, I'm not sure how to handle that.

Any help will be much appreciated!

Thanks,

Elisa