Retry until download .bak file

Hi,

I have this below SQL Code which can download .bak file from FTP but sometimes it got failed due to connection problem. I want to add a logic if the connection got failed then retry again until download and then go to the next step

The SQL job has the following steps..

exec xp_cmdshell 'cd C:\Program Files (x86)\WinSCP  && winscp.com /command "open ftpes://Secure%xxx.FTPS:xxx@ftps.xxx.com -hostkey=*" "cd /database" "lcd U:\MSSQLI2" "get *.bak"';
go


EXEC msdb.dbo.sp_send_dbmail
@profile_name ='Default',
@recipients = 'is_sqldba@xxx.com',
@body = 'Refresh will be start after 5 mins, please complete your current work and disconnect, Thanks!!!',
@subject = 'IND Database Refresh daily'
GO
WAITFOR DELAY '00:01:00'
GO

declare @sqlstr nvarchar(1024)
declare @dt char(8)
select @dt=convert( char(8),getdate(),112)
select @dt
select @sqlstr =
'restore headeronly from disk = ''U:\MSSQLI2\IND_157GIC034_backup_'+@dt+'.bak'';'
select @sqlstr
exec (@sqlstr)
go

Thanks

Zahid
Zahid AhamedDatabase AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Put your code in a TRY...CATCH block.
0
Zahid AhamedDatabase AdministratorAuthor Commented:
Hi Vitor,

I wanted to try with this in the SQL job step. Since my goal is if the following statement fail then can retry again until the .bak file download or can try to reconnect into the FTP server.

exec xp_cmdshell 'cd C:\Program Files (x86)\WinSCP  && winscp.com /command "open ftpes://Secure%xxx.FTPS:xxx@ftps.xxx.com -hostkey=*" "cd /database" "lcd U:\MSSQLI2" "get *.bak"';
go

Here i have attached the screen shot.

Or you wanted me to try with this Try Catch logic?

BEGIN TRY  
   
exec xp_cmdshell 'cd C:\Program Files (x86)\WinSCP  && winscp.com /command "open ftpes://Secure%xxx.FTPS:xxx@ftps.xxx.com -hostkey=*" "cd /database" "lcd U:\MSSQLI2" "get *.bak"';

END TRY  

BEGIN CATCH  
    SELECT  
        ERROR_NUMBER() AS ErrorNumber  
       ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH  


Thank You,

Zahid
InkedCapture_LI.jpg
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use the TRY..CATCH block in a job step. Just execute again the code in the CATCH block instead of presenting the error number and message.
0
Zahid AhamedDatabase AdministratorAuthor Commented:
using SQL Agent this is working fine.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Zahid AhamedDatabase AdministratorAuthor Commented:
This got fixed using sql agent job in the retry option.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.