2

I think I want to associate a piece of data with each session.

The association must have the following properties:

  • The data being associated is provided either as a part of connection string (if that is possible at all) or as a parameter for a stored procedure which then must be the first SP called within the session.
  • The data must be easily available for any stored procedures the session then calls, without having this piece of data passed as a parameter.
    This point is essential for the idea: obviously, I could add a parameter into each of my stored procedures and oblige the software to pass the piece of data each time any stored proc is called, but that is exactly what I want to avoid.
  • The data must automatically expire and be invalid as soon as the session disconnects. No actions from the client must be required to mark the data as expired. If a session disconnects unexpectedly, that still must render its piece of data invalid.

I've considered several ways of doing it, but didn't like any:

  • Local temporary tables.
    Pick a table name (based on a freshly created GUID to avoid any clashes). Have a stored procedure that creates temporary table with this name and inserts a row with the data passed as a parameter. Then all other procs can reference the #table and get the data. The idea is that each session will have it's own #table, while the name of the #table remains the same.
    Won't work. The temporary table created in the stored procedure will be dropped upon exiting the procedure. Making the user create the #table directly, without calling a SP, is not an option.
  • A regular table where data is looked up by @SPID.
    Won't work. This requires deleting the data manually upon disconnect.
  • APP_NAME().
    Will probably work, but that's not really app name, and that will not be easily extendable.

Are there better ways of associating data with a session?

  • What's your use case for this functionality? – Nick Chammas May 7 '12 at 12:55
  • @NickChammas Storing some information about the current user (beyond what is provided by SQL Server directly); mostly for logging, with a little bit of decision making. Not related to security. – GSerg May 7 '12 at 12:58
3

Use SET CONTEXT_INFO.

The most common use case for this is to send triggers information about the actor that caused the trigger to fire.

For example:

CREATE PROCEDURE [dbo].[ModifyData]
AS
BEGIN
    DECLARE @ObjectID BINARY(4) = CAST(@@PROCID AS BINARY(4));
    SET CONTEXT_INFO @ObjectID;

    UPDATE [dbo].[Data]
    ...
END;
GO

CREATE TRIGGER [LogModifications]
ON [dbo].[Data]
FOR UPDATE 
AS 
BEGIN
    DECLARE @ModifiedByObjectID INT = CAST(SUBSTRING(CONTEXT_INFO(), 1, 4) AS INT);

    ...
END;
GO
  • I was just reading about that one when you posted the answer; Possible, but same notes apply as for app_name(). – GSerg May 7 '12 at 12:45
  • @GSerg - Could you elaborate? SET CONTEXT_INFO satisfies the three bullet points at the beginning of your question, and I don't see how the APP_NAME notes apply to it. It is explicitly designed to let you associate information with a session. – Nick Chammas May 7 '12 at 12:49
  • What I meant is that there is a restriction on what can be stored as context info. 128 bytes of binary data is enough for me currently, but if I suddenly want a bit more data, it won't fit there, which is "not easily extendable." Switching the whole thing to a different mechanism will then be a pain. You suggestion is the best so far though. – GSerg May 7 '12 at 12:56
  • I thought about that, but then we're back to the problem of cleaning up expired data? No wait, we're not. The data will not delete itself, but will not get used by any subsequent connections... Yes, that's an option. – GSerg May 7 '12 at 13:03
  • 1
    @GSerg - Yes, use a generated ID for fetching data, and use the SPID for cleaning it out. If you want more precise ways of determining when a piece of data is no longer needed, I suggest looking at the information in sys.dm_exec_requests. Namely, look at start_time and transaction_id. – Nick Chammas May 7 '12 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.