Error Handling [?] - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error Handling [?]

Author

Topic

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-06-02 : 06:26:53

Hi fellow code warriors

I have a problem with handling errors within a procedure.
I want to execute the following proc and if it picks up an error along the way it should inform me (by e-mail using the sp_send_cdosysmail proc). I would also like it to include the error msg and Number.

What I realised is that it executes and stops if or when there's an error but if I try to pick up the error using @@Error I can't because @@Error is active (<> '0') after the error and by then the proc has already stopped and therefore I can't pick up the

@@Error

Code Follows:


Declare @InpFile Varchar(400),
	@Load_Period Datetime


exec get_load_period @load_period out


if datepart(m,@Load_Period) < 10 
	Set @InpFile = 'G:\Backup SOR Data\BF_Load' 
			+ right(cast(datepart(year,@Load_Period) as Varchar),2) 
			+ '0' + cast(datepart(m,@Load_Period) as varchar) + '\TSSA.L071a'
else
	Set @InpFile = 'G:\Backup SOR Data\BF_Load' 
			+ right(cast(datepart(year,@Load_Period) as Varchar),2) 
			+ cast(datepart(m,@Load_Period) as varchar) + '\TSSA.L071a'


Set nocount off

Declare @Recs numeric,
        @strSql NVARCHAR(500)
 
Truncate Table dbo.Temp71a

Set @strSql ='Bulk insert dbo.Temp71a from x' + @InpFile +'x'+ 
' With (DatafileType=xcharx,
 Fieldterminator=x,x,
 --lastrow=10000,
 rows_per_batch=10000,
 tablock, 
 Rowterminator= x\nx)'

Set @strSql=Replace(@strSql,'x','''')


EXECUTE sp_executesql @strSql
if @@Error <> '0'
	GOTO ErrorHandler


ErrorHandler:
	Select @Error_ID = @@Error
	
	If @Error_id <> 0
	
	
		Select @Error_Des = description
		from master.dbo.sysmessages
		Where error = @Error_ID
	
		Set  @Body = 'The Procedure encountered the following error.' + 
		'Error Number :' + Cast(@Error_ID as Varchar) + '.' +
		'With the Following description :' + 
		@Error_Des
		
		exec 	sp_send_cdosysmail
		'BF_Update@MyDomain.Com',          --Sender ID
		'MyID@MyDomain.Com',               --Receiver ID
		'Update Error',                    --Subject
		@Body                              --Msg

In the code above, I put in the wrong file name and it throws out an error saying the file doesn't exist, How can I push that error to my "Send mail (sp_send_cdosysmail)" Proc?????

You can't teach an old mouse new clicks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 07:09:07

Assign that value immediately after Execute Statement
EXECUTE sp_executesql @strSql
Select @Error_ID = @@Error
if @Error_Id <> '0'
GOTO ErrorHandler

Madhivanan

Failing to plan is Planning to fail