Wednesday, March 21, 2012

How can I always close a text file from a CLR Stored Procedure even when the process is killed?

I have a C# SQL 2005 .net stored procedure which scrubs a text file looking for characters not in a range of characters and replacing them with another character. This works fine except when the process is killed. When this happens the file handle of the file being scrubbed is not released. I use a try catch finally block when opening the file and the output file. The finally section fiushes the output file and closes all files and streams but still when I go to access the file again or use the file in explorer it says the file is still in use. Should I be handling this some other way? How do I know the files will always be closed correctly.

This behavior could happen because that when you kill the process, your finally section may not be called. Eventually, the file handle will be released when garbage collector kicks in.

No comments:

Post a Comment