Rollback transaction error

I am getting this error on a sp with roleback..I believe I did start with a begin as seen below. Thx

[SQLServer]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Code...

if @err = 0
begin
  commit
end
else
begin
  rollback
  raiserror('insert failed', 16,1)
end


END
Glen_DAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
I think either you commited or possibly rolled back ur transaction before this line, posssiblly u missed a return statement there
0
chapmandewCommented:
doens't look like you've posted all of your code, but you're missing a begin tran

begin tran

f @err = 0
begin
  commit
end
else
begin
  rollback
  raiserror('insert failed', 16,1)
end
0
Aneesh RetnakaranDatabase AdministratorCommented:
u can check the @@TRANCOUNT value which shows the no of open transaction, but i prefer you check the entire code

if @@TRANCOUNT > 0
   ROLLBACK TRAN
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Glen_DAuthor Commented:
here's all my code below the variables...thx

BEGIN

      SET NOCOUNT ON;

INSERT INTO tbl_Facility (Facility_Name, Facility_Description, Building_Nos, Rooms, Shuttle_Legacy)
VALUES(@Facility_Name, @FacilityDescription, @BuildNos, @Rooms, @Shuttle_Legacy)

set @err = @@ERROR
set @MainID = SCOPE_IDENTITY()

if @err = 0
begin
insert into dbo.LU_Facility (Facility_Name)
(select distinct dbo.InitCap(ltrim(rtrim(facilityname))) as Facility from temp where facilityname not in (select facility_name from dbo.LU_Facility))
set @err = 0
end

if @err = 0
begin
  insert into tbl_Program (MainId, programID, projectID, centerID, elementID)
  values (@MainID, @Program, @Project, @Center, @Element)
  set @err = 0
end


if @err = 0
begin
  insert into tbl_Project_Req (MainId, project_req)
  values (@MainID, @Requirement)
  set @err = 0
end


if @err = 0
begin
  insert into tbl_Utilization (MainId, Need_Duration_Start, Need_Duration_End, UsageID, ReadinessID)
  values (@MainID, @StartDate, @EndDate, @UsageLvl, @Readiness_Status)
  set @err = 0
end

if @err = 0
begin
  insert into tbl_Confidence(MainId, ConfidenceID)
  values (@MainID, @ConfidenceLevel)
  set @err = 0
end

if @err = 0
begin
  insert into tbl_Operational  (MainId, LEO_TS_Development, LEO_TO, Year_TO_Begin, Lunar_TS_Development, Lunar_TO, Lunar_SD_Op)
  values (@MainID, @LEOTransSysDev, @LEOTransOps,  @YearLEOTransOpsBegan, @LunarTransSysDev, @LunarTransOps, @LunarSurfaceSysDev_Ops)
  set @err = 0
end

if @err = 0
begin
  commit
end
else
begin
  rollback
  raiserror('insert failed', 16,1)
end


END

0
Aneesh RetnakaranDatabase AdministratorCommented:
As tim mentioned you were missing the begin transaction

BEGIN TRAN

      SET NOCOUNT ON;

INSERT INTO tbl_Facility (Facility_Name, Facility_Description, Building_Nos, Rooms, Shuttle_Legacy)
VALUES(@Facility_Name, @FacilityDescription, @BuildNos, @Rooms, @Shuttle_Legacy)

select @err = @@ERROR , @MainID = SCOPE_IDENTITY()

if @err = 0
begin
insert into dbo.LU_Facility (Facility_Name)
(select distinct dbo.InitCap(ltrim(rtrim(facilityname))) as Facility from temp where facilityname not in (select facility_name from dbo.LU_Facility))
set @err = 0
end

if @err = 0
begin
  insert into tbl_Program (MainId, programID, projectID, centerID, elementID)
  values (@MainID, @Program, @Project, @Center, @Element)
  set @err = 0
end


if @err = 0
begin
  insert into tbl_Project_Req (MainId, project_req)
  values (@MainID, @Requirement)
  set @err = 0
end


if @err = 0
begin
  insert into tbl_Utilization (MainId, Need_Duration_Start, Need_Duration_End, UsageID, ReadinessID)
  values (@MainID, @StartDate, @EndDate, @UsageLvl, @Readiness_Status)
  set @err = 0
end

if @err = 0
begin
  insert into tbl_Confidence(MainId, ConfidenceID)
  values (@MainID, @ConfidenceLevel)
  set @err = 0
end

if @err = 0
begin
  insert into tbl_Operational  (MainId, LEO_TS_Development, LEO_TO, Year_TO_Begin, Lunar_TS_Development, Lunar_TO, Lunar_SD_Op)
  values (@MainID, @LEOTransSysDev, @LEOTransOps,  @YearLEOTransOpsBegan, @LunarTransSysDev, @LunarTransOps, @LunarSurfaceSysDev_Ops)
  set @err = 0
end

if @err = 0
begin
  commit
end
else
begin
  rollback
  raiserror('insert failed', 16,1)
end


END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glen_DAuthor Commented:
Thx Guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.