doobdave
asked on
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
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
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks for the comments ispaleny.
Regards,
David
select @@spid