Hafez (ha**********@msn.com) writes:
The problem:!!
I want to create a temp table which is common between different users
so that each user could select his rows and print them without
printing others selection.
All clients use the same connection string.
Is there a way to identify users?
I think it's possible to delete rows of a user when he logs out the
program.
Do I think right?
If all you want is a table that goes away with the connection, then
create a temp table like this:
CREATE TABLE #temp (col1 int NOT NULL, ...)
That table is private to the connection, and it goes away when you
disconnect.
There are however situations where this is not feasible. A common technique
in that case is to use @@spid as the key in the table:
CREATE TABLE transient (spid int NOT NULL,
col1 int NOT NULL,
...)
go
DELETE transient WHERE spid = @@spid
INSERT transient(spid, col1, col2, ...)
SELECT @@spid, ...
go
SELECT col1, ...
FROM transient
WHERE spid = @@spid
AND ...
Here there is no automatic cleanup at exit, which is why you must delete
before you can insert anything, so that data that is around from the
previous connection is around.
However, neither this solution is always good. In modern clients you often
work disconnected, and reconnect only when you have something to say
SQL Server. In that case, you may get a different connection. Also, a
popular library like ADO can sometimes open a second connection behind
your back, although you only have one connection object.
To address this you can have some method to get a unique key that is
a negative number (so that you can use @@spid when that is safe.) We
have som 20 tables of this kind in our system, and we get they by
taking checksum(newid()) and the register the key in a special table
which is then cleaned up at night. A simpler approach may be to simply
use a GUID column instead of integer.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
