I have created a stored procudure SP2 which creates the csv file. This store procedure is called within another stored procedure SP1 but when i run stored procedure SP1 which contains execute statement to SP2 from .net application, My table gets locked and i get a deadlock. the csv file is created but is locked and and it doesn't write anything to it and sits there. Everytime this happens, we have to restart the server. i get the following error when i run the query to see what has happened:
BlockingSessionID VictimSessionID BlockingQuery VictimQuery
82 96 xp_cmdshell SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM ##csvtemp
WaitDurationSecond WaitType BlockingQueryCompletePercent
1154 LCK_M_SCH_S 0
Don't know how to resolve this issue.