TRY CATCH PROBLEM

Question

 



   BEGIN TRY



            EXEC msdb.dbo.sp_send_dbmail 



            @recipients=@email,



            @body= @msg,



            @subject =@subjectformat,



            @file_attachments=@attachment,



 



            @profile_name = 'Airtel',



            @body_format = 'HTML' ;



 



   END TRY



 



BEGIN CATCH



GOTO LABEL



END CATCH



 



--SOME SCRIPT TO PERFORM TASK 1 





 



 



 



LABEL : --SOME OTHER SCRIPT TO PERFORM TASK 2 

WHATS WRONG WITH THE STRUCTURE OF TRY CATCH?? EVEN WHEN THERE IS SOME EXCEPTION, TASK 1 IS PERFORMED.WHY?

Answer 1

BEGIN TRY
       Select 1/0--divide by zero error
END TRY
 
BEGIN CATCH
	GOTO LABEL
END CATCH
Select 'task1' 
 
LABEL:
Select 'Task2'

It does not select Task1 when encounter an error

Answer 2

BEGIN TRY

       Select 1/0--divide by zero error

END TRY

 

BEGIN CATCH

	GOTO LABEL

END CATCH

Select 'task1' 

 

LABEL:

Select 'Task2'

It does not select Task1 when encounter an error

THATS RIGHT BUT THERE SEEMS TO BE PROBLEM WITH msdb.dbo.sp_send_dbmail EXCEPTIONS

Answer 3

What is the error you get when executing msdb.dbo.sp_send_dbmail ?. Try...catch will catch only those errors where sevirity greater than 10

Answer 4

What is the error you get when executing msdb.dbo.sp_send_dbmail ?. Try...catch will catch only those errors where sevirity greater than 10

if i give invalid path for @file_attachments, it shows

(4 row(s) affected)

Msg 22051, Level 16, State 1, Line 0

Attachment file \\\------------------------ is invalid.

(1 row(s) affected)

(1 row(s) affected)

here i dont want these 

(1 row(s) affected)

(1 row(s) affected)
after the error :(

Answer 5

There is a technique to catch these knid of errors.. See the link

http://www.sqlusa.com/articles2008/trycatch/


Or you can create a customized procedure for sending mails which take care of invalid attachment files.

Create Proc SendMail 
(
	@recipients  varchar(100),
	@body   varchar(max),
	@subject   varchar(100),
	@profile_name    varchar(100)='DbMailProfile',
	@file_attachments  varchar(100)='',
	@body_format    varchar(10)='HTML'

)
AS
Begin
	Declare 
		@FileInfo Table(FileExist Int,IsDirectory Int,ParentDirectoryExist Int)

Begin Try
	If @file_attachments <> ''
	Begin
		Insert Into @FileInfo EXEC XP_FILEEXIST @file_attachments
		IF (Select FileExist from @FileInfo)=0
		RAISERROR ('Attchment File Does not Exist.',11,1)
	End
	EXEC msdb.dbo.sp_send_dbmail 
		@recipients=@recipients,
		@body= @body,
		@subject =@subject,
		@file_attachments=@file_attachments,
		@profile_name = @profile_name,
		@body_format = @body_format
End Try

Begin Catch
	Select ERROR_MESSAGE() "Error Description",ERROR_SEVERITY()"Error Severity"
End Catch
End



SendMail '[e-mail]','Test Mail','Test Subject','DbMailProfile','C:\notExist.txt'