[Webinar] Streamline your web hosting managementRegister Today

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

COMMIT / ROLLBACK usage WITH EXEC giving error on MS SQL Server

MS SQL Server query giving a script error when I use an EXEC command (Stored Procedure) just after or before COMMIT / ROLLBACK. It is ok when I take the EXEC command lines out.

Could you help please, is there any work around for this?

Thanks,
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_test]
AS
BEGIN
	SET NOCOUNT ON;
	--////////////////////////////////////////////
	Declare @ErrorCode int
Select @ErrorCode = @@Error

	Declare @TransactionCountOnEntry int
If @ErrorCode = 0
Begin
   Select @TransactionCountOnEntry = @@TranCount
   BEGIN TRANSACTION
End

Declare @Temp nchar(10)
SELECT @Temp = 'Insertion'
INSERT INTO dbo.Test(logo) VALUES(@Temp)

--////////////////////////////////////////////////
		IF @@TRANCOUNT > @TransactionCountOnEntry
		BEGIN
		   IF @ErrorCode = 0		  
		  COMMIT TRANSACTION
		  EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has run successfully'

		   ELSE
		  ROLLBACK TRANSACTION
		  EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has failed'
		END
--/////////////////////////////////////////
END
GO

Open in new window

0
Serdar Demirkol
Asked:
Serdar Demirkol
4 Solutions
 
RiteshShahCommented:
I affraid, I don't understand your question 100% but even try this code.
0
 
Rajkumar GsSoftware EngineerCommented:
#1:- You have opened the Transaction inside the condition
If @ErrorCode = 0
      BEGIN TRANSACTION

But you are trying ROLLBACK TRANSACTION for else of condition that started Transaction

eg:- If @ErrorCode = 1 BEGIN TRANSACTION code will not get executed. But ROLLBACK TRANSACTION can occur.

This is one that I felt wrong in the first sight.


#2:- It needs BEGIN.. END to group the set of commands that need to be executed inside IF.


Attached the corrected script

Raj
USE [AdventureWorks] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
 
ALTER PROCEDURE [dbo].[sp_test] 
AS 
BEGIN 
        SET NOCOUNT ON; 
        --//////////////////////////////////////////// 
        Declare @ErrorCode int 
Select @ErrorCode = @@Error 

   BEGIN TRANSACTION 
 
        Declare @TransactionCountOnEntry int 
If @ErrorCode = 0 
Begin 
   Select @TransactionCountOnEntry = @@TranCount 

End 
 
Declare @Temp nchar(10) 
SELECT @Temp = 'Insertion' 
INSERT INTO dbo.Test(logo) VALUES(@Temp) 
 
--//////////////////////////////////////////////// 
IF @@TRANCOUNT > @TransactionCountOnEntry 
BEGIN 
	IF @ErrorCode = 0  
		BEGIN             
			COMMIT TRANSACTION 
			EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has run successfully' 
		END
	ELSE 
		BEGIN             
			ROLLBACK TRANSACTION 
			EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has failed' 
		END
END 
--///////////////////////////////////////// 
END 
GO

Open in new window

0
 
Lee SavidgeCommented:
Try this. You're missing the begin and end in the if statement.

Lee
IF @@TRANCOUNT > @TransactionCountOnEntry
                BEGIN
                   IF @ErrorCode = 0              
                   BEGIN
                    COMMIT TRANSACTION
                    EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has run successfully'
                   END
                   ELSE
                   BEGIN
                    ROLLBACK TRANSACTION
                    EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has failed'
                   END
                END

Open in new window

0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Lee SavidgeCommented:
If your IF statement has multiple statements inside it then you need to surround the block with BEGIN...END for the IF part and the ELSE part.

Lee
0
 
Serdar DemirkolApplication Developer & DBAAuthor Commented:
It works ok now.
Thanks guys,
0
 
RiteshShahCommented:
surprised!!! where my code went? It was there in very first answer of this question but not there now.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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