Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Constants in session level
29 views
Skip to first unread message
duru...@gmail.com
May 22, 2013, 10:24:52 AM5/22/13
to
Hi!
We have more frequently used constants in another database.
For example: "Current_User" which represent the current logged user id.
We used it in my triggers, queries, etc.
I plan to port this in MS SQL server, and I want merge all users to logical level (to roles), for not use database level (real ms users).
But I'm not sure what MS SQL server supports.
I have two ways:
1.)
I created a named temporary table (dropped at session's end), put the needed constants to it, and later I used "select into" or "join" to get this info.
For example the current_userid = 128.
This can be used in trigger, stored proces, and queries too.
2.)
If the MS-SQL supports to define session level variables, I would use them instead of temp table. But I want to access/use them in triggers, stps, and queries too.
Please help me: does SQL server support these session level variables, or other way; or I need to use temp table to store my session level constants anywhere?
Thanks for any help!
Regards: dd
We have more frequently used constants in another database.
For example: "Current_User" which represent the current logged user id.
We used it in my triggers, queries, etc.
I plan to port this in MS SQL server, and I want merge all users to logical level (to roles), for not use database level (real ms users).
But I'm not sure what MS SQL server supports.
I have two ways:
1.)
I created a named temporary table (dropped at session's end), put the needed constants to it, and later I used "select into" or "join" to get this info.
For example the current_userid = 128.
This can be used in trigger, stored proces, and queries too.
2.)
If the MS-SQL supports to define session level variables, I would use them instead of temp table. But I want to access/use them in triggers, stps, and queries too.
Please help me: does SQL server support these session level variables, or other way; or I need to use temp table to store my session level constants anywhere?
Thanks for any help!
Regards: dd
rpresser
May 22, 2013, 4:21:08 PM5/22/13
to
On Wednesday, May 22, 2013 10:24:52 AM UTC-4, duru...@gmail.com
wrote:
SQL Server does not know what sessions your webserver is using. As
far as SQL Server is concerned, every query is a unique question
from a new person; it cannot connect one to other other at all
unless you repeat information to it, or store some information in a
table.
Furthermore, temporary tables are not shared from query to query.
You must use a PERMANENT table to store your information from query
to query.
wrote:
far as SQL Server is concerned, every query is a unique question
from a new person; it cannot connect one to other other at all
unless you repeat information to it, or store some information in a
table.
Furthermore, temporary tables are not shared from query to query.
You must use a PERMANENT table to store your information from query
to query.
Erland Sommarskog
May 22, 2013, 5:35:33 PM5/22/13
to
(duru...@gmail.com) writes:
> If the MS-SQL supports to define session level variables, I would use
> them instead of temp table. But I want to access/use them in triggers,
> stps, and queries too.
>
> Please help me: does SQL server support these session level variables,
> or other way; or I need to use temp table to store my session level
> constants anywhere?
rpresser answered your question assuming that your session is a webserver session, I'm not sure where he got that from.
> If the MS-SQL supports to define session level variables, I would use
> them instead of temp table. But I want to access/use them in triggers,
> stps, and queries too.
>
> Please help me: does SQL server support these session level variables,
> or other way; or I need to use temp table to store my session level
> constants anywhere?
I assume that you simply mean conenction-global variables. No, SQL Server does not have much of the kind. You use SET CONTEXT_INFO to set a varbinary(128) value which you later can retrieve with the function context_info(). This typically used to hold the actual user, when the application uses an application login.
--
Erland Sommarskog, Stockholm, esq...@sommarskog.se
rpresser
May 23, 2013, 1:19:06 PM5/23/13
to
On Wednesday, May 22, 2013 5:35:33 PM UTC-4, Erland Sommarskog wrote:
> rpresser answered your question assuming that your session is a
> webserver session, I'm not sure where he got that from.
None so blind as those who assume they know what the question means.
> rpresser answered your question assuming that your session is a
> webserver session, I'm not sure where he got that from.
My mistake.
Dick Christoph
Jul 2, 2013, 2:23:51 AM7/2/13
to
I would recommend storing all sorts of session data in permanent SQL Tables.
That way if the session terminates unexpectedly you can get the data to
restore it from the DB. Tie the tables in MS Sql together with some sort of
Session-id which doesn't have to bear any resemblance to the web servers
session ID. Rather than drop and recreate tables, make them permanent and
have cleanup routines when the session user logs out.
Dick
http://dchristo.vigilant75.com
<duru...@gmail.com> wrote in message
news:6c8763b9-4cc4-49a2...@googlegroups.com...
That way if the session terminates unexpectedly you can get the data to
restore it from the DB. Tie the tables in MS Sql together with some sort of
Session-id which doesn't have to bear any resemblance to the web servers
session ID. Rather than drop and recreate tables, make them permanent and
have cleanup routines when the session user logs out.
Dick
http://dchristo.vigilant75.com
<duru...@gmail.com> wrote in message
news:6c8763b9-4cc4-49a2...@googlegroups.com...
0 new messages