Post reply

SQLCMD - begin tran without commit tran - no error, no warning

  • November 29, 2011 at 1:27 pm

    #263765

    Hi everyone,

    We are using SQLCMD to run our conversion scripts.

    We faced a problem with one conversion that was starting a transaction (BEGIN TRAN) but never commited (COMMIT TRAN) it or rolbacked it (ROLLBACK TRAN).

    Why, on this situation, SQLCMD terminate without giving an error or a warning after having rollbacked everything silently from the BEGIN TRAN to the end of the script?

    Is there a way to run SQLCMD giving a warning in this situation?

    Best regards.

    Carl

  • November 30, 2011 at 12:54 am

    #1414465

    Are you missing here? I am guessing here because you didn't post your code here.

    Error Reporting Options

    -b on error batch abort

    Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0. If the -V option has been set in addition to -b, sqlcmd will not report an error if the severity level is lower than the values set using -V. Command prompt batch files can test the value of ERRORLEVEL and handle the error appropriately. sqlcmd does not report errors for severity level 10 (informational messages).

    If the sqlcmd script contains an incorrect comment, syntax error, or is missing a scripting variable, ERRORLEVEL returned is 1.

  • November 30, 2011 at 1:41 pm

    #1414918

    What do you mean by "here" ("Missing here")?

    Since sqlcmd does not return an error in our case, I don't think your solution will work.

    Best regards.

    Carl

  • November 30, 2011 at 1:56 pm

    #1414931

    What if you use a TRY/CATCH block?

    BEGIN TRY

    BEGIN TRANSACTION;

    <DO STUFF >

    COMMIT TRANSACTION;

    END TRY

    BEGIN CATCH

    ROLLBACK TRANSACTION;

    < ERROR HANDLING HERE >

    END CATCH;

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • November 30, 2011 at 1:59 pm

    #1414936

    Hello Andre,

    Yes, good idea.

    It is an approach that is very different from what is done here rigth now but it could help in this particular case.

    Best regards.

    Carl

  • December 2, 2011 at 8:23 am

    #1415926

    Hello Andre,

    Will the use of a TRY/CATCH block help me to detect a missing COMMIT TRAN in a script?

    From what I tested, the answer is no. But maybe a missed something...

    The real problem here is the fact that a missing "COMMIT TRAN" in a script is silently handle by the tool/server and there seems to have no way to catch this flaw (human error: missing commit tran).

    Best regards.

    Carl

  • December 2, 2011 at 9:29 am

    #1416008

    Hi, Carl.

    You could use it to make sure that the transaction will be either committed or rolled back when the script ends. There won't be a missing COMMIT TRAN after the script ends because the TRY/CATCH block will take care of that for you. Unless you have many nested BEGIN TRAN commands it would work fine.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • December 2, 2011 at 9:32 am

    #1416010

    Or maybe use a loop "WHILE @@TRANCOUNT > 0 ROLLBACK TRAN..." or something. 🙂

    Sorry for the double post. I clicked the wrong button.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • December 2, 2011 at 9:45 am

    #1416024

    Hi Andre,

    I like the idea of adding some "generic" code to each conversion script.

    Using @@TRANCOUNT is a good idea in this case. 🙂

    Best regards.

    Carl

  • December 2, 2011 at 4:05 pm

    #1416215

    Carl B. (11/29/2011)

    Why, on this situation, SQLCMD terminate without giving an error or a warning after having rollbacked everything silently from the BEGIN TRAN to the end of the script?

    I didn't see an answer to this part of the original post, so if I missed it I apologize.

    The transaction was still open when SQLCMD closed correct? This is no different than closing a query window in SSMS with an open transaction.

    Once the connection is closed any open transactions get rolled back.

    Kenneth

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • December 5, 2011 at 6:55 am

    #1416659

    Hi understand your point Kenneth but in the context of script execution (using the -i option), both tools should warn the end user in this case. At least we should be able (with another option) to enable this behavior.

    Best regards.

    Carl

  • December 5, 2011 at 9:13 am

    #1416769

    Oh I agree 100% that you should be able to enable some type of logging to find out what happend. I was just trying to respond to that part of the orriginal question.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • December 5, 2011 at 11:29 am

    #1416920

    Ok, thank's Kenneth.

    Have a nice week.

    Carl

  • December 6, 2011 at 4:56 pm

    #1417727

    This is a tough one because the use of TRANSACTION is to ENSURE that there is an explicit COMMIT or ROLLBACK and ROLLBACK is the default upon lost connection, is all by design. This is how it is suppossed to work. It is asking alot to have an error generated when (to the system) no error has occured. I would suggest an edit of the script to ensure that the COMMIT is there or edit the batch file executing the commands to ensure a COMMIT. Or dont use TRANSACTIONS because the default behavior of a closed connection outside of TRANSACTION is to commit.

    I thought that there was a connection option to change the behavior to commit on closed transactions, but I cannot find it. May have been in Oracle where it is the other way around: Automatic rolback on uncommitted transactions on close of connections without declaring a transaction.

  • December 7, 2011 at 6:54 am

    #1418061

    Hi Jason,

    We have solved our problem by adding a test on @@TRANCOUNT and raising an error when <> 0 at the end of each script we execute.

    "Auto commit" on close connection would also have been a nice solution for us.

    Thank's for your input and have a nice day.

    Carl

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy