Archived Forum Post

Index of archived forum posts

Question:

SFTP in Microsoft SQL Server, files locked and hanging

Nov 17 '16 at 08:14

Hi, We have been using Chilkat.SFTP inside of a Microsoft SQL Server Stored Procedure for years. It connects to a secure FTP site, uploads a file, then scrolls through some directories and pulls down any files that are out there for us. We don't own or control the SFTP server.

In the last couple months, the job is getting stuck (I don't know how else to put it). For example, today I found 86 files had been downloaded through the job, but when I checked, the job was still running. There was a 0-kb file locked open on my local operating system which said it was being held by SQL Server.

It takes re-booting the local machine in order to clear the file lock to be able to delete it. The calls to the Chilkat API aren't reporting any errors. It's like the file gets opened locally for writing and the whole process grinds to a halt.

I'm looking for suggestions on ideas for my Stored Procedure code to somehow know something went wrong and to release the file lock?

Here's the Cliff's notes of our current process:

sp_OACreate 'Chilkat.SFtp', @sftp OUT
EXEC sp_OASetProperty @sftp, 'ConnectTimeoutMs', 5000
EXEC sp_OASetProperty @sftp, 'IdleTimeoutMs', 10000
EXEC sp_OAMethod @sftp, 'Connect', @success OUT, @hostname, @port
EXEC sp_OAMethod @sftp, 'AuthenticatePw', @success OUT, @as_user, @as_password
EXEC sp_OAMethod @sftp, 'InitializeSftp', @success OUT
... skip the uploading part, no issues there
EXEC sp_OAMethod @sftp, 'OpenDir', @handle OUT, @as_directory
EXEC sp_OAMethod @sftp, 'ReadDir', @dirListing OUT, @handle
EXEC sp_OAGetProperty @dirListing, 'NumFilesAndDirs', @n OUT
... loop through @n
EXEC sp_OAMethod @dirListing, 'GetFileObject', @fileObj OUT, @I
EXEC sp_OAGetProperty @fileObj, 'Filename', @sTmp0 OUT
EXEC sp_OAMethod @sftp, 'OpenFile', @filehandle OUT, @tmp, 'readOnly', 'openExisting'
EXEC sp_OAMethod @sftp, 'DownloadFile', @success OUT, @filehandle, @fullDownloadFile
EXEC sp_OAMethod @sftp, 'CloseHandle', @success OUT, @filehandle
EXEC sp_OAMethod @sftp, 'RemoveFile', @success OUT, @tmp
... end loop through @n
EXEC sp_OAMethod @sftp, 'CloseHandle', @success OUT, @handle

Any thoughts?

Thanks!

---

Answer

The first thing to do is to make sure you're using the very latest version of Chilkat.

If the problem remains, turn on verbose logging via the VerboseLogging property, and set the DebugLogFilePath property to the name of a log file that would be created by Chilkat. (It must be a path with directory permissions for your SQL Server process to create the file on the local filesystem.) Then re-run and examine the log file that gets created once it hangs.

---

Answer

Hi. I added the verbose logging and the debug file. After an hour, I have got a 200Mb file and growing with no way to stop it. I am getting a repeated section (below). The only difference between each section that I can see is "packetLen" changes, one value in "payload" (example below first one is EF9E, second is EF9F), and "m_curServerWinSize". The file size on the local machine is still 0 kb.

readChannelData:
    clientChannelNum: 0
    totalTimeoutMs: 0
    bCheckQueue: 1
    bOnlyExtendedData: 0
    bSkipExtendedData: 1
    idleTimeoutMs_1: 0
    calledFrom: 103
    bReadOneRaw: 0
    (leaveContext)
Decrypting first block of message..
packetLen: 188
mType: CHANNEL_DATA
payloadMsgType: 94
payloadLen: 41
payload: 5E00 0000 0000 0000 2000 0000 1C65 0001

EF9E 0000 0004 0000 000B 456E 6420 6F66 2046 696C 6500 0000 00 packetType: SSH_FXP_STATUS StatusResponse: Request: SSH_FXP_READ StatusCode: 4 StatusMessage: End of File (leaveContext) Unexpected status response. sendFxpRead: sendPacket: packetType: SSH_FXP_READ m_curServerWinSize: 273675 sendMessage: msgName: CHANNEL_DATA unpaddedLength: 41 remainder: 9 paddingLen: 7 totalSize: 48 (leaveContext) (leaveContext) (leaveContext)

readChannelData:
    clientChannelNum: 0
    totalTimeoutMs: 0
    bCheckQueue: 1
    bOnlyExtendedData: 0
    bSkipExtendedData: 1
    idleTimeoutMs_1: 0
    calledFrom: 103
    bReadOneRaw: 0
    (leaveContext)
Decrypting first block of message..
packetLen: 92
mType: CHANNEL_DATA
payloadMsgType: 94
payloadLen: 41
payload: 5E00 0000 0000 0000 2000 0000 1C65 0001

EF9F 0000 0004 0000 000B 456E 6420 6F66 2046 696C 6500 0000 00 packetType: SSH_FXP_STATUS StatusResponse: Request: SSH_FXP_READ StatusCode: 4 StatusMessage: End of File (leaveContext) Unexpected status response. sendFxpRead: sendPacket: packetType: SSH_FXP_READ m_curServerWinSize: 273648 sendMessage: msgName: CHANNEL_DATA unpaddedLength: 41 remainder: 9 paddingLen: 7 totalSize: 48 (leaveContext) (leaveContext) (leaveContext)