Cannot Use the COMMIT Statement within an INSERT-EXEC Statement......

Question

Post reply

Cannot Use the COMMIT Statement within an INSERT-EXEC Statement......

rdsb_2170

SSC Eights!

Points: 817

May 5, 2021 at 1:52 pm

#3879622

Good Morning,

I have been trying to resolve this error and one other error for over 10 hours now ( researching on line, trying various code changes etc.) I got one of the errors resolved, but this last one is driving me crazy. I'm at my wits end. I hate to say it but I just need someone to help be modify the script, so it will work and if possible explain what I'm doing wrong.

Any help would be very much appreciated.

Here is the script and the error.

USE [I3_CMS]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS ( SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#PackageMainTblu') )
DROP TABLE #PackageMainTblu;

BEGIN TRY

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET DEADLOCK_PRIORITY LOW;

CREATE TABLE #PackageMainTblu(
[PackageDateSold] [datetime] NULL,
[PackageChannelDeptCd] [varchar](5) NULL,
[DepartmentDescription] [varchar](50) NULL,
[Channel] [varchar](50) NULL,
[PackageTypeCd] [varchar](4) NOT NULL,
[PackageType] [varchar](50) NULL,
[PackageDescription] [varchar](100) NULL,
[CurrStatus] [varchar](20) NULL,
[PackageLeadId] [numeric](18, 0) NULL,
[PackageSaleId] [numeric](18, 0) NULL,
[PackageOfficeSite] [varchar](50) NULL,
[PackageDateCancelled] [datetime] NULL,
[PackageGrossCountPMA] [int] NULL,
[PackageCancelCountPMA] [int] NULL,
[NetPackage] [int] NULL,
[PackageStatus] [varchar](20) NULL,
[Destination] [varchar](50) NULL,
[RateCode] [varchar](4) NULL,
[Program] [varchar](20) NULL,
[InProductionDate] [date] NULL,
[TravelWindowMonths] [int] NULL,
[Gross] [int] NULL,
[Net] [int] NULL,
[Deposit] [int] NULL,
[DelayedBookingFee] [int] NULL,
[WeekendFee] [varchar](50) NULL,
[HolidayFee] [varchar](50) NULL,
[HighSeasonFee] [varchar](50) NULL
)

BEGIN TRANSACTION

INSERT INTO #PackageMainTblu
EXECUTE [I3_CMS].[dbo].[spPackageHistory_Tblu]

Select * From #PackageMainTblu

IF @@TRANCOUNT > 0
COMMIT

END TRY
BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState );

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;

END CATCH

GO

?

****** ERROR MESSAGE *****

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 50000, Level 16, State 0, Line 89

Cannot use the COMMIT statement within an INSERT-EXEC statement unless BEGIN TRANSACTION is used first.

Thank you,

Dawn

This topic was modified 3 years, 6 months ago by rdsb_2170.
This topic was modified 3 years, 6 months ago by Steve Jones - SSC Editor. Reason: code formatting

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Jones - SSC Editor SSC Guru Points: 736198 More actions · Answer 2

This works for me:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR alter PROCEDURE spPackageHistory_Tblu
AS
SELECT GETDATE(), 'test', 'test'


GO

IF EXISTS ( SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#PackageMainTblu') )
DROP TABLE #PackageMainTblu;

BEGIN TRY

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET DEADLOCK_PRIORITY LOW;

CREATE TABLE #PackageMainTblu(
[PackageDateSold] [datetime] NULL,
[PackageChannelDeptCd] [varchar](5) NULL,
[PackageChannelDeptCd2] [varchar](5) NULL
)

BEGIN TRANSACTION

INSERT INTO #PackageMainTblu
EXECUTE [dbo].[spPackageHistory_Tblu]

Select * From #PackageMainTblu

IF @@TRANCOUNT > 0
COMMIT

END TRY
BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState );

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;

END CATCH

GO

I'm not sure what's wrong in your code, but the basic structure works.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

Error 50000 is a user-defined error. You need to provide the code from your stored procedure [I3_CMS].[dbo].[spPackageHistory_Tblu].

Also, you should just have a THROW in the catch instead of setting those variables and using RAISERROR. Maybe you should also do the same inside your stored procedure.