[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1040
  • Last Modified:

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.

USE [ICS_Internal]
GO
/****** Object:  StoredProcedure [dbo].[usp_AddToPicklist]    Script Date: 10/29/2011 13:10:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===============================================================
-- Author: Me
-- Create date: 10/28/2011
-- Description:	Insert Records from Access Project_Picklist_Buffer
-- requires: @IndexID to match parents ID in project_indexes table
-- returns error code and message
-- ===============================================================
ALTER PROCEDURE [dbo].[usp_AddToPicklist] 
	-- Add the parameters for the stored procedure here
	@IndexID int = 0,
	@Item_Number int = NULL,
	@Loc varchar(80) = NULL,	
	@Description varchar(80) = NULL,
	@Mfg varchar(50) = NULL,
	@Cat varchar(50) = NULL, 	
	@Type varchar(60) = NULL,
	@RangeScale varchar(60) = NULL,
	@Block varchar(60) = NULL,
	@Textvals varchar(255) = NULL,
	@FamilyCode varchar(6) = NULL,
	@Sch_Or_Pnl varchar(1) = NULL,
	@AssyCode varchar(24) = NULL,
	@Explode varchar(1) = NULL,
	@Line float = NULL,
	@AssyQty varchar(8) = NULL,
	@SSN int = NULL,
	@RetCode int = NULL OUTPUT,
	@RetMsg varchar(150) = NULL OUTPUT
AS

DECLARE @Err as int
SET @Err = @@ERROR
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRAN
--check if id exists maybe someone deleted it
SELECT * 
FROM Project_Indexes
WHERE ID = @IndexID
-- debug print
PRINT @@ROWCOUNT
IF @@ROWCOUNT <> 0 
	-- insert into project_picklists table
	BEGIN 
	INSERT INTO Project_Picklists
           ([IndexID]
           ,[Item_Number]
           ,[Loc]
           ,[Description]
           ,[Mfg]
           ,[Cat]
           ,[Type]
           ,[RangeScale]
           ,[Block]
           ,[Textvals]
           ,[FamilyCode]           
           ,[Sch_Or_Pnl]
           ,[AssyCode]
           ,[Explode]
           ,[Line]
           ,[AssyQty]
           ,[SSN])
     VALUES
           (@IndexID
           ,@Item_Number
           ,@Loc
           ,@Description
           ,@Mfg
           ,@Cat
           ,@Type
           ,@RangeScale
           ,@Block
           ,@Textvals
           ,@FamilyCode          
           ,@Sch_Or_Pnl
           ,@AssyCode
           ,@Explode
           ,@Line
           ,@AssyQty
           ,@SSN)
    END
ELSE
	BEGIN
		SELECT @RetCode = 0, @RetMsg = 'Specification Workseet: ' +  CONVERT(varchar, @@IDENTITY) + ' Was not found unable to update.'
		RETURN
	END
-- check for error
IF @Err = 0
	BEGIN
		SELECT @RetCode = 1, @RetMsg = 'Specification Worksheet: ' + CONVERT(varchar, @@IDENTITY) + ' Inserted Successfully'
		COMMIT TRAN
		RETURN
	END
ELSE
	BEGIN
		SELECT @RetCode = 0, @RetMsg = 'Runtime Error: ' + CONVERT(varchar, @Err)
		ROLLBACK TRAN
	END

Open in new window



I'm getting the following error:
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

Any Ideas on how to fix this?

Thanks

0
JeffatICS
Asked:
JeffatICS
1 Solution
 
TempDBACommented:
Before return, you need to committ or rollback the transaction.

IF @@ROWCOUNT <> 0

here in else portion, you are simply returning. You need to take the action with the transaction there.
0
 
JeffatICSAuthor Commented:
Replacing the Return with ROLLBACK TRAN solved the issue
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now