March 9, 2010 at 2:52 pm
I have a stored procedure that inserts data from a source database to a destination database for migration purposes using dynamic sql passed thru to sp_executesql. Each table insert is a separate string that is passed so each table is inserted in a separate sp_executesql call and not all at once. The Stored Proc is executed thru a C# Application. This has been in production for months without any problems although no large migrations have been done yet. Now there is an issue when migrating a large client where the stored proc itself appears to have completed but not all of the sp_executesql calls have been made. It seems to be hanging on one insert specifically and after a period of 45 minutes or so quits. I ran a trace on this and the trace shows the sp completes successfully but in reality there are still 5 more inserts to be done. The insert the sp seems to hang on does not actually insert anything either although I know for a fact well over 100000 rows should be there. It is as if sp_executesql quits and the sp calling it quits as well, without throwing an error. There are no udf's or quirky sql being used except that the insert utilizes three selects using a Union. Does anyone have any ideas about what could cause this?
March 11, 2010 at 8:18 pm
Am I misreading the problem? Are you sending more than 100000 insert statements as one variable into sp_executesql? One of them is hanging the process and therefore the entire group isn't committing?
How long is the whole string?
I'd be curious as to the business process that required that particular solution. Can you break it into smaller segments to isolate the offending insert?
March 12, 2010 at 8:48 am
I think you've misunderstood. This sp has 25 separate inserts that are all executed individually by setting the Query to a string and executing sp_executesql. Each insert is set to a string, executed and then the next insert is set to a string, executed.... until all of them are done. The problem I'm having is that when the sp gets to the 21st insert and executes it dynamically, it appears to just hang and never completes the insert. The result is that the sp completes without any error, but without inserting anything for the 21st insert or executing the remaining 4 inserts. There is no conditional statements that would cause this. The query that the sp hangs on is expected to take the longest to complete, but it nevers appears to complete at all.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply