Custom-generated SPID

[Petrik Salovaara]

Summary of the problem:
We need a mechanism that allows our client application to send a
custom-generated identification string or ID-number to SQL Server via
ADO Connect object. This ID number should be visible in SQL Server
trigger procedures.

We are trying to accomplish the following:
- An update/insert/delete operation occurs
- Trigger writes a record containing info about the operation to an
audit-table
- The record should contain a field that uniquely identifies
the instance of the client application who performed the operation

We have tried to solve the problem as follows:
- Connect's SPID is written to aduit-table by the trigger
(with SELECT @@SPID)
- The SPID is available to the client app via ADO connect object

This works as long as every instance of our client app uses different
connect object. As soon as two or more clients are sharing the same
connect, this approach does not work.

Any ideas are welcome.
Petrik

[Anith Sen]

One option is to use SET CONTEXT_INFO which associates up to 128 bytes of
binary information with the current session or connection.

DECLARE @ctx AS VARBINARY(128)
SET @ctx = CAST('my_id' AS VARBINARY(128))
SET CONTEXT_INFO @ctx --- assign it

You can retrieve the value for this variable in your stored procedure or
trigger like:

DECLARE @ctx VARBINARY(128)
SET @ctx = (SELECT context_info
FROM master..sysprocesses
WHERE spid = @@SPID) -- @@SPID to get the current spid
-- do whatever

--
Anith

[Petrik Salovaara]

"Anith Sen" <an...@bizdatasolutions.com> wrote in news:OBkT4pj#DHA.3496
@TK2MSFTNGP10.phx.gbl:

> One option is to use SET CONTEXT_INFO which associates up to 128 bytes of
> binary information with the current session or connection.

Thanks for reply, this seems like right way to go. We'll try this out.
Petrik