autonomous transactions

Is there any equivalent to Oracle's autonomous transaction in sqlserver.

Depends on what does "autonomous transaction" mean.

|||No. There is no way to do this in SQL Server right now. You can write extended SPs or CLR SPs (SQL 2005) that performs a different connection to the database. But this approach has it's own limitations and scalability problems. One workaround that will work in SQL2000/2005 depending on your requirements is to use a table variable within a transaction to dump the information. Table variables do not participate in user transactions so you can later retrieve the rows and dump them into an error log table for example after the transaction has been rolled back.|||Thanks Umachander for the information.

Autonomous Transaction can be used for controlling transactions in

stored procedures. Autonomous Transactions allow you to create a new

subtransaction that may commit or rollback changes independent

of the parent transaction. Assume two procedures - Parent and child

procedure. The parent procedure calls child procedure. You can set the

statements with in the child procedure as Autonomous and this will be

independent of the transaction of the Parent procedure. Hope the above

explains the Autonomous Transaction.|||Helped me. Thanks to both of you I learned something today!|||

Hi Umachander,

I have read your comments and I had the same problem when using SSMA to convert a PL/SQL stored procedure to T-SQL.

Interesting note is the error message - "ERROR ORA2MS-2046 line: 3 col: 4: Conversion of PRAGMA AUTONOMOUS_TRANSACTION is not implemented in this version"

I sincerely believe that if SQL Server is to be RDBMS of choice, code from any other RDBMS must be convertable

If you have any suggestions please let me know as it would be most appreciated

|||The only workarounds are those I mentioned in my previous reply. And we are considering this feature for a future version of SQL Server.