September 11, 2015 at 8:08 am
Hi everyone,
I try to send a backup with ftp, and write the result to a text file, when i do on command prompt i get the right result, but when i use t sql, the request write the config file on my text file as:
command prompt:
c:>ftp -n -s:C:\BI\FTP_CONFIG.txt >> C:\BI\backup_V15\result.txt
Result:
C:\>ftp -n -s:C:\BI\FTP_CONFIG.txt
ftp> open server
Connected to server.
220 FTP Server ready.
ftp> user acv
331 Password required for acv_bi
230 User acv_bi logged in.
ftp> lcd C:\BI\backup_V15
Local directory now C:\BI\backup_V15.
ftp> cd uploads
250 CWD command successful
ftp> binary
200 Type set to I
ftp> put backup_V15.bak
200 PORT command successful
150 Opening BINARY mode data connection for backup_V15.bak
226 Transfer complete
ftp: 86496256 bytes sent in 2.08Seconds 41624.76Kbytes/sec.
ftp> disconnect
221 Goodbye.
ftp> bye
T-SQL:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
SELECT @cmdline = 'ftp -n -s:C:\BI\FTP_CONFIG.txt >> C:\BI\backup_V15\result'+'_'+@DateTime+'.txt'
EXEC master.dbo.xp_cmdshell @cmdline
EXEC sp_configure 'show advanced options', 0
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
result:
open server
user acv_bi
lcd C:\BI\backup_V15
cd uploads
binary
put backup_V15.bak
disconnect
bye
i need a help please.
Thank you
September 11, 2015 at 10:21 am
Is the the output from a step or from a query window?
September 11, 2015 at 10:26 am
from a step of a sql job
September 11, 2015 at 11:29 am
What is output from that job step? You will probably have to look at the job history to find out.
September 11, 2015 at 11:35 am
i have something like that in my job history.
Date9/11/2015 5:00:00 AM
LogJob History (JB_BACKUP_DB)
Step ID0
ServerTTS-BI
Job NameJB_BACKUP_DB
Step Name(Job outcome)
Duration00:20:05
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
The job succeeded. The Job was invoked by Schedule 19 (SCH_BACKUP_DB). The last step to run was step 1 (ST_BACKU_DB).
---------------------------------------------------------------------------------------------------------------------------------------------------------
i need to have this result in my result file text when i execute in the job, i got that when i execute in command prompt dos:
Result:
C:\>ftp -n -s:C:\BI\FTP_CONFIG.txt
ftp> open server
Connected to server.
220 FTP Server ready.
ftp> user acv
331 Password required for acv_bi
230 User acv_bi logged in.
ftp> lcd C:\BI\backup_V15
Local directory now C:\BI\backup_V15.
ftp> cd uploads
250 CWD command successful
ftp> binary
200 Type set to I
ftp> put backup_V15.bak
200 PORT command successful
150 Opening BINARY mode data connection for backup_V15.bak
226 Transfer complete
ftp: 86496256 bytes sent in 2.08Seconds 41624.76Kbytes/sec.
ftp> disconnect
221 Goodbye.
ftp> bye
September 11, 2015 at 12:02 pm
do you have a proxy setup on this server for cmd_shell?
September 11, 2015 at 12:05 pm
no proxy server, the same command work well on dos.
September 11, 2015 at 12:16 pm
The account you use for the command prompt isn't the same one used in the job. Sometimes cmd_shell hides the error message you are looking for. have you tried to run the job in a 'Operating System' step?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply