Stored Proc generated Error 266

[LucieLastic]

hi

I have the following structure in my proc. It is appears to be generating the following error message on the ALTER TABLE..ADD CONSTRAINT command (in bold) and I don't know why. My Rollback isn't called either. Sorry, but I'm not very experienced at writing Transact SQL. Are my Transaciton commands in the right places? Hope someone can guide me in the right direction.

Error:

Server: Msg 266, Level 16, State 2, Procedure sp_ManageAlignmentTable, Line 191
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

create proc sp_ManageAlignmentTable
as
DECLARE @ErrorCheck int
BEGIN

-- Start a transaction
[COLOR=blue]BEGIN TRANSACTION[/color]
:
DELETE FROM...
INSERT INTO...

ALTER TABLE DROP INDEX...
ALTER TABLE DROP INDEX...

PRINT 'RUN - Renaming ALIGNMENT Tables'

exec sp_rename CMP_ALIGN_TEMP, CMP_ALIGN_TEMPX
IF @@ERROR<>0 GOTO error_handler

exec sp_rename CMP_ALIGN, CMP_ALIGN_TEMP
IF @@ERROR<>0 GOTO error_handler

exec sp_rename CMP_ALIGN_TEMPX, CMP_ALIGN 
IF @@ERROR<>0 GOTO error_handler

PRINT '-END - Renaming ALIGNMENT Tables'

DELETE FROM...
PRINT 'RUN - Creating Indexes'

CREATE  INDEX [IDX_ID] ON [CMP_ALIGN_TEMP]([ID]) ON [PRIMARY]

IF @@ERROR<>0 GOTO error_handler

[COLOR=maroon]ALTER TABLE [CMP_ALIGN_TEMP] ADD CONSTRAINT [PK_ALIGN_TEMP] PRIMARY KEY  CLUSTERED ...[/color]
IF @@ERROR<>0 GOTO error_handler
:


PRINT '*** COMMIT TRANSACTION ***'

[COLOR=blue]COMMIT TRANSACTION[/color]
PRINT '*** COMMIT TRANSACTION OK ***'
RETURN

error_handler:
  BEGIN
[COLOR=blue]    ROLLBACK TRANSACTION[/color]
    PRINT '##### ERROR ##### - Transaction has been rolled back'
    RETURN
  END

END
GO

Thank you in advance,
lou

 

[SQLDenis]

a non trapable error
use SET XACT_ABORT ON (after looking it up in BOL of course) ;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[hneal98]

it looks like you are putting your commit statement at the end of your procedure. You need to have it at the beggining of your processing and your rollback at the end.

 

[LucieLastic]

hi

Thanks for the reply SQLDenis. I commented out the ALTER TABLE..ADD CONSTRAINT..PRIMARY KEY and it appears to have worked. So, maybe I can just add a normal unique index rather than a primary key and that'll sort the problem out. Why would ALTER Table make it fail? I guess it's probably got another BEGIN transaction in there. What do you think?

lou

 

[SQLDenis]

Maybe duplicate values or other garbage data (NULLS)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[LucieLastic]

hi

I think the problem was it was timing out.

lou