Check out "Session Context Information" in books on-line. You can
execute a SET CONTEXT_INFO statement when you connect to sql server, and
then retrieve that within sql server transactions based on SPID. Your
application would just need to send the same context info to every sql
server that is elisted in the the transaction.
-----Original Message-----
From: Franz Zoister
[mailto:mssqldba-ezmlmshield-x20233432.[Email address protected]
Sent: Tuesday, June 29, 2004 3:35 PM
To: LazyDBA Discussion
Subject: RE: getting current transaction ID
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]
---------------------------------------------------------------------
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