This would be some workaround which have already thought at too.
The problem with this is mainly one thing:
The transaction can span over more calls of different stored procedure calls invoked by method calls of COM+ components (in sequence). The transaction is a distributed one where the sql server connection is enlisted on. Such a thing would mean enhance the application code for setting some "footprints" and can be a lot of coding which want to avoid and is prone to errors because if we do this we have to do it very carefully to not forget a single codepart involved in the transactions in question and additionally have to maintain this additional code for keep the notification mechanism alive and consistent.
Still getting this transactionID (if that is possible) would be best in my opinion.
-----Original Message-----
From: Aleksey Urusov
[mailto:mssqldba-ezmlmshield-x72719353.[Email address protected]
Sent: Dienstag, 29. Juni 2004 22:24
To: LazyDBA Discussion
Subject: RE: getting current transaction ID
What about storing the combination of timestamp + spid?
A
-----Original Message-----
From: Franz Zoister
[mailto:mssqldba-ezmlmshield-x34332067.[Email address protected]
Sent: Tuesday, June 29, 2004 3:55 PM
To: LazyDBA Discussion
Subject: RE: getting current transaction ID
The @@spid gives me just the current sql server process ID.
But this is not enough because besides otheres the connections are pooled
and the next transaction might have the same @@spid.
regards FZ
-----Original Message-----
From: Aleksey Urusov
[mailto:mssqldba-ezmlmshield-x24716669.[Email address protected]
Sent: Dienstag, 29. Juni 2004 21:50
To: LazyDBA Discussion
Subject: RE: getting current transaction ID
Try
select @@spid()
Alex
-----Original Message-----
From: Franz Zoister
[mailto:mssqldba-ezmlmshield-x62909113.[Email address protected]
Sent: Tuesday, June 29, 2004 3:40 PM
To: LazyDBA Discussion
Subject: getting current transaction ID
Hi!
I would need a way to get the current transactionID of the connection (Sql
Server 2000 SP3).
The reason for this is that want to create triggers on some tables which
write update-, insert- and delete- history into another table from where
this change history (only the committed entries) is picked up for
notification to another service and I want to notify changes grouped by
transaction so that the subscriber of this notifications doesnt get
inconsistent data.
I am not using replication for this because the changes should be
transmitted as fast as possible.
I didnt find such a connection property or function for reading the current
transation ID in the BOL so far.
Does anybody know how such transaction ID can be read in T-SQL if this is
possible at all ?
regards FZ
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page