Weird Timeout In CLR Stored Procedures That Use FtpWebRequest / Response....

Hi all,

This is a problem for that I lost a weekend to reach to this conclusions:

Here is the scenario:
I need a file downloaded from a FTP server on my SQL 2005 DB Server to process it in several ways. This file is tens of MBs (let's say 50MB). I set up a CLR stored procedure to bring it locally on the machine.

Inside this procedure, call it getFtpFile, I use the FtpWebRequest to issue the commands I need to the FTP server (get the file size and download the file). For reading and writing I use a StreamReader sr and a StreamWriter. sw

I read the remote file in block with:
sr.ReadBlock(buf, 0, blockSizeBytes);

Everything work file until the last block that, most probably will not be of size blockSizeBytes, but smaller. So, what happens is that when I read this last block there is an exception raised from the call saying:
The underlying connection was closed: An unexpected error occurred on a receive.

This happens only if:
-> the code runs inside SQL (calling the SP) (it runs perfect on normal tet environment where I have another executable and call the getFtpFile method in the CLR dll);
-> 100 seconds (100000 miliseconds) (exactly) have passed (small files do not experiencing this problem)

I thought the reader writer scheme is not perfect and implemented again with a simple FileStream.WriteByte(response.GetResponseStream().Read());
This approach, even if very slow, still has the problem after 100 seconds, in a CLR SQL environment, when it reads the at the last character.

I have used all the combinations of KeepAlive and response.Closose(). Problems still there.

Conclusion: There must be a 100 seconds timeout that immediately after my last byte has been received, closes the connection and the stream of the response crashes during this last call.



Thank you all for the help!