SQL SERVER 2005的一个事务错误，高手们帮忙

Question

在C#程序里执行以后报错：
在 EXECUTE后的事务计数指示缺少了COMMIT和ROLLBACK TRANSACTION 语句。上一计数=1，当前计数=0。过程如下：ALTER PROCEDURE [equ].[SP_GEN_EXAMREC]  
    @P_PERID float(53),
 
   @P_DJTYPE float(53),
    @P_TYPE float(53),
   @P_DATE varchar(max)
AS 
 
   BEGIN      DECLARE
         @dt_date datetime, 
  
         @n_per_id float(53)      
      /*
      *   
      *   　功  能：  根据点检初始化表和点检日期生成点检记录
      *   　编写者:   吕海江
      *   　编写时间：2007-10-14  
      *
      */
      SET @dt_date = sysdb.ssma_oracle.to_date2(@p_date, 'yyyy-mm-dd')      SET @n_per_id = @p_perid      DECLARE
         /*
         *   SSMA warning messages:
         *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
         */         @c_cu$id float(53), 
         @c_cu$cyc_id float(53), 
         @c_cu$exam_time datetime,          @c_cu$day_num float(53)      /*取点检初始化信息，并且是没有在点检实施记录中生成过的信息*/
      DECLARE
          cu CURSOR LOCAL FORWARD_ONLY FOR 
            SELECT a.ID, a.CYC_ID, sysdb.ssma_oracle.trunc_date(a.EXAM_TIME) AS exam_time, b.DAY_NUM
            FROM equ.EXAM_OPER_LIST  AS a, pub.CYC  AS b
            WHERE 
               a.CYC_ID = b.ID AND 
               a.EXAM_TYPE = @P_DJTYPE AND 
               a.EQU_ID IN 
               (
                  SELECT EQU.ID
                  FROM pub.EQU
                  WHERE EQU.EQU_SUBGRP_ID = @p_type AND EQU.USED = 'Y'
               ) AND 
               a.ID NOT IN 
               (
                  SELECT EXAM_OPER_REC.EXAM_OPER_LIST_ID
                  FROM equ.EXAM_OPER_REC
                  WHERE EXAM_OPER_REC.EXAM_TIME = @dt_date
               )
            ORDER BY a.ID      OPEN cu      WHILE 1 = 1
      
         BEGIN            FETCH cu
                INTO @c_cu$id, @c_cu$cyc_id, @c_cu$exam_time, @c_cu$day_num            IF @@FETCH_STATUS = -1
               BREAK 
            INSERT equ.EXAM_OPER_REC(
  
               EXAM_OPER_LIST_ID/*点检计划id*/, 
               EXAM_VALUE/*点检值*/, 
               SYSTIME/*点检记录时间*/, 
               REC_PER_ID/*记录人*/, 
               FINISH_STATE/*点检完成状态*/, 
               EXAM_RES/*点检结果*/, 
               EQU_ID/*设备*/, 
               EXAM_ITEM_ID/*点检项目id*/, 
               EXAM_CONTENT_ID/*点检内容id*/, 
               EXAM_STATE/*点检状态*/, 
               CYC_ID/*周期*/, 
               BENCH_NAME/*点检基准*/, 
               EXAM_TYPE/*点检类别*/, 
               EXAM_LEVEL/*点检级别*/, 
               POST_ID/*岗位*/, 
               PER_ID/*点检责任人*/, 
               EXAM_TIME/*点检时间*/)
               SELECT 
                   EXAM_OPER_LIST.ID, 
                  NULL, 
                  @dt_date, 
                  @n_per_id, 
                  '0', 
                  '1', 
                  EXAM_OPER_LIST.EQU_ID, 
                  EXAM_OPER_LIST.EXAM_ITEM_ID, 
                  EXAM_OPER_LIST.EXAM_CONTENT_ID, 
                  EXAM_OPER_LIST.EXAM_STATE, 
                  EXAM_OPER_LIST.CYC_ID, 
                  EXAM_OPER_LIST.BENCH_NAME, 
                  EXAM_OPER_LIST.EXAM_TYPE, 
                  EXAM_OPER_LIST.EXAM_LEVEL, 
                  EXAM_OPER_LIST.POST_ID, 
                  EXAM_OPER_LIST.PER_ID, 
                  @dt_date
               FROM equ.EXAM_OPER_LIST
               WHERE EXAM_OPER_LIST.ID = @c_cu$id/*      end if;*/        END      CLOSE cu      DEALLOCATE cu      IF @@TRANCOUNT > 0
         COMMIT WORK 
 
   END

Answer 1

http://topic.csdn.net/t/20060802/20/4922893.html
和你同样的问题

Answer 2

  错误   266   
  严重级别   16   
  消息正文   
  EXECUTE   后的事务计数指出缺少了   COMMIT   或   ROLLBACK   TRANSACTION   语句。原计数   =   %1!，当前计数   =   %2!。   
    
  解释   
  如果某存储过程退出时其   @@TRANCOUNT   值与进入该存储过程时不同，则   Microsoft®   SQL   Server™   返回错误   266。   
    
    
    
  说明     该错误可忽略，因为它只将消息发送到客户端而不影响执行。  

Answer 3

你只有COMMIT 没有 BEGIN TRAN啊。ALTER PROCEDURE [equ].[SP_GEN_EXAMREC]  
    @P_PERID float(53),
 
   @P_DJTYPE float(53),
    @P_TYPE float(53),
   @P_DATE varchar(max)
AS 
 
   BEGIN      DECLARE
         @dt_date datetime, 
  
         @n_per_id float(53)      
      /*
      *   
      *   　功  能：  根据点检初始化表和点检日期生成点检记录
      *   　编写者:   吕海江
      *   　编写时间：2007-10-14  
      *
      */
      SET @dt_date = sysdb.ssma_oracle.to_date2(@p_date, 'yyyy-mm-dd')      SET @n_per_id = @p_perid      DECLARE
         /*
         *   SSMA warning messages:
         *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
         */         @c_cu$id float(53), 
         @c_cu$cyc_id float(53), 
         @c_cu$exam_time datetime,          @c_cu$day_num float(53)      /*取点检初始化信息，并且是没有在点检实施记录中生成过的信息*/
      DECLARE
          cu CURSOR LOCAL FORWARD_ONLY FOR 
            SELECT a.ID, a.CYC_ID, sysdb.ssma_oracle.trunc_date(a.EXAM_TIME) AS exam_time, b.DAY_NUM
            FROM equ.EXAM_OPER_LIST  AS a, pub.CYC  AS b
            WHERE 
               a.CYC_ID = b.ID AND 
               a.EXAM_TYPE = @P_DJTYPE AND 
               a.EQU_ID IN 
               (
                  SELECT EQU.ID
                  FROM pub.EQU
                  WHERE EQU.EQU_SUBGRP_ID = @p_type AND EQU.USED = 'Y'
               ) AND 
               a.ID NOT IN 
               (
                  SELECT EXAM_OPER_REC.EXAM_OPER_LIST_ID
                  FROM equ.EXAM_OPER_REC
                  WHERE EXAM_OPER_REC.EXAM_TIME = @dt_date
               )
            ORDER BY a.ID      OPEN cu      WHILE 1 = 1
      
         BEGIN            FETCH cu
                INTO @c_cu$id, @c_cu$cyc_id, @c_cu$exam_time, @c_cu$day_num            IF @@FETCH_STATUS = -1
               BREAK 
            INSERT equ.EXAM_OPER_REC(
  
               EXAM_OPER_LIST_ID/*点检计划id*/, 
               EXAM_VALUE/*点检值*/, 
               SYSTIME/*点检记录时间*/, 
               REC_PER_ID/*记录人*/, 
               FINISH_STATE/*点检完成状态*/, 
               EXAM_RES/*点检结果*/, 
               EQU_ID/*设备*/, 
               EXAM_ITEM_ID/*点检项目id*/, 
               EXAM_CONTENT_ID/*点检内容id*/, 
               EXAM_STATE/*点检状态*/, 
               CYC_ID/*周期*/, 
               BENCH_NAME/*点检基准*/, 
               EXAM_TYPE/*点检类别*/, 
               EXAM_LEVEL/*点检级别*/, 
               POST_ID/*岗位*/, 
               PER_ID/*点检责任人*/, 
               EXAM_TIME/*点检时间*/)
               SELECT 
                   EXAM_OPER_LIST.ID, 
                  NULL, 
                  @dt_date, 
                  @n_per_id, 
                  '0', 
                  '1', 
                  EXAM_OPER_LIST.EQU_ID, 
                  EXAM_OPER_LIST.EXAM_ITEM_ID, 
                  EXAM_OPER_LIST.EXAM_CONTENT_ID, 
                  EXAM_OPER_LIST.EXAM_STATE, 
                  EXAM_OPER_LIST.CYC_ID, 
                  EXAM_OPER_LIST.BENCH_NAME, 
                  EXAM_OPER_LIST.EXAM_TYPE, 
                  EXAM_OPER_LIST.EXAM_LEVEL, 
                  EXAM_OPER_LIST.POST_ID, 
                  EXAM_OPER_LIST.PER_ID, 
                  @dt_date
               FROM equ.EXAM_OPER_LIST
               WHERE EXAM_OPER_LIST.ID = @c_cu$id/*      end if;*/        END      CLOSE cu      DEALLOCATE cu   END

Answer 4

SQL2005的事务写法用begin try
begin tran
..
commit tran
end try
begin catch
rollback tran
raiserror 50005 N'操作失败'
end catch

Answer 5

楼主的语句没有启用事务WORK05实现方法:
A. 使用 TRY…CATCH
此示例显示的 SELECT 语句将生成被零除错误。该错误会导致跳转至相关 CATCH 块的执行。 复制代码 
USE AdventureWorks;
GOBEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    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;
GO
 B. 在事务内使用 TRY…CATCH
此示例显示 TRY…CATCH 块如何在事务内工作。TRY 块内的语句会生成违反约束的错误。 复制代码 
USE AdventureWorks;
GO
BEGIN TRANSACTION;BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
        WHERE ProductID = 980;
END TRY
BEGIN CATCH
    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;    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO
 C. 将 TRY…CATCH 与 XACT_STATE 配合使用
此示例显示如何使用 TRY…CATCH 构造来处理事务内发生的错误。XACT_STATE 函数确定应提交事务还是应回滚事务。在本示例中，SET XACT_STATE 为 ON，在发生违反约束的错误时，这会使事务处于不可提交状态。 复制代码 
USE AdventureWorks;
GO-- Check to see if this stored procedure exists.
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL
    DROP PROCEDURE usp_GetErrorInfo;
GO-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_LINE () as ErrorLine,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_MESSAGE() as ErrorMessage;
GO-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs. 
SET XACT_ABORT ON;BEGIN TRY
    BEGIN TRANSACTION;
        -- A foreign key constrain exists on this table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;    -- If the DELETE statement succeeds, commit the transaction.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;    -- Test XACT_STATE:
        -- If 1, the transaction is committable.
        -- If -1, the transaction is uncommittable and should 
        --     be rolled back.
        -- XACT_STATE = 0 means that there is no transaction and
        --     a COMMIT or ROLLBACK would generate an error.    -- Test if the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state.' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;    -- Test if the transaction is committable
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT
            N'The transaction is committable.' +
            'Committing transaction.'
        COMMIT TRANSACTION;   
    END;
END CATCH;
GO