SQL Server equivalent of Oracle context variables

Posted on 2005-05-12
Last Modified: 2008-01-09
Hello Experts!

I've been charged with mirroring functionality, which exists in my company's Oracle db, to SQL Server, and have come to realize that Oracle and Sql Server are nowhere near as similar as I was led to believe (or maybe wanted to believe)!

Anyway, my latest in a series of problems is trying to find a SQL Server equivalent of Oracle's CONTEXT VARIABLES.

Here's the situation:

We have a web app which is used by many users (as one wuold expect).
The app interacts with an Oracle db, to store and retrieve user and app-related data.
Certain operations performed on the database (eg. Inserts into certain tables) are logged using triggers.
These triggers also log information SPECIFIC to the user & session that caused the modification of data.

The current methodology for this is as follows:
1) Web app stores current user's info and session info (eg. what task the user is in the middle of) in Oracle context variables.
2) Web app calls stored procedure to perform insert/update/delete on a table.
3) Trigger on that table fires, and reads values from context variables and puts these values in the logging table.
4) Web app clears context variables.

So, does anyone have any ideas/suggestions for achieving the same functionality in SQL Server?
We currently have a solution in place, but I personally don't think it's the best possible... and so am looking for other ideas.

I will post the current solution if you would like me to.

Thanks in advance,

Question by:doobdave
    LVL 13

    Expert Comment

    select suser_sname()
    select @@spid
    LVL 13

    Expert Comment

    If you want to store a session specific information:
     it can be stored in OLEDB connection string property "Application Name".

    Application Name='IISSession*1112*'

    and accessed by T-SQL

    select APP_NAME()

    2. Or create a temporary (#) table storing variables in rows. Temporary tables are auto-deleted on connection closing.
    LVL 8

    Author Comment

    Hi ispaleny,

    Thanks for the responses.

    The suggestions you make are cetainly workable, here's my comments:

    1. I haven't tried this yet, but we need to store quite a bit of information, so I'm not sure the best way for us to do this is using one long string which would then have to be parsed.

    2. This is the method we currently have in place and which works. However, we can't take advantage of stored procedures for creating and populating the temp table, as the temp table goes out of scope once the proc ends. So the trigger will not be able to access the values.

    Thanks for your time, and any more suggestions would be greatly appreciated.


    LVL 13

    Accepted Solution

    1. You cannot store a long string in app_name, it is limited to nvarchar(128).
    2. You can use SPs for populating the temp tables created before you use them in SP and such a table exists after SP execution. But it is true, you cannot use compilated execution plans in this work.
    LVL 8

    Author Comment

    I will award you the points as you have helped me to ensure that the way we're doing things is indeed the best/only workable solution for our scenario.

    Thanks for the comments ispaleny.



    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now