Home Can OLE SQL write to FTP?
Reply: 0

Can OLE SQL write to FTP?

user8952
1#
user8952 Published in August 15, 2018, 5:38 am

My simplified goal here is to create a stored procedure that can write a string to an FTP file, and I would prefer to use the T-SQL sp_OA... stored procedures to do this rather than xp_cmdshell if possible.

I have working code that I copied from elsewhere that will first create/open the file using sp_OAMethod with the 'OpenTextFile', then sp_OAMethod 'Write' to write my string to it. Then I have a separate bit of T-SQL code that I copied from online that generates a bat file to perform an ftp command line operation on the file and executes it.

I'm writing this script for another group of users, and I know they will be hesitant to enable xp command shell on their server, and I'm not sure if they will be able to allow writing to a temporary directory as this code requires. Also, I rather like how slick the sp_OAMethod functions are but I can't find a good reference guide on what functions are available or how they work.

Is there any way to combine these two steps into one and possibly eliminate the temp directory using only sp_OA functions?

Here is some working code that writes a string to the HDD:

CREATE PROCEDURE spWriteStringToFile (@String VARCHAR(MAX), @FileAndPath VARCHAR(500))
AS
DECLARE @objFileSystem INT,
        @objTextStream INT,
        @objErrorObject INT,
        @strErrorMessage VARCHAR(1000),
        @Command VARCHAR(1000),
        @hr INT

SET NOCOUNT ON

SET @strErrorMessage = 'opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT

IF @HR=0 SELECT @objErrorObject = @objFileSystem , @strErrorMessage = 'Creating file "' + @FileAndPath + '"'
IF @HR=0 EXECUTE @hr = sp_OAMethod @objFileSystem, 'OpenTextFile', @objTextStream OUT, @FileAndPath, 8, True

IF @HR=0 SELECT @objErrorObject = @objTextStream, @strErrorMessage = 'writing to the file "' + @FileAndPath + '"'
IF @HR=0 EXECUTE @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String

IF @HR=0 SELECT @objErrorObject=@objTextStream, @strErrorMessage='closing the file "' + @FileAndPath + '"'
IF @HR=0 EXECUTE @hr = sp_OAMethod  @objTextStream, 'Close'

IF @hr<>0
    BEGIN
    DECLARE 
        @Source VARCHAR(255),
        @Description VARCHAR(255),
        @Helpfile VARCHAR(255),
        @HelpID INT

    EXECUTE sp_OAGetErrorInfo  @objErrorObject, @source output,@Description output,@Helpfile output,@HelpID output
    SET @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage,'doing something') + ', ' + COALESCE(@Description,'')
    RAISERROR (@strErrorMessage,16,1)
    END
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objFileSystem

And working code that moves a file from the HDD to a specific vendor folder on my company's FTP site:

CREATE PROCEDURE [dbo].[up_FTPPushFile]
    @file_to_push VARCHAR(255), 
    @ftp_to_server VARCHAR(255),
    @ftp_login VARCHAR(255), 
    @ftp_pwd VARCHAR(255) 
as
Set Nocount On
--STEP 0
--Ensure we can find the file we want to send.
Create table #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
Insert #FileExists EXEC master.dbo.xp_fileexist @file_to_push

IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
 Drop table #FileExists
 RAISERROR ('File %s does not exist. FTP process aborted.', 16, 1, @file_to_push)
 RETURN 1
END  
--STEP 1
--Create xxx.bat batch file using bcp utility, file path/name is the same as @file_to_push
--batch file will hold 4 records:
--1) login
--2) password
--3) ftp command and file to push
--4) exit command

DECLARE @WriteString NVARCHAR(MAX)

SET @WriteString = @ftp_login + N'
' + @ftp_pwd + N'
cd /VendorFolder/OutFolder
put ' + @file_to_push + N'
bye
del ' + @file_to_push


declare @sql VARCHAR(255), @cmd VARCHAR(255), @batch_ftp VARCHAR(255), @ret int
set @batch_ftp = Left(@file_to_push, Len(@file_to_push)-4) +'.bat'
EXECUTE spWriteStringToFile @WriteString, @batch_ftp

--STEP 2
--Ensure we can find the batch file we just created.
Delete #FileExists
Insert #FileExists EXEC master.dbo.xp_fileexist @batch_ftp
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
 Drop table #FileExists
 RAISERROR ('Unable to create FTP batch file %s. FTP process aborted.', 16, 1, @batch_ftp)
 RETURN 1
END  
Drop table #FileExists

--STEP 3
--Execute newly created .bat file, save results of execution
Create table #temp_ftp_results (ftp_output VARCHAR(255))
set @cmd = 'ftp -s:'+@batch_ftp+' '+@ftp_to_server
Insert #temp_ftp_results Exec master.dbo.xp_cmdshell @cmd
IF EXISTS (SELECT * FROM #temp_ftp_results WHERE (ftp_output like '%Login failed%' or ftp_output like '%Access is denied%'))
BEGIN
 Drop table #temp_ftp_results
 RAISERROR ('Unable to FTP file %s. Login failed or access denied. FTP process aborted.', 16, 1, @file_to_push)
 RETURN 1
END  

--STEP 4
--delete batch file
set @cmd = 'del ' + @batch_ftp
INSERT #temp_ftp_results EXEC master.dbo.xp_cmdshell @cmd
set @cmd = 'del ' + @file_to_push
INSERT #temp_ftp_results EXEC master.dbo.xp_cmdshell @cmd

Drop table #temp_ftp_results

And just for fun, my implementation that takes a generic SQL table, and will put a file on an FTP site every time a row is added or updated:

CREATE TRIGGER dbo.<TableName>Change    --TODO: Fill out table name here
   ON  dbo.<TableName>                  --TODO: Fill out table name here
   AFTER INSERT, UPDATE
AS 
BEGIN
    --Insert code here to separate our data data from other customer data if applicable
    --IF (SELECT CompanyName FROM INSERTED) = 'My Company'
    --BEGIN

        --Temporary Directory on SQL server to store files before transfer
        DECLARE @TempDirectory VARCHAR(MAX) = 'C:\Temp\'

        --FTP site credentials
        DECLARE @FTPServer VARCHAR(MAX) = 'FTPServer.com'
        DECLARE @FTPUsername VARCHAR(MAX) = 'Username'
        DECLARE @FTPPassword VARCHAR(MAX) = 'Password'

        --Find the triggered table name
        declare @TableName sysname
        SET @TableName = (SELECT object_name(parent_id) from sys.triggers where object_id = @@PROCID)

        --Find the column names in that table
        DECLARE @Columns TABLE (ColumnName VARCHAR(MAX), ColumnCount DECIMAL(18, 0) IDENTITY(0, 1))

        INSERT INTO @Columns
            SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = @TableName

        --Loop through each of the column names and append the inserted values for each modified row
        DECLARE @ColumnCount DECIMAL(18, 0) = (SELECT COUNT(*) FROM @Columns)
        DECLARE @Counter DECIMAL(18, 0) = 0
        DECLARE @WriteString NVARCHAR(MAX) = ''
        DECLARE @CurrentColumn VARCHAR(MAX)
        DECLARE @SQL NVARCHAR(MAX)
        DECLARE @Params NVARCHAR(MAX)
        DECLARE @TempString VARCHAR(MAX)
        DECLARE @LineString NVARCHAR(MAX) = ''
        DECLARE @CurrentRowIndex DECIMAL(18, 0) = 0
        DECLARE @RowCount DECIMAL(18, 0) = (SELECT COUNT(*) FROM INSERTED)

        SELECT * INTO #INSERTED FROM INSERTED

        --Add an identity column so each row has a unique number on it to loop through
        ALTER TABLE #INSERTED ADD IDX DECIMAL(18, 0) IDENTITY(0, 1)

        WHILE @CurrentRowIndex < @RowCount
        BEGIN
            WHILE @Counter < @ColumnCount
            BEGIN
                SET @CurrentColumn = (SELECT ColumnName FROM @Columns WHERE ColumnCount = @Counter)

                SET @SQL = 'SELECT @WriteStringOUT = ISNULL(CONVERT(VARCHAR(MAX), (SELECT ' + @CurrentColumn + ' FROM #INSERTED WHERE IDX = ' + CONVERT(VARCHAR(MAX), @CurrentRowIndex) + ')), '''')'
                SET @Params = '@WriteStringOUT VARCHAR(MAX) OUTPUT'

                EXECUTE sp_executesql @SQL, @Params, @WriteStringOUT = @TempString OUTPUT

                SET @LineString = @LineString + '|' + @TempString

                SET @Counter = @Counter + 1
            END

            --Eliminate beginning pipe and add a NewLine
            SET @WriteString = @WriteString + RIGHT(@LineString, LEN(@LineString) - 1) + N'
'

            SET @LineString = N''
            SET @Counter = 0
            SET @CurrentRowIndex = @CurrentRowIndex + 1
        END

        --Create file name formatted TableNameYYYYMMDDHHmmSSsss.txt
        DECLARE @FileName VARCHAR(MAX) = @TempDirectory + @TableName + REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(CHAR(23),CONVERT(DATETIME,CURRENT_TIMESTAMP,101),121), ':', ''), '-', ''), ' ', ''), '.', '') + '.txt'

        --Eliminate the ending new line
        SET @WriteString = LEFT(@WriteString, LEN(@WriteString) - 1)

        --Write string to temp file
        EXECUTE spWriteStringToFile @WriteString, @FileName

        --Send temp file to FTP server and delete temp files
        EXECUTE up_FTPPushFile @FileName, @FTPServer, @FTPUsername, @FTPPassword
    --END
END
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.297809 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO