如何修复开始语句和提交语句的错误匹配数？

Question

我得到了这个错误：

执行后的

事务计数表示开始语句和提交语句的数目不匹配。以前计数= 2，当前计数= 3。

但是，我对SQL Server还不太了解，无法阻止这个错误。

下面是我的DROP PROCEDURE命令：

--Specify database in which to uninstall procedure
USE SalesLogix_Dev
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'usp_matt_db_tasks')
            AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE usp_matt_db_tasks
GO

下面是CREATE PROCEDURE：

--Specify database in which to install procedure
USE SalesLogix_Dev
GO

--Drop existing objects in order to guanrantee error-free install
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'usp_matt_db_tasks')
            AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE usp_matt_db_tasks
GO


CREATE PROCEDURE usp_matt_db_tasks
    -- Add the parameters for the stored procedure here

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    BEGIN TRANSACTION
INSERT INTO [SalesLogix_Dev].[sysdba].[LEAD] (
    CREATEUSER,
    CREATEDATE,
    MODIFYUSER,
    MODIFYDATE,
    FIRSTNAME,
    ACCOUNTMANAGERID,
    ASSIGNDATE,
    COMPANY,
    COMPANY_UC,
    EMAIL,
    DONOTSOLICIT,
    ISPRIMARY,
    LEADSOURCEID,
    SECCODEID,
    STATUS,
    LASTNAME,
    LASTNAME_UC,
    INDUSTRY,
    NOTES,
    HOMEPHONE) 
SELECT 
       ,'something'
       ,CURRENT_TIMESTAMP
       ,'something'    
       ,CURRENT_TIMESTAMP
       ,replace(firstname, '"', '')
       ,'something'
       ,CURRENT_TIMESTAMP
       ,replace(company, '"', '')
       ,replace(UPPER(company), '"', '')
       ,replace(email, '"', '')
       ,'1'
       ,'T'
       ,''
       ,'SYST00000001'
       ,'New'
       ,replace(lastname, '"', '')
       ,replace(UPPER(lastname), '"', '')
       ,replace(department, '"', '')
       ,replace(comments, '"', '')
       ,replace(phone, '"', '')

  FROM [SalesLogix_Dev].[sysdba].[CSVTemp]

update  [SalesLogix_Dev].[sysdba].[LEAD] set LEAD_ADDRESSID = 'Q' + LEADID where DONOTSOLICIT = 1

INSERT INTO [SalesLogix_Dev].[sysdba].[LEAD_ADDRESS] (
    LEAD_ADDRESSID,
    LEADID,
    CREATEUSER,
    CREATEDATE,
    MODIFYUSER,
    MODIFYDATE,
    ISMAILING,
    ISPRIMARY) 
SELECT 
      LEAD_ADDRESSID
     ,LEADID
     ,'something'
     ,CURRENT_TIMESTAMP
     ,'something'      
     ,CURRENT_TIMESTAMP
     ,'T'
     ,'T'

  FROM [SalesLogix_Dev].[sysdba].[LEAD] where DONOTSOLICIT = 1

  update  [SalesLogix_Dev].[sysdba].[LEAD] set DONOTSOLICIT = 0 where DONOTSOLICIT = 1
  DROP TABLE [SalesLogix_Dev].[sysdba].[CSVTemp]
  ROLLBACK  TRANSACTION
COMMIT TRANSACTION

END

最后，我执行如下操作：

USE SalesLogix_Dev
GO

EXEC usp_matt_db_tasks;

Answer 1

若要删除名称为usp_matt_db_tasks的所有过程，可以运行以下查询：

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += 'DROP PROCEDURE ' 
    + SCHEMA_ID(name) + '.'
    + QUOTENAME(name) + ';'
    FROM sys.procedures WHERE name = 'usp_matt_db_tasks';

EXEC sp_executesql @sql;

应回滚当前活动的任何事务，关闭当前窗口，然后在新的查询窗口中创建过程：

CREATE PROCEDURE dbo.usp_matt_db_tasks
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    INSERT INTO [SalesLogix_Dev].[sysdba].[LEAD] 
    (
      CREATEUSER,
      ...
      HOMEPHONE
    ) 
    SELECT 
          ,'something'
          ...
          ,replace(phone, '"', '')
    FROM [SalesLogix_Dev].[sysdba].[CSVTemp];

    UPDATE [SalesLogix_Dev].[sysdba].[LEAD] 
      SET LEAD_ADDRESSID = 'Q' + LEADID 
      WHERE DONOTSOLICIT = 1;

    INSERT INTO [SalesLogix_Dev].[sysdba].[LEAD_ADDRESS] 
    (
      LEAD_ADDRESSID,
              ...
      ISPRIMARY
    ) 
    SELECT 
      LEAD_ADDRESSID
              ,...
      ,'T'
    FROM [SalesLogix_Dev].[sysdba].[LEAD]
    WHERE DONOTSOLICIT = 1;

    UPDATE [SalesLogix_Dev].[sysdba].[LEAD] 
      SET DONOTSOLICIT = 0 
      WHERE DONOTSOLICIT = 1;

    DROP TABLE [SalesLogix_Dev].[sysdba].[CSVTemp];

    COMMIT TRANSACTION;
END
GO

现在，当您调用此过程时，您应该始终使用EXEC dbo.usp_matt_db_tasks;，并且您可能应该考虑添加一些错误处理，以便在发生错误时能够正确回滚事务。

Answer 2

看来您正在回滚事务，然后尝试提交。

两者都应该结束一个“开始事务”。