| Author |
Topic |
|
gargmanoj
Starting Member
7 Posts |
 Posted - 2005-08-07 : 08:24:15
|
| hi all,how can i get the transaction id of an transaction which is finished just now. like suppose i executed a transaction and i want to know what trnsactionId was assigned to this transaction by database.i need it urgently plz respond. any kind of help is appreciated.tahnx in advance. |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-07 : 09:52:14
|
| You can't (EDIT: from reading Stalker's post, I wasn't clear - you can't find the transaction ID of a transaction that's already finished/committed).The only thing you can get at is the internal transaction ID (called XdesID) from analyzing the transaction log, and you can't do anything with that (apart from use it to filter further analysis of the log)What are you trying to do?Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-08-07 : 09:52:42
|
| >>how can i get the transaction id of an transaction which is finished just now.question is not correct... What do you mean "just now" ? A second ago ? A millisecond ago ?in syslockinfo there is information about transactionID holding the locks... Maybe that's what you need...Why do you need this ? Always, when people ask such "incorrect" questions, they need something else... |
|
|
|
gargmanoj
Starting Member
7 Posts |
Posted - 2005-08-07 : 11:30:01
|
| >>> What are you trying to do?actually i am doing a project on database security in which i am supposed to assess n repair the damage caused by a malicious transaction. as there is no formal way to read transaction logs in mS SQL Server 2000 so for reading logs i am writing triggers for any kind of data change in tables and i am recording those changes in a seperate table. data in this table can be used for getting previous data if in case i have to undo some transaction.for identification of each transaction a logical unique id cant be used i need the exact transactionID assigned by the databasethanks |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-08-07 : 11:36:10
|
why can't you use a guid?you have to have a guid column in each table for it though...Go with the flow & have fun! Else fight the flow  |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-07 : 11:49:17
|
quote:
Originally posted by gargmanojfor identification of each transaction a logical unique id cant be used i need the exact transactionID assigned by the database
Why not? What does getting the exact transaction ID gain you?Again - you can't get it.Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
gargmanoj
Starting Member
7 Posts |
Posted - 2005-08-07 : 12:00:34
|
| >> Why not? What does getting the exact transaction ID gain you?reason for not using a logical tid is that as my triggers cant record the selct stmts. and while doing the damage assessment i need the dependencies between warious transactions so i have to record slect stmt also for that i am preprocessing the sql queries and recording the colums this query has accessed. as triggers and this processing is done seperately how can i decide the sequence of there execution. if i can get the original transaction id then there is no problem in getting the exact sequence |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-08-07 : 12:07:31
|
| well since you can't you should rethink your problem and go about it in some other way.Go with the flow & have fun! Else fight the flow |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-07 : 12:13:15
|
quote:
Originally posted by gargmanoj
>> Why not? What does getting the exact transaction ID gain you?reason for not using a logical tid is that as my triggers cant record the selct stmts. and while doing the damage assessment i need the dependencies between warious transactions so i have to record slect stmt also for that i am preprocessing the sql queries and recording the colums this query has accessed. as triggers and this processing is done seperately how can i decide the sequence of there execution. if i can get the original transaction id then there is no problem in getting the exact sequence
Having the xact ids doesn't guarantee you the execution order of the SQL statements, especially if coming from different connections. How do you account for the interplay of isolation levels and locking modes, locjing hints, blocking etc in your scheme?Also, having the xact ids doesn't help you work out the dependencies between them. Given that you have no knowledge of how and when we generate xact ids, I'm at a loss to see how they help you at all.It seems bit pointless that you're guarding against malicious xacts by preprocessing the SQL statements to allow data recovery - why not just spot a malicious xact while you're processing and disallow it?ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
|
how to get transactionID of a transaction