July 4, 2006 at 5:46 am
Hi,
Please let me know what is equavalent in SQL Server for DBMS_TRANSACTION in Oracle ?
OR How to identify the transaction or How to get transaction unique id?
Thank you very much for early response!!!!!!!!!!!!!!
July 5, 2006 at 7:44 am
Have you tried the SQL Server Migration Assistant (SSMA)? It helps in the conversion of Oracle code to SQL Server. It is a FREE download from MSFT.
July 5, 2006 at 7:14 pm
DBCC OPENTRAN will give you information about the oldest active transaction. However, if you are looking to identify the start/end of a transaction in PL/SQL using DBMS_TRANSACTION.local_transaction_id you'll have to re-think your strategy in SQL Server. I had a similar problem some years ago. I re-rote my auditing system using @@trancount. The latter is incremented by 1 for every cascaded transaction.
There is no equivalent to step_id in SQL Server.
Cheers,
Win
July 11, 2006 at 7:45 am
We tried with SSMA tool. It converts all the DDL, indexes etc except triggers. It shows red color for triggers.
Is there any options to convert triggers as well.
Currently I able to convert the trigger also...thanks lot......
July 19, 2006 at 4:46 am
I also need to identify the unique transaction id for use in an auditing system.
did you manage to find an equivalent to Oracle's local_transaction_id? I can't see how the @@trancount would help - can you send more pointers plz?
July 19, 2006 at 7:23 pm
We use @@trancount just as boundary marker in cascading transactions. The auditing trigger in a child table starts like this:
if @@trancount > 1
return
A transaction count greater than 1 indicates that a cascading transaction started higher up in the data model hierarchy. In order to impact as little as possible on transactions we only log the parent transaction. If however the transaction starts at the level of the child table, then it is logged.
The actual transaction id is not important. We log database user, OS user, workstation, program which manipulates the db, session id and of course date/time.
Cheers,
Win
July 19, 2006 at 8:57 pm
I assume that Oracle is giving you some for of unique transaction identifier. SQL Server doesn't have this but you might be able to do it yourself (if you have control over the SQL code of course).
The syntax for the begin transaction command is
BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable
[ WITH MARK [ 'description' ] ] ]
You could use the "WITH MARK 'description'" option to supply your own unique transaction identifier.
July 21, 2006 at 3:46 am
Thanks for the replies.
Yes Oracle provides a unique transaction id that I can capture in underlaying triggers. This means I don't need to 'prep' the start of the transaction itself (which may be an unauthorised transaction through a different application for example) first. I'll give it more thought here though. Again thanks for your responses.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply