November 29, 2011 at 1:27 pm
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
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
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
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
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
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
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
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
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
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
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
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
Ok, thank's Kenneth.
Have a nice week.
Carl
December 6, 2011 at 4:56 pm
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
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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply