First, wrap the SQL statements within an eval.

Second, you'll want to enable transactions for multiple queries by setting autocommit to 0. After the queries have executed, then you'll need to do a manual commit.

Finally, check to see if the error message $@ has been set. If there is an error message, issue a rollback. I have embedded the necessary statements in your code. BTW, this code is untested, so your mileage may vary...

sub change_dboption_true(){

    my ( $dbh, $DB ) = @_;
    my $option = "single user";
    my $switch = "true";
    my $sql;
   
==> eval {
==>     $dbh->{AutoCommit} = 0;  # enable transactions

        $sql = "use master";
        $$dbh->do($sql) or $app->error($FATAL,"Can't do SQL statement 
+[ $sql ] :: $DBI::errstr"); FAILS HERE

        $sql = "sp_dboption $DB, \"$option\", $switch";
        $$dbh->do($sql) or $app->error($FATAL,"Can't do SQL statement 
+[ $sql ] :: $DBI::errstr");

        $sql = "use $DB";
        $$dbh->do($sql) or $app->error($FATAL,"Can't do SQL statement 
+[ $sql ] :: $DBI::errstr");

        $sql = "checkpoint";
        $$dbh->do($sql) or $app->error($FATAL,"Can't do SQL statement 
+[ $sql ] :: $DBI::errstr");

==>     $dbh->commit;   # manually commit the changes
==> } 
==> if ($@) {
==>     # handle the error ... insert other code here
==>     $dbh->rollback;
==> }

    $$dbh->disconnect;

}#end change_dboption_true
[download]