July 6, 2009 at 4:35 am
Hi Everyone,
I have placed a bit of transaction handling on my code, the problem now is that because a table is not present on the database. The problem is because the table does not exist on the database, the procedure exits but the transaction is still open on the database. Is there anything I can do whereby if the procedure fails for any reason, all transactions are also dropped as well.
Thanks
Create Procedure spInsertProfile
as
Begin
Begin tran
Insert into PersonProfile.dbo.[Profile]
(
SRN,
Name,
[Address],
School
)
Select
SRN,
Name,
[Address],
School
from tempprofile
Insert into tblMarket_Profifile
(
SRN,
ProfileNo,
Score,
Rating
)
Select
SRN,
ProfileNo,
Score,
Rating
from mkttempprofile
Commit
IF @@trancount > 0
Rollback
End
Msg 208, Level 16, State 1, Procedure spInsertProfile, Line 16
Invalid object name 'tblMarket_Profifile'.
Msg 266, Level 16, State 2, Procedure spInsertProfile, Line 2215
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
July 6, 2009 at 4:56 am
Dean Jones (7/6/2009)
Msg 208, Level 16, State 1, Procedure spInsertProfile, Line 16Invalid object name 'tblMarket_Profifile'.
Msg 266, Level 16, State 2, Procedure spInsertProfile, Line 2215
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Hi,
First msg shows the table not qualified in the DB ie put the data base and table owner before the table like PersonProfile.dbo.[Profile]
second msg shows the coding error
try this
Create Procedure spInsertProfile
as
Begin
Insert into PersonProfile.dbo.[Profile]
(SRN,Name,[Address],School)
Select SRN,Name,[Address],School
from tempprofile
Insert into /*Db and the owner of the table*/tblMarket_Profifile
(SRN,ProfileNo,Score,Rating)
Select SRN,ProfileNo,Score,Rating
from mkttempprofile
IF @@rowcount > 0
begin
rollback tran
end
end
ARUN SAS
July 6, 2009 at 8:29 am
Thanks for the reply, I cannot see any begin tran in the code above, or is it not needed.
Secondly, is there a workaround for transaction handling if the table does not exist ?
Or will the code simply exit if an object is not there and leave an open transaction ?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply