Discussion:
Constants in session level
(too old to reply)
d***@gmail.com
2013-05-22 14:24:52 UTC
Permalink
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
rpresser
2013-05-22 20:21:08 UTC
Permalink
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.
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
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.
Erland Sommarskog
2013-05-22 21:35:33 UTC
Permalink
Post by d***@gmail.com
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.

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, ***@sommarskog.se
rpresser
2013-05-23 17:19:06 UTC
Permalink
Post by Erland Sommarskog
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.

My mistake.
Dick Christoph
2013-07-02 06:23:51 UTC
Permalink
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
Post by d***@gmail.com
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.
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
Continue reading on narkive:
Loading...