• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 545
  • Last Modified:

SQL Server equivalent of Oracle context variables

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,

David
0
doobdave
Asked:
doobdave
  • 3
  • 2
1 Solution
 
ispalenyCommented:
select suser_sname()
select @@spid
0
 
ispalenyCommented:
If you want to store a session specific information:
1.
 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.
0
 
doobdaveAuthor Commented:
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.

Regards,

David
0
 
ispalenyCommented:
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.
0
 
doobdaveAuthor Commented:
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.

Regards,

David
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now