Using SQL plugin with scripts that have rollback

[wbrussell]

I'm trying to figure out if the sql plugin will report an error if the script is allowed to run (not abort on error). My sql scripts have error handling and at the end of the script if any errors occurred the transactions are rolled back.

If the abort on error is checked, my assumption is that the rollback will never get executed since the script doesn't run all the way thru. This behavior would leave the database in an unknown state. If the flag is checked, according to the docs, it will always report SUCCESS even if there was an error and everything was rolled back. Why wouldn't the result show error?

From the user doc:

The variable will hold one of the following values, or a custom error message as described below:

Value Meaning
SUCCESS Connection to the database succeeded. The script executed successfully, or there were some script execution errors and Abort Execution on Script Error was not checked.
CONNECT_ERROR Connection to the server failed.
CONNECT_DATABASE_ERROR Connection to the database failed.
ERROR An unknown error occured.

In addition to the values in the table above, when Abort Execution on Script Error is checked, and an error occurs during script execution (where database and server connections succeeded), this variable holds detailed error information as follows: The variable is split into two parts, delineated by the string literal DELINEATOR. The first part of the variable holds the exact textual error message. The second part of the variable holds the exact SQL script fragment where execution errors occured. You may parse the variable using the Parse String function to extract both fields and utilize them separately.

Abort Exection on Script Error

Check this box to halt processing of the SQL script if any SQL statement fails. Uncheck this box to allow the SQL script to continue execution even if some SQL statements fail.

[FrancescoT]

Dear User,

just a question, have you verified what is the result value returned If "abort on error" is not checked?

I suppose it will return SUCCESS in both cases (with "abort on error" ON or OFF), because the script error is handled by the script itsef.

Let me know.

Regards

[wbrussell]

I did some testing yesterday:

if the "abort on error" checkbox is not checked, any error encountered during the script is not reported to the plugin as an error, but as SUCCESS. This is what the documentation says it does, but I don't understand the logic here since there is no way of knowing whether or not the script ran successfully during the install.

When the checkbox checked, how the script is aborted depends on your script. If there are "GO" statements after each TSQL commands, any error encounted will immediately stop the script (at the GO statement) from further execution. If there aren't any GO statements and an error occurs at the beginning of the script, the script will continue to run all the way to the end. The error will be reported to the plugin, however, depending on how you script was written your database could be in an undesirable state if some items errored and others were executed.

[FrancescoT]

Dear User,

When the checkbox checked, how the script is aborted depends on your script. If there are "GO" statements after each TSQL commands, any error encounted will immediately stop the script (at the GO statement) from further execution. If there aren't any GO statements and an error occurs at the beginning of the script, the script will continue to run all the way to the end. The error will be reported to the plugin, however, depending on how you script was written your database could be in an undesirable state if some items errored and others were executed.

Maybe I'm wrong, but I believe this is correct way the plugin has to work.

I'm not an SQL expert, but if with your "SQL script" you start a TRANSACTION, all the DB operations are not applied if the Transaction is not committed.
Doing so, the DB can't be in an undesirable state if a script error occurs ... I'm wrong?

Regards

[wbrussell]

That is correct, but whether or not transactions are used are up to the user.

It just seems to me that the "unchecked" option serves no purpose and could give a user a false sense of security on the installation. If it at least it returned an error you could handle it.