Sql – How to fix mismatching number of BEGIN and COMMIT statements

sqlsql serversql-server-2008

I am getting this error:

Transaction count after EXECUTE indicates a mismatching number of
BEGIN and COMMIT statements. Previous count = 2, current count = 3.

But I don't know enough about SQL Server to stop the error.

Here is my DROP PROCEDURE command:

--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

And here is the 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

And finally I execute as follows:

USE SalesLogix_Dev
GO

EXEC usp_matt_db_tasks;

Best Answer

To drop all the procedures with the name usp_matt_db_tasks you can run this query:

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;

You should rollback any transactions that are currently active, close your current window, then create procedure in a new query window:

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

Now when you call this procedure you should always use EXEC dbo.usp_matt_db_tasks; and you should probably consider adding some error handling so that you can properly rollback the transaction in case something goes wrong.

Related Topic