December 4, 2002 at 9:14 am
Hi every one
I have situation here which little confused me..
I have created a ftp new package (dts) for fetch database backup file from main SQL Server to another server for restrore purpose. when I execute the package then i can see the file start transfering on destinated directory but as soon as jobs end this backupfile disappear..
I tried different things such as leave overwrite check box unchecked and execute the package. In this case I have received the file but when execute again and I checked the overwrite check box then file disapppear.
I do not understand why.
another question is i couldn't find the option of if server can ftp any file to another ftp site. I mean send to rather fetch from.
third please let me know (if any done this) if can do ftp using store procedure if can then how.
thnx
swarn
SqlIndia
December 4, 2002 at 1:58 pm
Had to do this in 7 which did not have the FTP option. What we did was script an FTP command line task and the command item in Job to run it. You could potentially use xp_cmdshell to call within TSQL. As for the disappearing file. You are saying you see the file in the folder then when done it disappears right. Have you checked the folders permissions or do you get any failure on the job?
December 5, 2002 at 1:42 am
Hi Antares
em using sql 2000 enterprise edition....
yes I have checked the rights on folder. cos when I fetch the file through third party software (ws_ftppro 7.0) or via browser then file is there rather DTS method.
like I am new in this so, could you please refer to me any article or script or an example so I can manage to get detail understandability of ftp transfer using store procedure.
** yet, I am beginner in advance stuff using sql 2000 **
thnkx
swarn
SqlIndia
December 5, 2002 at 2:30 am
I wanted to do this in SQL7 and produced a DLL used by ActiveX Script in DTS.
Far away is close at hand in the images of elsewhere.
Anon.
December 5, 2002 at 4:00 am
David
plz see following link for sql 6.5 and 7.0.
http://www.sqlteam.com/item.asp?ItemID=6002
I hope it will help you, not for me cos using SQL 2000 Enterprise.
regards
swarn
SqlIndia
December 5, 2002 at 5:30 pm
How did you set yours up as I have no troubles using myself?
December 6, 2002 at 3:10 am
If you're referring to me maybe my reply was confusing! I have already achieved this by downloading existing source from net (to save time) compiled the DLL and wrote a script in DTS to use the DLL to FTP. Works perfectly.
Far away is close at hand in the images of elsewhere.
Anon.
December 6, 2002 at 6:00 am
for Antares......
since last reply, I have setup whole process like this in DTS package..
1. excecute command for backup
2. on success execute process task FTP to required server
3. then delete backup file.
4. on another server in DTS execute command for restore database.
5. on completion of restoration delete transfered backup file.
but I have asked that i want to do all this in store procedure (from one place which better to manage and hopefully quicker). I am trying to write but it would be nice and save my time to get head start from you guys.
please guide me how can I write whole process in store procedure.
for David.....
sorry david I thought you want know.. my mistake. I just came cross this article for same process using SQL 6.5 and 7.0..
thankx any way
swarn
SqlIndia
December 9, 2002 at 8:17 am
To reply to the original question:
1) There is a bug in the FTP task in DTS. If the file is greater than a certain size, it will be deleted at the end of the FTP.
2) The FTP task in DTS can only read, not send.
December 9, 2002 at 3:34 pm
Here is a stored procedure to ftp files using SQL Server stored procedure:
Create proc 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 @sql varchar(255), @cmd varchar(255), @batch_ftp varchar(255), @ret int
set @sql = '"SELECT ftp_batch FROM ##temp_ftp_bat WHERE file_to_push = '''+ @file_to_push+'''"'
set @batch_ftp = Left(@file_to_push, Len(@file_to_push)-4) +'.bat'
set @cmd = 'BCP '+ @sql +' queryout '+ @batch_ftp +' /T /c'
Create table ##temp_ftp_bat(ftp_batch varchar(255), file_to_push varchar(255))
Insert into ##temp_ftp_bat values (@ftp_login, @file_to_push)
Insert into ##temp_ftp_bat values (@ftp_pwd, @file_to_push)
Insert into ##temp_ftp_bat values ('put '+@file_to_push, @file_to_push)
Insert into ##temp_ftp_bat values ('bye', @file_to_push)
EXEC master.dbo.xp_cmdshell @cmd
Drop table ##temp_ftp_bat
--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
Drop table #temp_ftp_results
--STEP 3
--delete batch file
set @cmd = 'del '+@batch_ftp
EXEC master.dbo.xp_cmdshell @cmd
go
December 15, 2002 at 4:52 pm
I ran into this problem, I assume it is bug in FTP feature of DTS. It appeared to be happening on the last file downloaded. My work around was to download a small dummy file after the file I needed.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply