none
Problem with rollback

    Question

  • Hello,

    I have this query

    DECLARE @TransactionName varchar(20) = 'inlinefunctionTransaction';
    begin tran @TransactionName
     select * from jsp('S1','P1');
    ROLLBACK TRAN @TransactionName;
    go

    but when executing it I get a message

    Cannot roll back inlinefunctionTransaction. No transaction or savepoint of that name was found. 

    Can anybody explin me please what's going wrong?

    Thanks

    Sunday, March 16, 2014 2:49 AM

Answers

  • I suspect you are already in a transaction before you do the begin tran @TransactionName.  If you are already in a transaction, then you are allowed to do another begin tran.  This will be a nested transaction, and you cannot rollback just the nested transaction.  You have to rollback everything.  A way to check if you are already in a transaction is to do a SELECT @@TRANCOUNT.  If that returns anything other than 0, you have already started a transaction.  So try running

    DECLARE @TransactionName varchar(20) = 'inlinefunctionTransaction';
    SELECT @@TRANCOUNT AS TransactionCount;
    begin tran @TransactionName
     select * from jsp('S1','P1');
    ROLLBACK TRAN @TransactionName;
    go

    If that select statement returns a value other than 0, then you are already in transaction state and you will not be allowed to rollback just the inlinefunctionTransaction.  If you want to do a rollback, you either need to just use ROLLBACK TRAN, or, if you gave your outermost transaction a name, you can use ROLLBACK TRAN <whatever you named your outermost transaction>.  Either of those will rollback everything you have done since you entered the outermost transaction.  You cannot use ROLLBACK TRAN to rollback just part of a transaction. 

    Tom

    Tuesday, March 18, 2014 2:51 AM

All replies

  • Can you share the code of the TVF?  I'm not sure why a transaction is needed here since a TVP cannot modify data.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, March 16, 2014 3:10 AM
  • Hi,

    Why are you trying to ROLLBACK a SELECT statement.


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Sunday, March 16, 2014 3:22 AM
  • As others suggested, you do not require a transaction if this is the real scenario.

    Please try the same, I am not able to replicate your issue. May be, if you can share your code with us, we would be able to help you better:

    CREATE FUNCTION jsp
                     ( @Region nvarchar(50) )
    RETURNS table
    AS
    RETURN (
            SELECT 1 s1
           );
    GO
    
    create table test_tran(Region varchar(50))
    
    DECLARE @TransactionName varchar(20) = 'inlinefunctionTransaction';
    begin tran @TransactionName
     Insert into test_tran Select s1 From jsp('sp')
    ROLLBACK TRAN @TransactionName;
    go
    
    Select * From test_tran

    Sunday, March 16, 2014 3:35 AM
  • the (RollBAck Tran) rollback all transaction, so i think that jsp contain a rollback in his code,

    you can try one think, replace jsp by a table like table1, or remove a rollback in jsp

    Sunday, March 16, 2014 3:51 AM
  • In addition to possibilities others have mentioned, if you are already in transaction state doing a begin transaction @TransactionName will not give you a transaction named @TransactionName.  SQL Server does not have nested transaction transactions.  If you are already in a transaction and you do another begin transaction, all that happens is that SQL Server increments a counter called @@TRANCOUNT.

    As an aside (because it is not the cause of your problem), you have @TransacName as varchar(20), but you are putting a string longer than 20 characters in that variable, so it will be truncated.

    Tom

     
    Sunday, March 16, 2014 4:06 AM
  • Thanks, but I tried with

    DECLARE

    @TransactionName varchar(40)='infTransaction';

    but again happened, so the varchar od 20 was not the problem.I have begin tran for several dierent procedures. All of them start with

    DECLARE

    @TransactionName varchar(40)='infTransaction';


    begin

    tran @TransactionName

    but only the last function ends with

    ROLLBACK

    TRAN @TransactionName;

    and all the procedures end with go, which I don't know whether is ok.

    Thanks



    • Edited by pet06 Monday, March 17, 2014 2:41 AM
    Monday, March 17, 2014 2:26 AM
  • I removed the rollback from there and put it in the previous procedure, but again I get the same error and it takes the name of the declared previous transaction
    Monday, March 17, 2014 2:28 AM
  • I removed the rollback from there and put it in the previous procedure, but again I get the same error and it takes the name of the declared previous transaction

    Hello,

    It seemst that you  run the query inside a stored procedure. Did you specify a outer transaction in the stored procedure? or any nested transaction in the UDF "JSP"?

    Please post the complete query statement you used for farther analysis.

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Monday, March 17, 2014 4:35 AM
  • I suspect you are already in a transaction before you do the begin tran @TransactionName.  If you are already in a transaction, then you are allowed to do another begin tran.  This will be a nested transaction, and you cannot rollback just the nested transaction.  You have to rollback everything.  A way to check if you are already in a transaction is to do a SELECT @@TRANCOUNT.  If that returns anything other than 0, you have already started a transaction.  So try running

    DECLARE @TransactionName varchar(20) = 'inlinefunctionTransaction';
    SELECT @@TRANCOUNT AS TransactionCount;
    begin tran @TransactionName
     select * from jsp('S1','P1');
    ROLLBACK TRAN @TransactionName;
    go

    If that select statement returns a value other than 0, then you are already in transaction state and you will not be allowed to rollback just the inlinefunctionTransaction.  If you want to do a rollback, you either need to just use ROLLBACK TRAN, or, if you gave your outermost transaction a name, you can use ROLLBACK TRAN <whatever you named your outermost transaction>.  Either of those will rollback everything you have done since you entered the outermost transaction.  You cannot use ROLLBACK TRAN to rollback just part of a transaction. 

    Tom

    Tuesday, March 18, 2014 2:51 AM