October 23, 2015 at 9:27 am
I'm all of a sudden getting this error on a Stored Procedure that has not been touched since it was created.
Does anything stand out as being an issue?
Thanks.
Msg 266, Level 16, State 2, Procedure usp_ArchivexactControlPoint, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
CREATE PROCEDURE [dbo].[usp_ArchivexactControlPoint]
AS
DECLARE @TableName VARCHAR (50)
DECLARE @BatchSize int
DECLARE @ArchiveYear CHAR (4)
DECLARE @RowCount int
SET @TableName = 'xactControlPoint'
SET @BatchSize = (SELECT BatchSize FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = @TableName)
SET @ArchiveYear = (SELECT ArchiveYear FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = @TableName)
DECLARE @BeginDate Date
SET @BeginDate = (SELECT TOP 1 Call_date
FROM [PrismData].[dbo].[tblCall] tc)
DECLARE @Active_Indicator bit
SET @Active_Indicator =
COALESCE((SELECT 1 FROM PrismDataArchive.dbo.ArchiveDriver
WHERE Active_Indicator = 1 AND TableName = @TableName),0)
SELECT @Active_Indicator AS Active_Indicator,@TableName AS TableName
--SET IDENTITY_INSERT xactControlPoint ON
WHILE @BatchSize > 0 AND @TableName = 'xactControlPoint' AND @Active_Indicator = 1
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
BEGIN
DELETE TOP (@BatchSize) FROM tc
OUTPUT DELETED.* INTO PrismDataArchive.dbo.xactControlPointArchive
FROM [PrismData].[dbo].[xactControlPoint] tc
WHERE tc.BeginDate >= DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0)
AND tc.BeginDate < DATEADD(yy,1,DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0))
END
SET @BatchSize = @@ROWCOUNT; -- capture number of rows affected
IF @BatchSize > 0
BEGIN
INSERT INTO ArchiveLog (TableName,ArchiveYear, BatchSize)
VALUES (@TableName,@ArchiveYear,@BatchSize)
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH
END
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 23, 2015 at 9:44 am
When I close the Window I get a popup that there are open transaction.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 23, 2015 at 10:45 am
I'm rewriting it.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply