Error handling in TSQL

I have the following codes in TSQL to run. I set the last insert statement to have error. But I did not get the rollback tran.

What i got is:
Here goes the transactions
(10 rows)
(12 rows)
(13 rows)
Server: Msg 208, Level 16, State 1, Line 6
Invalid object name '#manageril1'.
======================================
begin tran
print 'Here goes the transactions'
insert into profile select * from #profins
insert into prof_compo select * from #compoins
insert into apps_user select * from #appluserins
insert into manager select * from #manageril1
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
else
Commit tran
print 'commit tran'
go
=====================================
What could I have not done or missed out? Why the rollback doesnot work?

Please advice.

Rgds,
Sam.From BOL:

A batch is a group of one or more Transact-SQL statements sent at one time from an application to Microsoft SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time.

A compile error, such as a syntax error, prevents the compilation of the execution plan, so none of the statements in the batch are executed.

A run-time error, such as an arithmetic overflow or a constraint violation, has one of two effects:

Most run-time errors stop the current statement and the statements that follow it in the batch.

A few run-time errors, such as constraint violations, stop only the current statement. All the remaining statements in the batch are executed.

Some recommendations about your case:

- check for errors after every insert (update,delete) statement;
- create sp and check return value (rollback if needs).|||The problem isn't in your rollback. Your IF statement is never executed because the procedure fails critically before it gets to it. @.@.ERROR registers constraint and primary key violations, but if a gross syntax error causes the process to crash then its not going to help.

blindman|||And the error handling is incorrect
@.@.error is reset by every sql statement. You might want to set a variable or goto an error handler on errror.

begin tran
print 'Here goes the transactions'

insert into profile select * from #profins
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
insert into prof_compo select * from #compoins
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
insert into apps_user select * from #appluserins
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
insert into manager select * from #manageril1
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
Commit tran
print 'commit tran'

Error Handling in codes

Hi all,
This is related to renaming of a file with sql code.
I have a stroed procedure that deals with FTP on exchange server,
rename the file and then send email to the proper group.
Platforms: FTP server is on Windows 2003 Exchange server with IIS 6.0.,
SQL Server 2000.
The portion of the stored procedure is given below.
select
@.recips = '' + rtrim(notify) + '',
@.txt =
'This file is located on the server W2K3S1 ' +
'under the DATA\' + rtrim(l.username) + ' directory.' + char(13) +
char(13) +
'The file name: ' + rtrim(@.NewTarget) + char(13) +
'Date Received: ' + convert(varchar(25),LogTime) + char(13) +
'File Size: ' + convert(char(20), BytesRecvd) ,
@.sub = 'FTP Notification from ' + rtrim(description),
@.newfilename = rtrim(@.NewTarget),
@.oldfilename = rtrim(l.username)+'\'+ rtrim(Target)
from FTP l , Notify n
where LogID = @.logId and
lower(l.username) = lower(n.username)
exec master.dbo.xp_sendmail
@.recipients = @.recips,
@.message = @.msg,
@.query = @.cmd ,
@.subject = @.sub ,
@.no_header = 'TRUE', @.width = 2500
At present what it does is it sends email saying the file has been
renamed, whereas the file was not renamed actually in the server.
I was wondering whether I could add some error codes in between that
will check the file has been renamed on the FTP server successfully and
then will send email to the group.
Could anyone please suggest/advice what I can do in between select and
exec master.dbo.xp_sendmail?
Thanks a million in advance for your help.
Best regards,
mamunOn 21 Sep 2005 10:20:54 -0700, microsoft.public.dotnet.languages.vb
wrote:

>Hi all,
>This is related to renaming of a file with sql code.
>I have a stroed procedure that deals with FTP on exchange server,
>rename the file and then send email to the proper group.
>Platforms: FTP server is on Windows 2003 Exchange server with IIS 6.0.,
>SQL Server 2000.
>
>The portion of the stored procedure is given below.
(snip)
>At present what it does is it sends email saying the file has been
>renamed, whereas the file was not renamed actually in the server.
Hi mamun,
Not too surprising, since there is nothing in your code that would
actually try to rename the table. Or did you trim the quote a bit too
much, and leave out the code where the actual renaming is done?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)