Locked File Issues In SQL 2000/2005

Posted on 2009-12-18
Last Modified: 2013-12-12

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?
Question by:bnrtech
    LVL 4

    Expert Comment

    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.
    LVL 4

    Expert Comment

    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.

    Author Comment

    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?

    Accepted Solution

    I have moved the file creation from a stored procedure into an SSIS package, that solved the problem.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    A list of useful business intelligence software.
    The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
    The viewer will learn how to create multiple layers to apply various filters and how to delete areas from each layer’s filter.
    After watching the Introduction to GIMP this tutorial will show you additional tools to use in GIMP.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now