adminorama (9/11/2012)
Hi everyoneI have to populate a table with a pretty large text file and I'm happily using the very good BULK INSERT statement and this "import" procedure is one of many I have on my DB.
A series of condition must be met before the parent procedure starts to call all the child procedures and one is (obviously) to check if all needed files are in the import directory.
From time to time the copy of this big file takes more than usual and the procedure starts with this file still in use by the system (still being copied to the destination directory).
Is there any technique that I can use to detect if the file is still in use without generating the error and delay the parent import start?
I'm using SQL Server 2008
Thanks a lot in advance
Yes. Set a flag.
Let the process that generates the offending file create a second - even empty - file after it completes creating the file your process need then your process has to check for the existence of the flag-file, if not there sleep for a couple of minutes and try again, if there, do whatever has to be done and delete the flag-file.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.