• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Locked File Issues In SQL 2000/2005

Hello,

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?
0
bnrtech
Asked:
bnrtech
  • 2
  • 2
1 Solution
 
LedigimateCommented:
If you know a .NET programming language, you can try writing CLR functions that accomplish the tasks performed by the stored procedures.  You may then be able to implement a waiting mechanism that will wait until the file is finished created and populated.  The CLR function you create should then be compiled into a .dll and you should then register its essembly with SQL Server, after which you can call the CLR funtion just like any other stored procedure within your T-SQL code.  The only reason I'm suggesting this is because you may then be able to execute your code in a more synchronized way.
0
 
LedigimateCommented:
I've spotted a few typos in my previous comment, but I trust you understand what I meant.  I've had English as a second language in high school.
0
 
bnrtechAuthor Commented:
Hi Ledigimate,

Thanks for your suggestion, but I am looking for something more along the lines of forcing sql to release file locks. Would you have any suggestions of how to accomplish something like that?
0
 
bnrtechAuthor Commented:
I have moved the file creation from a stored procedure into an SSIS package, that solved the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now