Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136

    Unanswered: xp_cmdshell help

    Hi,

    I need to do FTP using a FTP script...here is the syntax that i am using

    ftp -s:c:\temp\ftpcmd.txt > c:\temp\log.txt

    when i run it thru the command line, log.txt file has the following
    ==================================================
    ftp> Connected to 9.999.99.999.

    open 9.999.99.999
    220-YYYY1 XXX FTP CS V1R2 at XXXXXX.XXXXXX.XXX.COM, 16:55:07 on 2004-06-15.
    220 Connection will close if idle for more than 5 minutes.
    User (9.999.99.999none)):
    331 Send password please.

    230 USERNAME is logged on. Working directory is "USERNAME.".
    ftp> Invalid command.

    ftp>
    ascii
    200 Representation type is Ascii NonPrint
    ftp> get 'XXX.YYY.ZZZ.ACCOUNT' c:\temp\XXX.YYY.ZZZ.ACCOUNT.txt
    200 Port request OK.
    125 Sending data set XXX.YYY.ZZZ.ACCOUNT FIXrecfm 22
    250 Transfer completed successfully.
    ftp: 1172544 bytes received in 3.31Seconds 353.82Kbytes/sec.

    ftp> quit
    221 Quit command received. Goodbye.
    =================================================

    but when i run it from inside a query...using

    declare @cmd varchar(1000)
    select @cmd = 'ftp -s:c:\temp\ftpcmd.txt > c:\temp\log.txt'
    exec master..xp_cmdshell @cmd

    the log.txt file has
    =================================================
    User (3.172.28.153none)): open 3.172.28.153
    Invalid command.




    ascii
    get XXX.YYY.ZZZ.ACCOUNT' c:\temp\XXX.YYY.ZZZ.ACCOUNT.txt
    quit
    =================================================

    why is this differnece? what can i do so that i get full details in the log file.
    please help.

    thanks
    rohit

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    xp_cmsshwll is running under the context of the sql server service account.

    So it's looking at itself, it's dirive, and it can't find ftp.

    Fully qualify the location of ftp...and make sure you're mappings match the server for testing purposes...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136

    Unhappy

    Brett,

    I am doing both the tests at a local machine and have given full path for file names.
    when I am running thru xp_cmdshell the file is getting FTP'd successfully but the log file has very few details. When I run "ftp -s:c:\temp\ftpcmd.txt > c:\temp\log.txt
    " thru the command line, log file comes out complete in all details as pasted below.
    i need all details in the log file when i run it thru xp_cmdshell.
    -Rohit
    Last edited by rohitkumar; 06-15-04 at 14:34.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You're running ON the server itself?

    Can you post the ftp script?

    I usually have a bat file that I execute that contains the ftp command...

    So I use 3 files

    The bat, that calls the ftp execution
    The ftp exec script
    and the actual script file
    Oh and 4 the redirected output log...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    Yes, I am running on the server itself.

    Presently in the production I am using the same bat file process but lately I have been getting problems with that so wanted to run it thru SQL xp_cmdshell.

    here is the script file

    ===================ftpcmd.txt===================== ===
    open 9.999.99.999
    <username>
    <password>
    ascii
    get 'XXX.YYY.ZZZ.ACCOUNT' c:\temp\XXX.YYY.ZZZ.ACCOUNT.txt
    quit
    ==================================================

    -Rohit

  6. #6
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136

    Unhappy

    ==========FTP script file ftpcmd.txt ==================
    open 9.999.99.999
    user <username> <password>
    lcd c:\temp
    ascii
    get 'XXX.YYY.ZZZ.ACCOUNT' c:\temp\XXX.YYY.ZZZ.ACCOUNT.txt
    quit
    ===============================================

    case 1:
    running thru command line at c:\ prompt
    ftp -s:c:\temp\ftpcmd.txt > c:\temp\log.txt

    case 2:
    running thru SQL xp_cmdshell in query analyzer
    exec master..xp_cmdshell 'ftp -s:c:\temp\ftpcmd.txt > c:\temp\log.txt'

    log.txt is different in these. first case has a detailed log. I want the log file in the second case to be as detailed as in the first one.

    note: both FTP cases do FTP the file, but second log does not have "Transfer Completed Successfully".

    Please help

    Thanks
    -Rohit
    Last edited by rohitkumar; 06-15-04 at 16:57.

  7. #7
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    looks like I'll have to look for some other way...

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah,

    No I can see (said the blindman...)

    Had the exact same problem....

    I had to set up a job...and execute that...

    And then used this sproc

    Code:
    CREATE procedure isp_ftp_PutDat
    @FTPServer	varchar(128) ,
    @FTPUser	varchar(128) ,
    @FTPPWD		varchar(128) ,
    @FTPPath	varchar(128) ,
    @SourcePath	varchar(128) ,
    @Retpd 		int,
    @workdir	varchar(128)
    
    AS
    
    --
    --                	Enterprise Solutions
    --
    --           File:      
    --           Date:      
    --         Author:      
    --         Server:      
    --       Database:      PRIVACY
    --          Login:      sa
    --    Description:      This Procedure will put all of the data files up to the mainframe
    --
    -- UserId   	Date        	Description
    -- -----------  --------------  ------------------------------------------------------------------------------------------
    -- x120385  	GetDate 1. Initial release
    --
    --
    
    
    DECLARE @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int, @rc int
    SELECT  @rc = 0
    
    DECLARE DSINFO CURSOR FOR
    SELECT TABLENAME , REPLACE(Dsname,'T','T'), dsreclen, datafilename
    FROM dsinfo
    
    
    DECLARE @TABLENAME varchar(300) , @FTPFileName varchar(8) , @Reclen int , @SourceFile varchar(350), @DATE_TIME datetime
    	,@rownum int , @rownum2 int , @count int
    
    
    OPEN DSINFO
    FETCH NEXT FROM DSINFO INTO @TABLENAME, @FTPFileName, @Reclen, @SourceFile
    SET @SourceFile = @FTPFileName +'.txt'
    declare	@cmd varchar(1000)
    declare @workfilename varchar(128)
    	
    	select @workfilename = '\ftpcmd' + @FTPFileName + '.txt'
    SELECT @CMD = 'ECHO ftp -s:' + @workdir + @workfilename + '        ^> ' + @workdir +'\' + @FTPFileName + '.txt' +  ' > ' + @workdir + '\ftpout.bat'
    EXEC master..xp_cmdshell @CMD 
    
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SourceFile = @FTPFileName +'.dat'
    /*
    declare	@cmd varchar(1000)
    declare @workfilename varchar(128)
    	
    	select @workfilename = 'ftpcmd.txt'
    */
    select @workfilename = '\ftpcmd' + @FTPFileName + '.txt'
    	-- deal with special characters for echo commands
    	select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
    	select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
    	select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
    	select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'<','^<'),'>','^>')
    	
    	
    	select	@cmd = 'echo '					+ 'open ' + @FTPServer
    			+ ' > ' + @workdir + @workfilename
    	exec master..xp_cmdshell @cmd
    	select	@cmd = 'echo '                   		+ @FTPUser
    			+ '>> ' + @workdir + @workfilename
    	exec master..xp_cmdshell @cmd
    	select	@cmd = 'echo '					+ @FTPPWD
    			+ '>> ' + @workdir + @workfilename
    	exec master..xp_cmdshell @cmd
    	Select	@cmd = 'echo delete '			+ '''' + @FTPPath +'.'+ @FTPFileName + ''''
    			+ '>> ' + @workdir + @workfilename
    	exec master..xp_cmdshell @cmd
    	select	@cmd = 'echo quote site cyl pri=5 sec=2 lrecl='+ CONVERT(varchar(8),@Reclen) + ' blksize=0 recfm=fb retpd=' + CONVERT(varchar(8),@Retpd)
    			+ '>> ' + @workdir + @workfilename
    	exec master..xp_cmdshell @cmd
    	select	@cmd = 'echo '					+ 'put ' + @SourcePath + '\' +  @SourceFile + ' ' + '''' + @FTPPath + '.' + @FTPFileName + ''''
    			+ ' >> ' + @workdir + @workfilename
    	exec master..xp_cmdshell @cmd
    	select	@cmd = 'echo '					+ 'quit'
    			+ ' >> ' + @workdir + @workfilename
    	exec master..xp_cmdshell @cmd
    	
    --	select @cmd = 'ftp -s:' + @workdir + @workfilename
    
    SELECT @CMD = 'ECHO ftp -s:' + @workdir + @workfilename + '        ^> ' + @workdir +'\' + @FTPFileName + '.txt' +  ' > ' + @workdir + '\ftpout.bat'
    --print @cmd
    		EXEC master..xp_cmdshell @CMD 
    
    SET @rownum = 0
    SET @rownum2 = 0
    set @rownum = (select count(*) from msdb.dbo.sysjobhistory where step_name = 'CallDATBAT')
    
    exec msdb..sp_start_job @job_name = 'FTP_UP_OHM', @step_name = 'CallDatBAT'
    set @count =  1 
    WHILE @count = 1
     BEGIN
    SET @rownum2 = (select count(*) from msdb.dbo.sysjobhistory where step_name = 'CallDatBAT')
    IF @rownum = @rownum2
     CONTINUE
    ELSE
    
    BREAK
    END
    
    
    SET @DATE_TIME = GETDATE()
    SELECT @cmd = 'bcp "Privacy.[dbo].[Dat_up_log]" in ' + @workdir +'\' + @FTPFileName + '.txt -T' +
    	      ' -S' + @@SERVERNAME + ' -f ' + @workdir +'\Dat_log_In.fmt' 
    
    EXEC master..xp_cmdshell @CMD
      		
    		IF EXISTS (SELECT log_message from [dbo].[Dat_up_log]
    			   WHERE log_message like '%successfully%'
    				AND TABLENAME IS Null AND DATE_TIME IS NULL ) 
    		BEGIN
    		
    		UPDATE [dbo].[Dat_up_log] 
    		SET TABLENAME = @TABLENAME
    		WHERE log_message IS NOT NULL and TABLENAME IS Null
    
    		UPDATE [dbo].[Dat_up_log] 
    		SET DATE_TIME = @DATE_TIME
    		WHERE log_message IS NOT NULL AND DATE_TIME IS NULL
    
    	END
    	ELSE
    	BEGIN
    	UPDATE [dbo].[Dat_up_log] 
    	SET log_message = 'The ftp failed for table' + @TABLENAME	
    	WHERE log_message IS NOT Null AND TABLENAME IS Null AND DATE_TIME IS NULL
    
    	UPDATE [dbo].[Dat_up_log] 
    		SET TABLENAME = @TABLENAME
    		WHERE log_message IS NOT NULL AND TABLENAME IS Null
    
    		UPDATE [dbo].[Dat_up_log] 
    		SET DATE_TIME = @DATE_TIME
    		WHERE log_message IS NOT NULL AND DATE_TIME IS NULL
    
    SELECT @Error_Loc = 1
    			     , @Error_Message =  'Dat FTP failed for Table ' + @TABLENAME
    			     , @Error_Type = 50002
    			     , @rc = -1
    		GOTO isp_ftp_PutDat_Error
    	END
    
    FETCH NEXT FROM DSINFO INTO @TABLENAME, @FTPFileName, @Reclen, @SourceFile
    
    End
    isp_ftp_PutDat_Exit:
    CLOSE DSINFO
    DEALLOCATE DSINFO
    RETURN @rc	
    
    isp_ftp_PutDat_Error:
    If @Error_Type = 50002
    
    	BEGIN
    		Select @Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc)) 
    			                + ',"' + ' Severity:  UserLevel ' 
    				          + ',"' + ' Message: ' + ',"' + RTrim(@Error_Message)
    	END
    
    RAISERROR @Error_Type @Error_Message
    
    GOTO isp_ftp_PutDat_Exit
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    got it...
    setting up a job seems to be a solution...
    Thank you very much Brett.

    -Rohit

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well I don't know if it's the only one...it's the only one I could figure out....

    Good Luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •