October 13, 2006 at 4:17 pm
I am new to SQL Server, so I'm sure I am making a simple error.
I get the error 'Transaction count after EXECUTE indicates a COMMIT or ROLLBACK is missing.
I have an update and an insert. I think I have done a commit if @@Error is zero otherwise rollback after each.
What might be wrong.
Also, can you confirm that only a BEGIN requires an END. IF and BEGIN TRANSACTION don't. Is that correct ?
Thanks
Robert
October 16, 2006 at 8:00 am
This was removed by the editor as SPAM
October 16, 2006 at 12:36 pm
You must be missing a Begin \ End block after an IF statement.
Can you post the Code ?
Difficult to give advice without it...
October 16, 2006 at 2:02 pm
Thanks for the reply. I think I sorted it out. The error message was misleading. What happened was that I refered to a table name as singular, rather tan plural. That is, I inserted into tblCar instead of tblCars. tblCar does not exist, but check syntax dindn't complain.
October 16, 2006 at 4:08 pm
Check out Joe Toscano's suggestions on Try...Catch regarding error messages with SQL2005 instead of using @@error:
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1180620,00.html#TRY
P_Insert_New_BookTitle_2K5
----------------------------------------------
create proc P_Insert_New_BookTitle_2K5
(@TitleName nvarchar(128),
@Price money,
@au_fname nvarchar(32),
@au_name nvarchar(64),
@CommissionRating int)
as
declare @err int,
@tablename sysname,
@errormessage nvarchar(2000)
BEGIN TRY
begin transaction
select @errormessage = 'insert into Titles table failed',
@tablename = 'Titles'
insert dbo.Titles (TitleName, Price)
values (@TitleName, @Price)
select @errormessage = 'insert into Authors table failed',
@tablename = 'Authors'
insert dbo.Authors (au_fname, au_lname, TitleID,
CommissionRating)
values (@au_fname, @au_fname, @@IDENTITY,
@CommissionRating)
commit transaction
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- Log the error
insert dbo.Application_Error_Log (UserName, tableName,
errorNumber, errorSeverity, errorState, errorMessage)
values (suser_sname(), @tableName, ERROR_NUMBER(),
ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE())
RAISERROR (@errormessage, 16,1)
END CATCH
Mary Myers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply