Task/transaction token by logging

[Matik]

Hallo All,

I'm making in my DB some logs.
I have a separate table containing:
ID,
Date,
Source,
Type,
ErrorNo,
Description

Acctualy the table struct is not immportand that's why I do not post
the script.
I have one procedure, which I call in my other function procedures,
triggers etc. with parameters.
This procedure (WriteToLog) is called, if in other procedures, triggers
is any validation, exception or only information which I would like to
log me separately, and it writes the record into this table.

But sometimes, it is very hard, to follow much logs, and I figured out,
if I could have something like transaction ID, task ID, it will be much
easier.

Firs idea was to, by going from procedures to other procedures, pass a
self generated token (ie. date with any additional number). But then, I
need to change everywhere where I call WriteToLog procedure the call
syntax.

Let's say that we have following situation:

Procedure1:

Action1
Select1
CompareOfValues1
exec WriteToLog

Action2
CompareOfValues2
exec WriteToLog

Action3
exec Procedure2
Procedure2:
Action1
exec WriteToLog
Select1
Insert1
Trigger1Started:
Action1
CompareOfValues1
exec WriteToLog
Action2
Trigger1Exit:
Procedure2Exit:
Procedure1Exit:

Now ... I could pass this my generated number, from SP to SP and so on.
But I would like to now, does the MS SQL server has something what
identifies transaction like descripted below.

In this case, I do not need to pass any number, only I need to get this
number anyhow in SP WriteToLog, and insert it into my log table.
Any sugestions?

Thank's in advance.

Mateusz

[Erland Sommarskog]

[posted and mailed, please reply in news]

Matik (ma****@sauron.xo.pl) writes:

Now ... I could pass this my generated number, from SP to SP and so on.
But I would like to now, does the MS SQL server has something what
identifies transaction like descripted below.

In this case, I do not need to pass any number, only I need to get this
number anyhow in SP WriteToLog, and insert it into my log table.
Any sugestions?

It sounds like this is a case where SET CONTEXT_INFO could come into
use. With this command you set a value for the process in
sysprocesses.context_info, and thus your WriteLog could read from this
place. context_info is binary, but you can convert forth and back from
a string if you like.

Read more about SET CONTEXT_INFO in Books Online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Matik]

Hello Erland,

Thank you for fast reply. Unfortunately, this option is not usefull for
me.
I cannot do this in my WriteToLog procedure. I need to set this option
at the beginning of each chain which I call, to get the value
consistent threw whole callstack.

I was thinking more, the SQL server is doing somethin like this
already, and I could use this transaction stamp for my logging.
Some more ideas?

Thank You

Mateusz

[Erland Sommarskog]

Matik (ma****@sauron.xo.pl) writes:

Thank you for fast reply. Unfortunately, this option is not usefull for
me.
I cannot do this in my WriteToLog procedure. I need to set this option
at the beginning of each chain which I call, to get the value
consistent threw whole callstack.
And that is precisely how you can use SET CONTEXT_INFO. You call SET
CONTEXT_INFO prior to starting the task, and in WriteToLog you get
the value from sysprocesses.
I was thinking more, the SQL server is doing somethin like this
already, and I could use this transaction stamp for my logging.

There is no such value that you can access.

And in any case, SET CONTEXT_INFO is more general, since it persists
over transaction boundaries.

The other option would be to have a temp table to retrieve the value
from.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp