Link to home
Start Free TrialLog in
Avatar of matrix_aash
matrix_aashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Transaction

Hi All,

I want to write down a transaction for the following t-sql query so that if any of the statements fail it should rollback the whole stored procedure and do nothing.

Please find below the sample code:

As you can see in tbe attached Code.

If statment drop the table and then inserts data into the same table and after that there are 5 different insert statement.

I need a transaction so that if insert statement 3 failes it will roll back everything. So it will rollback insert statment 2 ,insert statement 1 and the drop table statment, same way if insert statement 4 fails than it should rollback everything prior to that and if no errors it should commit the transaction.

Thanks in advance.

Cheers,


begin transaction
BEGIN TRANSACTION
IF  EXISTS (select name from sysobjects where xtype='u' and name = 'tablename')
 DROP TABLE [dbo].tablename
 SELECT * INTO tablename
 FROM 
tablename1
 
INSERT STATEMENT 1
 
INSERT STATEMENT 2
 
INSERT STATEMENT 3
 
INSERT STATEMENT 4
 
INSERT STATEMENT 5

Open in new window

Avatar of chapmandew
chapmandew
Flag of United States of America image
try this

begin try

BEGIN TRANSACTION
IF  EXISTS (select name from sysobjects where xtype='u' and name = 'tablename')
 DROP TABLE [dbo].tablename
 SELECT * INTO tablename
 FROM
tablename1
 
INSERT STATEMENT 1
 
INSERT STATEMENT 2
 
INSERT STATEMENT 3
 
INSERT STATEMENT 4
 
INSERT STATEMENT 5

commit tran
end try
begin catch
rollback tran
select error_description()
end catch
Avatar of Mark Wills
Mark Wills
Flag of Australia image
That looks vaguely familiar...

The try / catch block is arguably one of the best things for T-SQL


BEGIN TRY
     BEGIN TRANSACTION
          INSERT                                           -- see below
      COMMIT TRANSACTION
END TRY
BEGIN CATCH
       ROLLBACK TRANSACTION
       -- do error handling
END CATCH

--- the only comment here is if there are dependancies, then might want to check @@rowcount before continuing - especially if inserts are stored procedures where logic errors (rather than hard errors) might prevent and insert (e.g. if date < getdate() insert ... else set @err = 1 )
Avatar of matrix_aash
matrix_aash
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

That does not work. Because it says after the select * into tablename from tablename statement it says 5043 rows affected than it failed on the first statement which I wanted to but the transaction did not commit and said that there are uncommited transaction ?

Please find below the error message:

(5043 row(s) affected)
Msg 208, Level 16, State 1, Procedure CRMSubscriptionsV1Job, Line 14
Invalid object name 'CRMSUBSCRIPTIONSDATAMATRICES'.
Msg 266, Level 16, State 2, Procedure CRMSubscriptionsV1Job, Line 14
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

I have come across this one before but just cannot get my head round it.

Thanks in advance.
Avatar of chapmandew
chapmandew
Flag of United States of America image
your first procedure call, is there a transaction in there as well?
Avatar of matrix_aash
matrix_aash
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

this is what I have.

I asked it to fail at insert statment 2 so that I can see whether the transaction is rollbacked or not and I ended up with the above error. Transaction was not finished and I had to end it manually.

BEGIN TRANSACTION
IF  EXISTS (select name from sysobjects where xtype='u' and name = 'tablename')
 DROP TABLE [dbo].tablename
 SELECT * INTO tablename
 FROM 
tablename1
 
INSERT STATEMENT 1
 
INSERT STATEMENT 2
 
INSERT STATEMENT 3
 
INSERT STATEMENT 4
 
INSERT STATEMENT 5
 
commit tran
end try
begin catch
rollback tran
select error_description()
end catch

Open in new window

Avatar of Mark Wills
Mark Wills
Flag of Australia image
Can we see the actual procedure ?

Are there dependencies from one statement to the next ?
Avatar of matrix_aash
matrix_aash
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Here you go...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CRMSubscriptionsV1Job]
AS
BEGIN TRY
BEGIN TRANSACTION
IF  EXISTS (select name from sysobjects where xtype='u' and name = 'CRMSubscriptionsV1')
 DROP TABLE [dbo].[CRMSubscriptionsV1]
 SELECT * INTO CRMSubscriptionsV1
 FROM 
[V_ACL_V1Subscriptions]
 
--################Date Matrices###########################
 
INSERT INTO [CRMSubscriptionsDataMatrices](USER_LOGONNAME,USER_PASSWORD,PRODUCT_MODULENAME,ORDER_ORDERPRODUCTID,
[Equipment_ID],[Equipment_Name],[EquipmentGroup_ID],[EquipmentGroup_Name],
[Equipment_Manufacturer],[Equipment_Series],[Equipment_Type],[Options_FullLifeBase],
[Options_HalfLifeBase],	[Options_CurrentValues],[Options_FutureValues],[Options_HistoricalValueFirstQuarter],
[Options_HistoricalValueSecondQuarter],[Options_HistoricalValueThirdQuarter],[Options_HistoricalValueFourthQuarter],
[Options_LeaseRate],[Options_MarketValue])
SELECT USER_LOGONNAME,USER_PASSWORD,PRODUCT_MODULENAME,ORDER_ORDERPRODUCTID,
[Equipment_ID],[Equipment_Name],[EquipmentGroup_ID],[EquipmentGroup_Name],
[Equipment_Manufacturer],[Equipment_Series],[Equipment_Type],[Options_FullLifeBase],
[Options_HalfLifeBase],	[Options_CurrentValues],[Options_FutureValues],[Options_HistoricalValueFirstQuarter],
[Options_HistoricalValueSecondQuarter],[Options_HistoricalValueThirdQuarter],[Options_HistoricalValueFourthQuarter],
[Options_LeaseRate],[Options_MarketValue]
FROM CRMSUBSCRIPTIONSV1 WHERE CRMSUBSCRIPTIONSV1.PRODUCT_MODULENAME = 'DATA MATRICES' AND
[Equipment_Manufacturer] IS NOT NULL AND [Equipment_Series] IS NOT NULL AND [Equipment_Type]
IS NOT NULL AND NOT EXISTS
(SELECT USER_LOGONNAME,USER_PASSWORD,PRODUCT_MODULENAME,ORDER_ORDERPRODUCTID,
[Equipment_ID],[Equipment_Name],[EquipmentGroup_ID],[EquipmentGroup_Name],
[Equipment_Manufacturer],[Equipment_Series],[Equipment_Type],[Options_FullLifeBase],
[Options_HalfLifeBase],	[Options_CurrentValues],[Options_FutureValues],[Options_HistoricalValueFirstQuarter],
[Options_HistoricalValueSecondQuarter],[Options_HistoricalValueThirdQuarter],[Options_HistoricalValueFourthQuarter],
[Options_LeaseRate],[Options_MarketValue]
 FROM CRMSUBSCRIPTIONSDATAMATRICES WHERE 
CRMSUBSCRIPTIONSV1.USER_LOGONNAME = [CRMSubscriptionsDataMatrices].USER_LOGONNAME COLLATE DATABASE_DEFAULT AND 
CRMSUBSCRIPTIONSV1.USER_PASSWORD = [CRMSubscriptionsDataMatrices].USER_PASSWORD COLLATE DATABASE_DEFAULT AND 
CRMSUBSCRIPTIONSV1.PRODUCT_MODULENAME = CRMSUBSCRIPTIONSDATAMATRICES.[product_modulename]COLLATE DATABASE_DEFAULT AND
CRMSUBSCRIPTIONSV1.ORDER_ORDERPRODUCTID = [CRMSubscriptionsDataMatrices].ORDER_ORDERPRODUCTID AND 
CRMSUBSCRIPTIONSV1.[Equipment_Name] = CRMSUBSCRIPTIONSDATAMATRICES.[Equipment_Name]COLLATE DATABASE_DEFAULT AND
CRMSUBSCRIPTIONSV1.[EquipmentGroup_Name] = CRMSUBSCRIPTIONSDATAMATRICES.[EquipmentGroup_Name]COLLATE DATABASE_DEFAULT AND
CRMSUBSCRIPTIONSV1.[Equipment_Manufacturer] = CRMSUBSCRIPTIONSDATAMATRICES.[Equipment_Manufacturer]COLLATE DATABASE_DEFAULT AND
CRMSUBSCRIPTIONSV1.[Equipment_Series] = CRMSUBSCRIPTIONSDATAMATRICES.[Equipment_Series]COLLATE DATABASE_DEFAULT AND
CRMSUBSCRIPTIONSV1.[Equipment_Type] = CRMSUBSCRIPTIONSDATAMATRICES.[Equipment_Type]COLLATE DATABASE_DEFAULT AND
CRMSUBSCRIPTIONSV1.[Options_FullLifeBase] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_FullLifeBase] AND
CRMSUBSCRIPTIONSV1.[Options_HalfLifeBase] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HalfLifeBase] AND
CRMSUBSCRIPTIONSV1.[Options_CurrentValues] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_CurrentValues] AND
CRMSUBSCRIPTIONSV1.[Options_FutureValues] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_FutureValues] AND
CRMSUBSCRIPTIONSV1.[Options_HistoricalValueFirstQuarter] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HistoricalValueFirstQuarter] AND
CRMSUBSCRIPTIONSV1.[Options_HistoricalValueSecondQuarter] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HistoricalValueSecondQuarter] AND
CRMSUBSCRIPTIONSV1.[Options_HistoricalValueThirdQuarter] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HistoricalValueThirdQuarter] AND
CRMSUBSCRIPTIONSV1.[Options_HistoricalValueFourthQuarter] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HistoricalValueFourthQuarter] AND
CRMSUBSCRIPTIONSV1.[Options_LeaseRate] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_LeaseRate]AND
CRMSUBSCRIPTIONSV1.[Options_MarketValue] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_MarketValue])
 
--########################Insert Data on Binary Basis########################
 
 
INSERT INTO [DATAMATRICESBINARYDATA_GROUP]([EquipmentGroup_ID],[EquipmentGroup_Name],User_Logonname)
SELECT  DISTINCT [EquipmentGroup_ID],[EquipmentGroup_Name],user_logonname
FROM CRMSUBSCRIPTIONSDATAMATRICES WHERE NOT EXISTS
(SELECT [EquipmentGroup_ID],[EquipmentGroup_Name],user_logonname
 FROM DATAMATRICESBINARYDATA_GROUP WHERE 
DATAMATRICESBINARYDATA_GROUP.[EquipmentGroup_ID] = [CRMSubscriptionsDataMatrices].[EquipmentGroup_ID] AND 
DATAMATRICESBINARYDATA_GROUP.[EquipmentGroup_Name] = [CRMSubscriptionsDataMatrices].[EquipmentGroup_Name] COLLATE DATABASE_DEFAULT and
DATAMATRICESBINARYDATA_GROUP.user_logonname = [CRMSubscriptionsDataMatrices].user_logonname COLLATE DATABASE_DEFAULT)
 
 
--#####################Hits data##############################################
 
 
INSERT INTO CRMSUBSCRIPTIONSV1_HITS(USER_LOGONNAME, USER_PASSWORD , 
ORDER_ORDERPRODUCTID, ORDER_HITSPURCHASED)
SELECT DISTINCT USER_LOGONNAME,USER_PASSWORD,ORDER_ORDERPRODUCTID,ORDER_HITSPURCHASED
FROM CRMSUBSCRIPTIONSV1 WHERE CRMSUBSCRIPTIONSV1.PRODUCT_ISHITBASED = '1' AND NOT EXISTS
(SELECT USER_LOGONNAME,USER_PASSWORD,ORDER_ORDERPRODUCTID,ORDER_HITSPURCHASED
 FROM CRMSUBSCRIPTIONSV1_HITS WHERE 
CRMSUBSCRIPTIONSV1.USER_LOGONNAME = CRMSUBSCRIPTIONSV1_HITS.USER_LOGONNAME COLLATE DATABASE_DEFAULT AND 
CRMSUBSCRIPTIONSV1.USER_PASSWORD = CRMSUBSCRIPTIONSV1_HITS.USER_PASSWORD COLLATE DATABASE_DEFAULT AND 
CRMSUBSCRIPTIONSV1.ORDER_ORDERPRODUCTID  = CRMSUBSCRIPTIONSV1_HITS.ORDER_ORDERPRODUCTID AND 
CRMSUBSCRIPTIONSV1.ORDER_HITSPURCHASED = CRMSUBSCRIPTIONSV1_HITS.ORDER_HITSPURCHASED)
GROUP BY ORDER_ORDERPRODUCTID,USER_LOGONNAME,USER_PASSWORD,ORDER_HITSPURCHASED
 
-----------------------------------------------
 
UPDATE [CRMSUBSCRIPTIONSV1_Hits]
SET [Product_HitsLeft] = [Order_HitsPurchased]
WHERE [Product_HitsLeft] IS NULL
 
 
--##################Hits changed###################################################
 
 
INSERT INTO [CRMSUBSCRIPTIONSV1_AGREEDTC](USER_LOGONNAME, USER_PASSWORD)
SELECT DISTINCT USER_LOGONNAME,USER_PASSWORD
FROM CRMSUBSCRIPTIONSV1 WHERE NOT EXISTS
(SELECT USER_LOGONNAME,USER_PASSWORD
FROM [CRMSUBSCRIPTIONSV1_AGREEDTC] WHERE 
CRMSUBSCRIPTIONSV1.USER_LOGONNAME = [CRMSUBSCRIPTIONSV1_AGREEDTC].USER_LOGONNAME COLLATE DATABASE_DEFAULT AND 
CRMSUBSCRIPTIONSV1.USER_PASSWORD = [CRMSUBSCRIPTIONSV1_AGREEDTC].USER_PASSWORD COLLATE DATABASE_DEFAULT)
GROUP BY USER_LOGONNAME,USER_PASSWORD
 
--####################step 3 for v1################################################
 
 
INSERT INTO CRMSUBSCRIPTIONSV1_HITS_BACKUP(USER_LOGONNAME, USER_PASSWORD , 
ORDER_ORDERPRODUCTID, ORDER_HITSPURCHASED, PRODUCT_HITSLEFT)
SELECT USER_LOGONNAME, USER_PASSWORD , 
ORDER_ORDERPRODUCTID, ORDER_HITSPURCHASED, PRODUCT_HITSLEFT
FROM [CRMSUBSCRIPTIONSV1_Hits]
 
 
COMMIT TRANSACTION
 
END TRY
BEGIN CATCH
ROLLBACK
SELECT @@ERROR
END CATCH

Open in new window

Avatar of Mark Wills
Mark Wills
Flag of Australia image
OK there are some dependancies there...

Would be inclined to move the begin transaction / commit transactions down to the next level ie around each insert...

You do have a small challenge in so much as step 2 kind of depends on step 1, and really need to commit step 1 before we can use some of those data relationships in/for step 2. But are you trying to rollback step 1 as well as step 2 if there is an issue in step 2 ? Hope not... Otherwise we are going to have to unravel the logic and use / build up a temp area of some description, or, start nesting a little differently (or both).

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CRMSubscriptionsV1Job]
AS
 
BEGIN TRY
 
  IF  EXISTS (select name from sysobjects where xtype='u' and name = 'CRMSubscriptionsV1')
  DROP TABLE [dbo].[CRMSubscriptionsV1]
 
 
  BEGIN TRANSACTION
    SELECT * INTO CRMSubscriptionsV1
    FROM [V_ACL_V1Subscriptions]
  COMMIT TRANSACTION
 
--################Date Matrices###########################
 
  BEGIN TRANSACTION
    INSERT INTO [CRMSubscriptionsDataMatrices](USER_LOGONNAME,USER_PASSWORD,PRODUCT_MODULENAME,ORDER_ORDERPRODUCTID,
    [Equipment_ID],[Equipment_Name],[EquipmentGroup_ID],[EquipmentGroup_Name],
    [Equipment_Manufacturer],[Equipment_Series],[Equipment_Type],[Options_FullLifeBase],
    [Options_HalfLifeBase],	[Options_CurrentValues],[Options_FutureValues],[Options_HistoricalValueFirstQuarter],
    [Options_HistoricalValueSecondQuarter],[Options_HistoricalValueThirdQuarter],[Options_HistoricalValueFourthQuarter],
    [Options_LeaseRate],[Options_MarketValue])
    SELECT USER_LOGONNAME,USER_PASSWORD,PRODUCT_MODULENAME,ORDER_ORDERPRODUCTID,
    [Equipment_ID],[Equipment_Name],[EquipmentGroup_ID],[EquipmentGroup_Name],
    [Equipment_Manufacturer],[Equipment_Series],[Equipment_Type],[Options_FullLifeBase],
    [Options_HalfLifeBase],	[Options_CurrentValues],[Options_FutureValues],[Options_HistoricalValueFirstQuarter],
    [Options_HistoricalValueSecondQuarter],[Options_HistoricalValueThirdQuarter],[Options_HistoricalValueFourthQuarter],
    [Options_LeaseRate],[Options_MarketValue]
    FROM CRMSUBSCRIPTIONSV1 WHERE CRMSUBSCRIPTIONSV1.PRODUCT_MODULENAME = 'DATA MATRICES' AND
    [Equipment_Manufacturer] IS NOT NULL AND [Equipment_Series] IS NOT NULL AND [Equipment_Type]
    IS NOT NULL AND NOT EXISTS
    (SELECT USER_LOGONNAME,USER_PASSWORD,PRODUCT_MODULENAME,ORDER_ORDERPRODUCTID,
    [Equipment_ID],[Equipment_Name],[EquipmentGroup_ID],[EquipmentGroup_Name],
    [Equipment_Manufacturer],[Equipment_Series],[Equipment_Type],[Options_FullLifeBase],
    [Options_HalfLifeBase],	[Options_CurrentValues],[Options_FutureValues],[Options_HistoricalValueFirstQuarter],
    [Options_HistoricalValueSecondQuarter],[Options_HistoricalValueThirdQuarter],[Options_HistoricalValueFourthQuarter],
    [Options_LeaseRate],[Options_MarketValue]
     FROM CRMSUBSCRIPTIONSDATAMATRICES WHERE 
    CRMSUBSCRIPTIONSV1.USER_LOGONNAME = [CRMSubscriptionsDataMatrices].USER_LOGONNAME COLLATE DATABASE_DEFAULT AND 
    CRMSUBSCRIPTIONSV1.USER_PASSWORD = [CRMSubscriptionsDataMatrices].USER_PASSWORD COLLATE DATABASE_DEFAULT AND 
    CRMSUBSCRIPTIONSV1.PRODUCT_MODULENAME = CRMSUBSCRIPTIONSDATAMATRICES.[product_modulename]COLLATE DATABASE_DEFAULT AND
    CRMSUBSCRIPTIONSV1.ORDER_ORDERPRODUCTID = [CRMSubscriptionsDataMatrices].ORDER_ORDERPRODUCTID AND 
    CRMSUBSCRIPTIONSV1.[Equipment_Name] = CRMSUBSCRIPTIONSDATAMATRICES.[Equipment_Name]COLLATE DATABASE_DEFAULT AND
    CRMSUBSCRIPTIONSV1.[EquipmentGroup_Name] = CRMSUBSCRIPTIONSDATAMATRICES.[EquipmentGroup_Name]COLLATE DATABASE_DEFAULT AND
    CRMSUBSCRIPTIONSV1.[Equipment_Manufacturer] = CRMSUBSCRIPTIONSDATAMATRICES.[Equipment_Manufacturer]COLLATE DATABASE_DEFAULT AND
    CRMSUBSCRIPTIONSV1.[Equipment_Series] = CRMSUBSCRIPTIONSDATAMATRICES.[Equipment_Series]COLLATE DATABASE_DEFAULT AND
    CRMSUBSCRIPTIONSV1.[Equipment_Type] = CRMSUBSCRIPTIONSDATAMATRICES.[Equipment_Type]COLLATE DATABASE_DEFAULT AND
    CRMSUBSCRIPTIONSV1.[Options_FullLifeBase] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_FullLifeBase] AND
    CRMSUBSCRIPTIONSV1.[Options_HalfLifeBase] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HalfLifeBase] AND
    CRMSUBSCRIPTIONSV1.[Options_CurrentValues] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_CurrentValues] AND
    CRMSUBSCRIPTIONSV1.[Options_FutureValues] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_FutureValues] AND
    CRMSUBSCRIPTIONSV1.[Options_HistoricalValueFirstQuarter] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HistoricalValueFirstQuarter] AND
    CRMSUBSCRIPTIONSV1.[Options_HistoricalValueSecondQuarter] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HistoricalValueSecondQuarter] AND
    CRMSUBSCRIPTIONSV1.[Options_HistoricalValueThirdQuarter] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HistoricalValueThirdQuarter] AND
    CRMSUBSCRIPTIONSV1.[Options_HistoricalValueFourthQuarter] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HistoricalValueFourthQuarter] AND
    CRMSUBSCRIPTIONSV1.[Options_LeaseRate] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_LeaseRate]AND
    CRMSUBSCRIPTIONSV1.[Options_MarketValue] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_MarketValue])
  COMMIT TRANSACTION 
 
--########################Insert Data on Binary Basis########################
 
  BEGIN TRANSACTION 
    INSERT INTO [DATAMATRICESBINARYDATA_GROUP]([EquipmentGroup_ID],[EquipmentGroup_Name],User_Logonname)
    SELECT  DISTINCT [EquipmentGroup_ID],[EquipmentGroup_Name],user_logonname
    FROM CRMSUBSCRIPTIONSDATAMATRICES WHERE NOT EXISTS
    (SELECT [EquipmentGroup_ID],[EquipmentGroup_Name],user_logonname
     FROM DATAMATRICESBINARYDATA_GROUP WHERE 
    DATAMATRICESBINARYDATA_GROUP.[EquipmentGroup_ID] = [CRMSubscriptionsDataMatrices].[EquipmentGroup_ID] AND 
    DATAMATRICESBINARYDATA_GROUP.[EquipmentGroup_Name] = [CRMSubscriptionsDataMatrices].[EquipmentGroup_Name] COLLATE DATABASE_DEFAULT and
    DATAMATRICESBINARYDATA_GROUP.user_logonname = [CRMSubscriptionsDataMatrices].user_logonname COLLATE DATABASE_DEFAULT)
  COMMIT TRANSACTION 
 
--#####################Hits data##############################################
 
  BEGIN TRANSACTION 
    INSERT INTO CRMSUBSCRIPTIONSV1_HITS(USER_LOGONNAME, USER_PASSWORD , 
    ORDER_ORDERPRODUCTID, ORDER_HITSPURCHASED)
    SELECT DISTINCT USER_LOGONNAME,USER_PASSWORD,ORDER_ORDERPRODUCTID,ORDER_HITSPURCHASED
    FROM CRMSUBSCRIPTIONSV1 WHERE CRMSUBSCRIPTIONSV1.PRODUCT_ISHITBASED = '1' AND NOT EXISTS
    (SELECT USER_LOGONNAME,USER_PASSWORD,ORDER_ORDERPRODUCTID,ORDER_HITSPURCHASED
     FROM CRMSUBSCRIPTIONSV1_HITS WHERE 
    CRMSUBSCRIPTIONSV1.USER_LOGONNAME = CRMSUBSCRIPTIONSV1_HITS.USER_LOGONNAME COLLATE DATABASE_DEFAULT AND 
    CRMSUBSCRIPTIONSV1.USER_PASSWORD = CRMSUBSCRIPTIONSV1_HITS.USER_PASSWORD COLLATE DATABASE_DEFAULT AND 
    CRMSUBSCRIPTIONSV1.ORDER_ORDERPRODUCTID  = CRMSUBSCRIPTIONSV1_HITS.ORDER_ORDERPRODUCTID AND 
    CRMSUBSCRIPTIONSV1.ORDER_HITSPURCHASED = CRMSUBSCRIPTIONSV1_HITS.ORDER_HITSPURCHASED)
    GROUP BY ORDER_ORDERPRODUCTID,USER_LOGONNAME,USER_PASSWORD,ORDER_HITSPURCHASED
  COMMIT TRANSACTION 
 
-----------------------------------------------
 
  BEGIN TRANSACTION 
    UPDATE [CRMSUBSCRIPTIONSV1_Hits]
    SET [Product_HitsLeft] = [Order_HitsPurchased]
    WHERE [Product_HitsLeft] IS NULL
  COMMIT TRANSACTION 
 
 
--##################Hits changed###################################################
 
  BEGIN TRANSACTION 
    INSERT INTO [CRMSUBSCRIPTIONSV1_AGREEDTC](USER_LOGONNAME, USER_PASSWORD)
    SELECT DISTINCT USER_LOGONNAME,USER_PASSWORD
    FROM CRMSUBSCRIPTIONSV1 WHERE NOT EXISTS
    (SELECT USER_LOGONNAME,USER_PASSWORD
    FROM [CRMSUBSCRIPTIONSV1_AGREEDTC] WHERE 
    CRMSUBSCRIPTIONSV1.USER_LOGONNAME = [CRMSUBSCRIPTIONSV1_AGREEDTC].USER_LOGONNAME COLLATE DATABASE_DEFAULT AND 
    CRMSUBSCRIPTIONSV1.USER_PASSWORD = [CRMSUBSCRIPTIONSV1_AGREEDTC].USER_PASSWORD COLLATE DATABASE_DEFAULT)
    GROUP BY USER_LOGONNAME,USER_PASSWORD
  COMMIT TRANSACTION 
 
--####################step 3 for v1################################################
 
  BEGIN TRANSACTION 
    INSERT INTO CRMSUBSCRIPTIONSV1_HITS_BACKUP(USER_LOGONNAME, USER_PASSWORD , 
    ORDER_ORDERPRODUCTID, ORDER_HITSPURCHASED, PRODUCT_HITSLEFT)
    SELECT USER_LOGONNAME, USER_PASSWORD , 
    ORDER_ORDERPRODUCTID, ORDER_HITSPURCHASED, PRODUCT_HITSLEFT
    FROM [CRMSUBSCRIPTIONSV1_Hits]
  COMMIT TRANSACTION 
 
END TRY
 
BEGIN CATCH
  ROLLBACK
  SELECT @@ERROR
END CATCH

Open in new window

Avatar of matrix_aash
matrix_aash
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

The idea is to rollback the whole SP in case any of the inserts or the first step fails. I am aware of the dependancies but cannot that not be achieved in a simple way. like the following ?

begin tran

select * into tablename from tablename1

insert statement 1
insert statement 2
insert statement 3
insert statement 4

if @error<>0
begin
rollback
end
else
begin
commit
end

Please let me know about the above method.

Avatar of Mark Wills
Mark Wills
Flag of Australia image
Don't think so....

If rows are not committed in statement 1, then how are they available in statement 3 ?

I think we need the ROLLBACK WORK in the catch block... And COMMIT WORK at the end to close off an outer BEGIN TRANSACTION.

When nesting transactions, ROLLBACK WORK always rolls back to the outermost BEGIN TRANSACTION statement and decrements the @@TRANCOUNT system function to 0.

And check @@trancount before commencing next step...

Whaddya think ?  And what happened to that Tim fella ?
Avatar of matrix_aash
matrix_aash
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

I think that right way of going ahead and you are right if the rows are not commited in insert 1 it wont be available for insert 3.

Can you please provide me a small sample with @@TRANCOUNT and wrapping rollback ?

Thanks in advance.
Avatar of Mark Wills
Mark Wills
Flag of Australia image
OK, below in the code snippet is your stored procedure... Do you want a small example ?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
ALTER PROCEDURE [dbo].[CRMSubscriptionsV1Job]
AS
 
BEGIN TRY
 
  IF  EXISTS (select name from sysobjects where xtype='u' and name = 'CRMSubscriptionsV1')
  DROP TABLE [dbo].[CRMSubscriptionsV1]
 
  BEGIN TRANSACTION TRN_CRM        -- start a new outer transaction that we can use to encapsulate every other transaction below.
 
    BEGIN TRANSACTION
      SELECT * INTO CRMSubscriptionsV1
      FROM [V_ACL_V1Subscriptions]
    COMMIT TRANSACTION
 
--################Date Matrices###########################
 
    IF @@trancount = 1    -- should still be within TRANSACTION TRN_CRM
    BEGIN
 
      BEGIN TRANSACTION
        INSERT INTO [CRMSubscriptionsDataMatrices](USER_LOGONNAME,USER_PASSWORD,PRODUCT_MODULENAME,ORDER_ORDERPRODUCTID,
        [Equipment_ID],[Equipment_Name],[EquipmentGroup_ID],[EquipmentGroup_Name],
        [Equipment_Manufacturer],[Equipment_Series],[Equipment_Type],[Options_FullLifeBase],
        [Options_HalfLifeBase],	[Options_CurrentValues],[Options_FutureValues],[Options_HistoricalValueFirstQuarter],
        [Options_HistoricalValueSecondQuarter],[Options_HistoricalValueThirdQuarter],[Options_HistoricalValueFourthQuarter],
        Options_LeaseRate],[Options_MarketValue])
        SELECT USER_LOGONNAME,USER_PASSWORD,PRODUCT_MODULENAME,ORDER_ORDERPRODUCTID,
        [Equipment_ID],[Equipment_Name],[EquipmentGroup_ID],[EquipmentGroup_Name],
        [Equipment_Manufacturer],[Equipment_Series],[Equipment_Type],[Options_FullLifeBase],
        [Options_HalfLifeBase],	[Options_CurrentValues],[Options_FutureValues],[Options_HistoricalValueFirstQuarter],
        [Options_HistoricalValueSecondQuarter],[Options_HistoricalValueThirdQuarter],[Options_HistoricalValueFourthQuarter],
        [Options_LeaseRate],[Options_MarketValue]
        FROM CRMSUBSCRIPTIONSV1 WHERE CRMSUBSCRIPTIONSV1.PRODUCT_MODULENAME = 'DATA MATRICES' AND
        [Equipment_Manufacturer] IS NOT NULL AND [Equipment_Series] IS NOT NULL AND [Equipment_Type]
        IS NOT NULL AND NOT EXISTS
        (SELECT USER_LOGONNAME,USER_PASSWORD,PRODUCT_MODULENAME,ORDER_ORDERPRODUCTID,
        [Equipment_ID],[Equipment_Name],[EquipmentGroup_ID],[EquipmentGroup_Name],
        [Equipment_Manufacturer],[Equipment_Series],[Equipment_Type],[Options_FullLifeBase],
        [Options_HalfLifeBase],	[Options_CurrentValues],[Options_FutureValues],[Options_HistoricalValueFirstQuarter],
        [Options_HistoricalValueSecondQuarter],[Options_HistoricalValueThirdQuarter],[Options_HistoricalValueFourthQuarter],
        [Options_LeaseRate],[Options_MarketValue]
         FROM CRMSUBSCRIPTIONSDATAMATRICES WHERE 
        CRMSUBSCRIPTIONSV1.USER_LOGONNAME = [CRMSubscriptionsDataMatrices].USER_LOGONNAME COLLATE DATABASE_DEFAULT AND 
        CRMSUBSCRIPTIONSV1.USER_PASSWORD = [CRMSubscriptionsDataMatrices].USER_PASSWORD COLLATE DATABASE_DEFAULT AND 
        CRMSUBSCRIPTIONSV1.PRODUCT_MODULENAME = CRMSUBSCRIPTIONSDATAMATRICES.[product_modulename]COLLATE DATABASE_DEFAULT AND
        CRMSUBSCRIPTIONSV1.ORDER_ORDERPRODUCTID = [CRMSubscriptionsDataMatrices].ORDER_ORDERPRODUCTID AND 
        CRMSUBSCRIPTIONSV1.[Equipment_Name] = CRMSUBSCRIPTIONSDATAMATRICES.[Equipment_Name]COLLATE DATABASE_DEFAULT AND
        CRMSUBSCRIPTIONSV1.[EquipmentGroup_Name] = CRMSUBSCRIPTIONSDATAMATRICES.[EquipmentGroup_Name]COLLATE DATABASE_DEFAULT AND
        CRMSUBSCRIPTIONSV1.[Equipment_Manufacturer] = CRMSUBSCRIPTIONSDATAMATRICES.[Equipment_Manufacturer]COLLATE DATABASE_DEFAULT AND
        CRMSUBSCRIPTIONSV1.[Equipment_Series] = CRMSUBSCRIPTIONSDATAMATRICES.[Equipment_Series]COLLATE DATABASE_DEFAULT AND
        CRMSUBSCRIPTIONSV1.[Equipment_Type] = CRMSUBSCRIPTIONSDATAMATRICES.[Equipment_Type]COLLATE DATABASE_DEFAULT AND
        CRMSUBSCRIPTIONSV1.[Options_FullLifeBase] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_FullLifeBase] AND
        CRMSUBSCRIPTIONSV1.[Options_HalfLifeBase] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HalfLifeBase] AND
        CRMSUBSCRIPTIONSV1.[Options_CurrentValues] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_CurrentValues] AND
        CRMSUBSCRIPTIONSV1.[Options_FutureValues] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_FutureValues] AND
        CRMSUBSCRIPTIONSV1.[Options_HistoricalValueFirstQuarter] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HistoricalValueFirstQuarter] AND
        CRMSUBSCRIPTIONSV1.[Options_HistoricalValueSecondQuarter] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HistoricalValueSecondQuarter] AND
        CRMSUBSCRIPTIONSV1.[Options_HistoricalValueThirdQuarter] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HistoricalValueThirdQuarter] AND
        CRMSUBSCRIPTIONSV1.[Options_HistoricalValueFourthQuarter] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_HistoricalValueFourthQuarter] AND
        CRMSUBSCRIPTIONSV1.[Options_LeaseRate] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_LeaseRate]AND
        CRMSUBSCRIPTIONSV1.[Options_MarketValue] = CRMSUBSCRIPTIONSDATAMATRICES.[Options_MarketValue])
      COMMIT TRANSACTION 
 
    END
    
--########################Insert Data on Binary Basis########################
 
    IF @@trancount = 1    -- should still be within TRANSACTION TRN_CRM
    BEGIN
 
      BEGIN TRANSACTION 
        INSERT INTO [DATAMATRICESBINARYDATA_GROUP]([EquipmentGroup_ID],[EquipmentGroup_Name],User_Logonname)
        SELECT  DISTINCT [EquipmentGroup_ID],[EquipmentGroup_Name],user_logonname
        FROM CRMSUBSCRIPTIONSDATAMATRICES WHERE NOT EXISTS
        (SELECT [EquipmentGroup_ID],[EquipmentGroup_Name],user_logonname
         FROM DATAMATRICESBINARYDATA_GROUP WHERE 
        DATAMATRICESBINARYDATA_GROUP.[EquipmentGroup_ID] = [CRMSubscriptionsDataMatrices].[EquipmentGroup_ID] AND 
        DATAMATRICESBINARYDATA_GROUP.[EquipmentGroup_Name] = [CRMSubscriptionsDataMatrices].[EquipmentGroup_Name] COLLATE DATABASE_DEFAULT and
        DATAMATRICESBINARYDATA_GROUP.user_logonname = [CRMSubscriptionsDataMatrices].user_logonname COLLATE DATABASE_DEFAULT)
      COMMIT TRANSACTION 
 
    END
     
--#####################Hits data##############################################
 
    IF @@trancount = 1    -- should still be within TRANSACTION TRN_CRM
    BEGIN
 
      BEGIN TRANSACTION 
        INSERT INTO CRMSUBSCRIPTIONSV1_HITS(USER_LOGONNAME, USER_PASSWORD , 
        ORDER_ORDERPRODUCTID, ORDER_HITSPURCHASED)
        SELECT DISTINCT USER_LOGONNAME,USER_PASSWORD,ORDER_ORDERPRODUCTID,ORDER_HITSPURCHASED
        FROM CRMSUBSCRIPTIONSV1 WHERE CRMSUBSCRIPTIONSV1.PRODUCT_ISHITBASED = '1' AND NOT EXISTS
        (SELECT USER_LOGONNAME,USER_PASSWORD,ORDER_ORDERPRODUCTID,ORDER_HITSPURCHASED
         FROM CRMSUBSCRIPTIONSV1_HITS WHERE 
        CRMSUBSCRIPTIONSV1.USER_LOGONNAME = CRMSUBSCRIPTIONSV1_HITS.USER_LOGONNAME COLLATE DATABASE_DEFAULT AND 
        CRMSUBSCRIPTIONSV1.USER_PASSWORD = CRMSUBSCRIPTIONSV1_HITS.USER_PASSWORD COLLATE DATABASE_DEFAULT AND 
        CRMSUBSCRIPTIONSV1.ORDER_ORDERPRODUCTID  = CRMSUBSCRIPTIONSV1_HITS.ORDER_ORDERPRODUCTID AND 
        CRMSUBSCRIPTIONSV1.ORDER_HITSPURCHASED = CRMSUBSCRIPTIONSV1_HITS.ORDER_HITSPURCHASED)
        GROUP BY ORDER_ORDERPRODUCTID,USER_LOGONNAME,USER_PASSWORD,ORDER_HITSPURCHASED
      COMMIT TRANSACTION 
 
    END     
-----------------------------------------------
 
    IF @@trancount = 1    -- should still be within TRANSACTION TRN_CRM
    BEGIN
 
      BEGIN TRANSACTION 
        UPDATE [CRMSUBSCRIPTIONSV1_Hits]
        SET [Product_HitsLeft] = [Order_HitsPurchased]
        WHERE [Product_HitsLeft] IS NULL
      COMMIT TRANSACTION 
     
    END
    
--##################Hits changed###################################################
 
    IF @@trancount = 1    -- should still be within TRANSACTION TRN_CRM
    BEGIN
 
      BEGIN TRANSACTION 
        INSERT INTO [CRMSUBSCRIPTIONSV1_AGREEDTC](USER_LOGONNAME, USER_PASSWORD)
        SELECT DISTINCT USER_LOGONNAME,USER_PASSWORD
        FROM CRMSUBSCRIPTIONSV1 WHERE NOT EXISTS
        (SELECT USER_LOGONNAME,USER_PASSWORD
        FROM [CRMSUBSCRIPTIONSV1_AGREEDTC] WHERE 
        CRMSUBSCRIPTIONSV1.USER_LOGONNAME = [CRMSUBSCRIPTIONSV1_AGREEDTC].USER_LOGONNAME COLLATE DATABASE_DEFAULT AND 
        CRMSUBSCRIPTIONSV1.USER_PASSWORD = [CRMSUBSCRIPTIONSV1_AGREEDTC].USER_PASSWORD COLLATE DATABASE_DEFAULT)
        GROUP BY USER_LOGONNAME,USER_PASSWORD
      COMMIT TRANSACTION 
     
    END
 
--####################step 3 for v1################################################
 
    IF @@trancount = 1    -- should still be within TRANSACTION TRN_CRM
    BEGIN
 
      BEGIN TRANSACTION 
        INSERT INTO CRMSUBSCRIPTIONSV1_HITS_BACKUP(USER_LOGONNAME, USER_PASSWORD , 
        ORDER_ORDERPRODUCTID, ORDER_HITSPURCHASED, PRODUCT_HITSLEFT)
        SELECT USER_LOGONNAME, USER_PASSWORD , 
        ORDER_ORDERPRODUCTID, ORDER_HITSPURCHASED, PRODUCT_HITSLEFT
        FROM [CRMSUBSCRIPTIONSV1_Hits]
      COMMIT TRANSACTION 
 
    END     
    
  COMMIT WORK        -- really same as doing  commit TRANSACTION TRN_CRM   
 
END TRY
 
BEGIN CATCH
  SELECT @@ERROR
  IF @@TRANCOUNT > 0 ROLLBACK WORK    -- should rollback everything contained within the TRANSACTION TRN_CRM, and being named, could rollback that name
END CATCH

Open in new window

Avatar of matrix_aash
matrix_aash
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Hi mark can you please provide a sample.   ?
Thanks
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial