联机帮助上的原话:错误 266
严重级别 16
消息正文
EXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。原计数 = %1!,当前计数 = %2!。解释
如果某存储过程退出时其 @@TRANCOUNT 值与进入该存储过程时不同,则 Microsoft® SQL Server™ 返回错误 266。说明 该错误可忽略,因为它只将消息发送到客户端而不影响执行。
严重级别 16
消息正文
EXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。原计数 = %1!,当前计数 = %2!。解释
如果某存储过程退出时其 @@TRANCOUNT 值与进入该存储过程时不同,则 Microsoft® SQL Server™ 返回错误 266。说明 该错误可忽略,因为它只将消息发送到客户端而不影响执行。
存在可以使存储过程不出现该错误的解决方案。以下是一组解决方案,每种方案都带有示例代码: 从相同的存储过程嵌套级(事务开始处)执行最终的 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句,如下例所示:
-- Example 1.a
CREATE PROCEDURE test1a
AS
SELECT @@TRANCOUNT
GO
BEGIN TRANSACTION
EXECUTE test1a
ROLLBACK TRANSACTION
GO
-- Example 1.b
CREATE PROCEDURE test1c
AS
SELECT @@TRANCOUNT
GO
CREATE PROCEDURE test1b
AS
BEGIN TRANSACTION
EXEC test1c
COMMIT TRANSACTION
GO
EXECUTE test1b
GO如果嵌套事务用于存储过程中,则执行匹配的提交。
说明 在 @@TRANCOUNT 等于 0(零)之前不提交事务。-- Example 2
CREATE PROCEDURE test2b
AS
SELECT @@TRANCOUNT
BEGIN TRANSACTION
SELECT @@TRANCOUNT
COMMIT TRANSACTION
SELECT @@TRANCOUNT
GO
CREATE PROCEDURE test2a
AS
BEGIN TRANSACTION
EXECUTE test2b
COMMIT TRANSACTION
GO
EXECUTE test2a
GO如果需要回滚,并且存储过程嵌套级与事务开始处不同,则以有效的用户定义错误使用 RAISERROR,并在 EXECUTE 语句之后检查 @@ERROR 函数。
-- Example 3
USE master
EXECUTE sp_addmessage 50001, 16, 'Rollback of transaction in test3'
GO
CREATE PROCEDURE test3
AS
RAISERROR (50001,16,1)
GO
BEGIN TRANSACTION
EXEC test3
IF @@error <> 50001
BEGIN
PRINT 'Commit'
COMMIT TRANSACTION
END
ELSE
BEGIN
PRINT 'Rollback'
ROLLBACK TRANSACTION
END
GO 该规则的例外是,如果某触发器执行回滚,则 @@TRANCOUNT 不需要匹配其起始值,因为批处理已终止。但是,如果由触发器调用的存储过程终止了事务,则可能导致该问题。
-- Example 4
CREATE TABLE x (col1 int)
GO
CREATE TRIGGER xins
ON x
FOR INSERT AS
ROLLBACK TRANSACTION
GO
CREATE PROCEDURE sp_xinsert
AS
SELECT @@TRANCOUNT
INSERT x (col1) VALUES (1)
SELECT @@TRANCOUNT
GO
BEGIN TRANSACTION
EXECUTE sp_xinsert
IF @@error <> 0
BEGIN
PRINT 'Commit'
COMMIT TRANSACTION
END
ELSE
BEGIN
PRINT 'Rollback'
ROLLBACK TRANSACTION
END
GO
SELECT *
FROM x
-- 看下面的测试USE tempdb
GOCREATE TABLE dbo.tb(id int primary key)
INSERT dbo.tb VALUES(1)
GOCREATE PROC pro_1
AS
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
INSERT dbo.tb VALUES(2)
EXEC pro_2
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage
END CATCH
GOCREATE PROC pro_2
AS
BEGIN TRY
BEGIN TRAN
INSERT dbo.tb VALUES(1)
COMMIT TRAN
END TRY
BEGIN CATCH
-- IF @@TRANCOUNT > 0
-- ROLLBACK TRAN
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200); SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE(); RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);
END CATCH
GOEXEC pro_1
SELECT * FROM dbo.tb
GODROP TABLE dbo.tb
DROP PROC pro_1, pro_2