I am writing a stored procedure that exports data to Excel. It uses a series of EXEC statements to run other stored procedures. The export happens in this order.
1)the excel file is created on the sql server
2)the data is inserted into the excel file
3)the file is moved to a location on the network.
However the procedure always fails to perform step 3 with the error "The process cannot access the file because it is being used by another process.". Step 3 is just a move command being issued using xp_cmdshell.
I have even tried using a copy command instead. The file is successfully copied. But it is left in a corrupted state. I believe the move/copy is happening before the file is finished being created and populated.
Does anyone have any ideas on how to get around this issue?