transaction error

[ademsandeepreddy]

when i am executing the stored procedure by passing xml it is throwing transaction error (1 row(s) affected) Msg 266, Level 16, State 2, Procedure get_next_sequence, Line 0 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. (1 row(s) affected) (1 row(s) affected) Msg 3902, Level 16, State 1, Procedure sp_ProcessExcelSheet, Line 58 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. (1 row(s) affected) stored procedure is here. ALTER PROCEDURE [dbo].[sp_ProcessExcelSheet] @file varchar (MAX) AS BEGIN DECLARE @ComplaintDetail TABLE ( ID int identity(1,1), FName Varchar(50) not null, Email Varchar(50), Address Varchar(100) not null, Disclose bit not null, Complaint text not null, Location varchar(50) not null, Area varchar(50) not null, PlaceFound varchar(50) not null, ComplaintMode varchar(50) not null ) DECLARE @idoc INT EXEC sp_xml_preparedocument @idoc OUTPUT, @file INSERT INTO @ComplaintDetail (FName,Email,Address,Disclose,Complaint,Location,Area,PlaceFound,ComplaintMode) SELECT FName,Email,Address,Disclose,Complaint,Location,Area,PlaceFound,ComplaintMode FROM OPENXML (@idoc, 'NewDataSet/Table',2) WITH (FName Varchar(50),Email Varchar(50),Address Varchar(100),Disclose bit,Complaint text, Location varchar(50), Area varchar(50), PlaceFound varchar(50), ComplaintMode varchar(50)) declare @Count int declare @IntCount int set @IntCount=1 select @Count=count(*) from @ComplaintDetail create table #Reff(FName varchar(50),Email varchar(50),ReffNo varchar(10) ) declare @FName Varchar(50) declare @Email Varchar(50) declare @Address Varchar(100) declare @Disclose bit declare @Complaint varchar(max) declare @Location varchar(50) declare @Area varchar(50) declare @PlaceFound varchar(50) declare @ComplaintMode varchar(50) declare @seqno int declare @sequentialNo varchar(10) BEGIN TRAN T1 while(@IntCount<@Count+1) begin exec dbo.get_next_sequence @seqno output set @sequentialNo= 'p'+convert(varchar(9),@seqno) select @FName=FName,@Email=Email,@Address=Address,@Disclose=Disclose,@Complaint=Complaint,@Location=Location,@Area=Area,@PlaceFound=PlaceFound,@ComplaintMode=ComplaintMode from @ComplaintDetail where ID=@IntCount Insert into HelpLine(ReferenceNo,FName,Email,Address,Disclose,Complaint,Location,Area,PlaceFound,ComplaintMode,ComplaintDate,ComplaintStatus) values(@sequentialNo,@FName,@Email,@Address,@Disclose,@Complaint,@Location,@Area,@PlaceFo

[Member 4501940]

You have a begin tran on the outside of a while loop and have a rollback on the inside with an unconditional commit on the outside. If a rollback occurs, the loop continues transaction-less and the commit will execute anyway = your error 3902. I would not wrap a transaction around a while loop if there was another way (there is!). Never issue a commit or rollback without checking to see if there is actually a tran.

if @@trancount > 0
	rollback transaction

I would use a try-catch, without a doubt. Heres generally how a transaction could be handled:

begin try
begin transaction

--dml statement here

if @@trancount > 0
commit transaction
end try

begin catch
if @@trancount > 0
rollback transaction
print error_message() --(execute my error handler)
end catch

Some of the other wierd messages you are getting is because the proc failed to issue a commit or rollback at all leaving 'things' in an un-committed state. Until you have verified that the proc you are developing will always close the trans - issue either a commit or rollback after each 'test'.