July 24, 2008 at 7:06 am
This procedure used to work well , I made some changes and added the Return statement
and now it gives no errors but does not insert data. If I comment the return statement
it works
My objective is to achieve the following
1) The Return statement to teminate the process if the file is not found and retry
till 11pm with 30 minute intervals in between. And only send the failure message on the last retry attempt
2)Is it possible to code the retry attempts using T-sql code instead of the option in job ?
Currently Iam using the retry attempt option built into the job.
Is this possible ?
July 24, 2008 at 8:09 am
You only get one statement after an IF clause so your current code is hitting RETURN and exiting. Try this:
ELSE
BEGIN
--==== If The file does not exist process terminates
EXEC msdb.dbo.sp_send_dbmail
@Recipients = 'xxxxx@yahoo.com'
,@Body = ' File Was not found in \\Muftafa\Downloads\.'
,@Subject = 'Automated Failure Message'
RETURN
END
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 25, 2008 at 1:29 am
This now gives an error
Msg 102, Level 15, State 1, Line 62
Incorrect syntax near 'END'.
and the rest of the script doesn't work
July 25, 2008 at 6:36 am
Make sure the other parts of the IF statement are also wrapped in BEGIN..END. You simply can't have multiple statements within an IF clause without wrapping them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2008 at 4:22 am
I simply added begin and End in the Else part and did some of the changes as shown below...
ELSE
begin
--==== If The file does not exist process terminates
EXEC msdb.dbo.sp_send_dbmail
@Recipients = 'xxxxx@yahoo.com'
,@Body = ' File Was not found in \\Muftafa\Downloads\.'
,@Subject = 'Automated Failure Message'
insert into tbStaging values (1)
RETURN
end
It worked perfectly fine. the tblStaging table had one record and NO Error.
Atif Sheikh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply