March 22, 2010 at 4:53 pm
I am trying to create a rollback and commit transaction as shown below,but when i run the script i get an error as
A GOTO statement references the label 'Error_Handler' but the label has not been declared. Is there a better way to rollback transction, i am trying to rollback trnasaction on a bunch of queries, do i have to rollback one at a time ??
Any help od insight would be awesome
The script used is
Declare @Error int
BEGIN TRANSACTION
CREATE TABLE [dbo].[XXX](
[a] [smallint] NOT NULL,
[nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[c] [tinyint] NOT NULL,
[d] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_XXX_1] PRIMARY KEY CLUSTERED
(
[PKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
SET @Error = @@ERROR
IF (@Error <> 0) GOTO Error_Handler
COMMIT TRAN
Error_Handler:
IF @Error <> 0
Begin
Rollback Transaction
PRINT 'Unexpected error occurred!'
END
March 22, 2010 at 5:02 pm
Try this one:
create proc sp_sample
@parameter1 char(4),
@parameter2 int = 0
as
set nocount on
declare @retcode int
begin tran
exec @retcode = master..xp_cmdshell 'ping -n 1 apmobil2'
if @retcode <>0 or @@error <> 0
goto FAILURE
commit tran
return 0
FAILURE:
if (@@trancount > 0)
begin
declare @lerror int
select @lerror = @@error
rollback tran
RAISERROR (@lerror,16,-1)
return 1
end
else
return 1
go
March 22, 2010 at 5:04 pm
amandix (3/22/2010)
Try this one:
create proc sp_sample@parameter1 char(4),
@parameter2 int = 0
as
set nocount on
declare @retcode int
begin tran
exec @retcode = master..xp_cmdshell 'ping -n 1 apmobil2'
if @retcode <>0 or @@error <> 0
goto FAILURE
commit tran
return 0
FAILURE:
if (@@trancount > 0)
begin
declare @lerror int
select @lerror = @@error
rollback tran
RAISERROR (@lerror,16,-1)
return 1
end
else
return 1
go
March 22, 2010 at 5:06 pm
Seeing as how this is posted in the 'SQL Server 2008' section, you should be using TRY/CATCH blocks instead. (The TRY would include the commit and the CATCH would include the rollback.)
If this is mis-posted and you're on 2K, the TRY/CATCH is not available.
March 23, 2010 at 7:20 am
Confusing. As posted, the CREATE TABLE statement will not compile since column PKey, referenced in the PRIMARY KEY definition, does not exist. Errors like this cannot be caught by TRY...CATCH or @@error.
There was no problem with the name or position of the label, so the code does not demonstrate the posted error at all. Very odd.
Anyway, assuming the PRIMARY KEY definition error is spurious, the following code works:
BEGIN TRY
BEGIN TRANSACTION
CREATE TABLE [dbo].[XXX]
(
[a] [smallint] NOT NULL,
[nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[c] [tinyint] NOT NULL,
[d] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
) ON [PRIMARY];
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCH;
March 23, 2010 at 11:05 am
Thanks that works with the try and catch, I am creating a bunch of tables and wonder if i have to define a try catch for every create statement, is there a way i can just call the commit on top and rollback and print error message on the bottom, if i create a try and block for evey one of the create table will it not stop the procedure whenever it fails
this i what i am actually trying to do
Begin transacation
** insert statments
** create tables
***Update statements
commit only if no error
rollback if errors
but i could use try and catch for every block, i dont know if that is efficient, let me know if anyhting that doesnot make sense
March 23, 2010 at 11:14 am
Also i used the trycatch on alter table as shown below and got an error as
Msg 2705, Level 16, State 4, Line 240
Column names in each table must be unique. Column name 'uuu' in table 'vvv' is specified more than once.,Should not i get the error message,pelase let me know what i am doin wrong
BEGIN TRY
BEGIN TRANSACTION
ALTER TABLE XXX ADD uuuu nvarchar(60) NULL
ALTER TABLE XXX ADD vvvv nvarchar(60) NULL
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCH;
March 23, 2010 at 11:14 am
Everything that you want to have happen together (either all committing or all rolling back) goes in the same TRY block. If any part of that block of code fails, it goes to the CATCH where you can roll it all back. You can use ERROR_NUMBER() (and other functions) to return information about what the specific failure was.
March 23, 2010 at 11:27 am
Is there a way we can create a procedure and call it in a script that would be a neat thing to learn please let me know if anyone has experience with this.
thanks for the replies
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply