Workaround for run sp_rename from within a transaction on SAP/Sybase ASE (Error 17260)

Question

I am trying call to sp_rename inside transaction (BEGIN TRANSACTION), but it shows this error message:

Can't run sp_rename from within a transaction., Error 17260, Procedure sp_rename, Line 78

The sp_rename code checks for any open transactions::

/*
** Running sp_rename inside a transaction would endanger the
** recoverability of the transaction/database. Disallow it.
** Do the @@trancount check before initializing any local variables,
** because "select" statement itself will start a transaction
** if chained mode is on.
*/
if @@trancount > 0
begin
    /*
    ** 17260, "Can't run %1! from within a transaction."
    */
    raiserror 17260, "sp_rename"
    return (1)
end
else
begin
    set chained off
end

I don't understand why these actions are a danger....

Additionally, I need a way to call this stored procedure within the transaction and then rollback this action.

Any suggestions?

**1)** users updating systems table, while in a transaction, is typically frowned upon however **2)** several system procs (including `sp_rename`) **do** perform such actions so **3)** I'm not sure why this particular proc has been coded explicitly to disable a user-initiated transactions; would likely need to raise the question with tech support (who would likely need to forward to ASE engineering) for a detailed explanation; as for a workaround ... you could try a modified version of `sp_rename` (not recommended for a production environment) though at that point you'd be on your own ... — markp-fuso, Feb 01 '21 at 15:39
... otherwise it may help if you provide a scenario where you foresee the need to run `sp_rename` inside a user-defined txn — markp-fuso, Feb 01 '21 at 16:24
I am building a unit testing framework for sybase/ase code. For my case, each test is a stored procedure. These tests should have calls from different sps with functionalities of the framework, such as creating fake tables among others. — Daniel Vera, Feb 01 '21 at 20:46
Each test is executed inside a transaction that makes sure to leave everything to the initial state when the test is finished (including the renaming of some table with the fake table program). When I try to run a test to create a fake table the error occurs because of the restriction. — Daniel Vera, Feb 01 '21 at 20:46

score 0 · Answer 1 · answered Feb 01 '21 at 23:05

ASE isn't really designed for rolling back schema changes (as you're seeing).

If you want a means of testing your 'framework functionality' consider:

create a new test db
run your scripts against this test db
when done just drop the test db; an alternative would be to run a series of drop commands to 'undo' all the schema changes

New databases are initially created as a copy of the model database so you could go so far as to install some base components in the model database but keep in mind the model database (and its contents) are used when creating all new databases (eg, all temp dbs when starting ASE), so don't add anything to the model database that you wouldn't want showing up in any new databases (outside your 'framework functionality' testing).

What you're proposing doesn't sound much different than what I've seen developers regularly do when testing a new 'release':

load a copy of the prod db
apply release package against said db
rinse/repeat until release package completes successfully
key being to start with a newly loaded (or created) database

A variation on the above:

create a new test db
add base components as needed
dump/save a copy of the test db
run your tests
when you want to run your test again then load that dump/saved copy back into the test db, then run tests again
basically same thing as loading copy of prod db but in this case you load a copy of your base test db

Workaround for run sp_rename from within a transaction on SAP/Sybase ASE (Error 17260)

1 Answers1