DSXchange

I have a stored proc that we call for every row and the procedure writes to staging table for all the matching records. One particular row has about 365k matching records and its taking 40 min to write to staging table, the problem we have is we dont get any response back after some time.

its working fine when the query doesnt take long time to fetch rows, is there a timeout limit on the stored proc so it just freezes afterwards,

and the job becomes dead. we need to manually kill it. any ideas??

Only the obvious one - don't call a stored procedure for every row. In addition to being slow, and depending upon what the stored procedure actually does, you risk the possibility of self-deadlock.