(SQL Server) SFTP Debug Log File Path

Demonstrates how to use the DebugLogFilePath property to get information if a Chilkat method crashes or hangs.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sftp int
    -- Use "Chilkat_9_5_0.SFtp" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.SFtp', @sftp OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Insert this code just before the method call that crashes or hangs.
    EXEC sp_OASetProperty @sftp, 'VerboseLogging', 1
    EXEC sp_OASetProperty @sftp, 'DebugLogFilePath', '/some/file/path/sftp_debug_log.txt'

    -- Make the call that crashes or hangs,
    -- for example, if the method is DownloadFileByName:

    DECLARE @remoteFilePath nvarchar(4000)
    SELECT @remoteFilePath = '...'
    DECLARE @localFilePath nvarchar(4000)
    SELECT @localFilePath = '...'
    DECLARE @success int
    EXEC sp_OAMethod @sftp, 'DownloadFileByName', @success OUT, @remoteFilePath, @localFilePath

    -- Note: If the method returns control to your application code, then it did not crash within Chilkat.
    -- The DebugLogFilePath property causes all information that would be recorded in the LastErrorText to be emitted to the
    -- log file (with immediate file write flushing, i.e. no buffering).  Thus, in a crash situation,
    -- the log file will contain information up to the point of the crash.

    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    EXEC @hr = sp_OADestroy @sftp


END
GO