Comparison with Oracle

In Oracle, there is something called dbms_application_info
where developers can set some application information for
a current session and that piece of information can be
retrieved or updated within the same session. Is there
something similar in SQL Server? Thanks."Peter" <pchow@.ureach.com> wrote in message
news:06b201c36061$a50fdbc0$a301280a@.phx.gbl...
> In Oracle, there is something called dbms_application_info
> where developers can set some application information for
> a current session and that piece of information can be
> retrieved or updated within the same session. Is there
> something similar in SQL Server?
Package variables do that much better, BTW.
The closest SQLServer has is
SET CONTEXT_INFO
You can store 128 bytes of binary data on the session.
The data just goes into master.dbo.sysprocesses.context_info
This is also the only way to store data outside of the current transaction
context.
David|||I found it a lot easier to use a permanent table with information relevant
to the spid than to use CONTEXT_INFO (but that was also caused by our
particular requirements), see the code below.
CREATE TABLE session_role(
spid INT NOT NULL,
role_id INT NOT NULL
CONSTRAINT PK_session_role PRIMARY KEY CLUSTERED (spid),
CONSTRAINT FK_session_role_role FOREIGN KEY (role_id)
REFERENCES role_definitions (id))
GO
CREATE PROCEDURE SetSessionRole @.nRoleID INT
AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
UPDATE session_role
SET role_id = @.nRoleID
WHERE spid = @.@.spid
ELSE
INSERT INTO session_role (spid, role_id)
VALUES (@.@.spid, @.nRoleID)
GO
CREATE PROCEDURE GetSessionRole @.nRoleID INT OUPUT
AS
SET NOCOUNT ON
SET @.nRoleID = (SELECT role_id FROM session_role WHERE spid = @.@.spid)
GO
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:Obu0gMGYDHA.2200@.TK2MSFTNGP09.phx.gbl...
> "Peter" <pchow@.ureach.com> wrote in message
> news:06b201c36061$a50fdbc0$a301280a@.phx.gbl...
> > In Oracle, there is something called dbms_application_info
> > where developers can set some application information for
> > a current session and that piece of information can be
> > retrieved or updated within the same session. Is there
> > something similar in SQL Server?
> Package variables do that much better, BTW.
> The closest SQLServer has is
> SET CONTEXT_INFO
> You can store 128 bytes of binary data on the session.
> The data just goes into master.dbo.sysprocesses.context_info
> This is also the only way to store data outside of the current transaction
> context.
> David
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OJ4rYXNYDHA.1492@.TK2MSFTNGP12.phx.gbl...
> I found it a lot easier to use a permanent table with information relevant
> to the spid than to use CONTEXT_INFO (but that was also caused by our
> particular requirements), see the code below.
>
The caviat here is that this session table can become a trouble spot for
locking in the application. Two otherwise unrelated transactions can
serialize or even deadlock because they involve reads and writes to this
table.
David|||Unlikely, as there is an index and a primary key on the spid column, and no
two transactions can access the same row because they have different spids.
The only situation I can think of when blocking might occur is when a new
row is inserted, as it will then take out a range lock on the index, and the
spids next to it won't be available. This won't happen very often and can
even be avoided by prepopulating the table with an appropriate range of
spids.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uOn4EGOYDHA.2648@.TK2MSFTNGP09.phx.gbl...
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OJ4rYXNYDHA.1492@.TK2MSFTNGP12.phx.gbl...
> > I found it a lot easier to use a permanent table with information
relevant
> > to the spid than to use CONTEXT_INFO (but that was also caused by our
> > particular requirements), see the code below.
> >
> The caviat here is that this session table can become a trouble spot for
> locking in the application. Two otherwise unrelated transactions can
> serialize or even deadlock because they involve reads and writes to this
> table.
> David
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OwGlUbOYDHA.888@.TK2MSFTNGP10.phx.gbl...
> Unlikely, as there is an index and a primary key on the spid column, and
no
> two transactions can access the same row because they have different
spids.
> The only situation I can think of when blocking might occur is when a new
> row is inserted, as it will then take out a range lock on the index, and
the
> spids next to it won't be available. This won't happen very often and can
> even be avoided by prepopulating the table with an appropriate range of
> spids.
>
As written, the EXISTS check in SetSessionRole will require a shared read
lock on every row in session_role. This will block if another transaction
has run SetSessionRole inside an open transaction. And if SetSessionRole is
run in SERIALIZABLE isolation, these shared read locks will be held until
the end of that transaction.
It's probably not a big deal, but it's a big difference from Oracle package
variables, and it's something to keep an eye on.
David|||Hi David,
The EXISTS check can be run WITH(NOLOCK) and then there will be no blocking,
and every process will only lock the row with it's own spid. Thanks for
pointing out the problem.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
IF EXISTS(SELECT NULL FROM session_role WITH(NOLOCK) WHERE spid = @.@.spid )
UPDATE session_role SET role_id = 1 WHERE spid = @.@.spid
ELSE
INSERT INTO session_role (spid, role_id) VALUES(@.@.spid, 1)
-- COMMIT TRAN
The whole thing of working with spids is a bit dubious by the way if you
work with connection pooling. You have the same connection but it doesn't
have to be the same user, in which case it might be better to keep the
information on the middle tier.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23GUoJlOYDHA.1736@.TK2MSFTNGP10.phx.gbl...
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OwGlUbOYDHA.888@.TK2MSFTNGP10.phx.gbl...
> > Unlikely, as there is an index and a primary key on the spid column, and
> no
> > two transactions can access the same row because they have different
> spids.
> > The only situation I can think of when blocking might occur is when a
new
> > row is inserted, as it will then take out a range lock on the index, and
> the
> > spids next to it won't be available. This won't happen very often and
can
> > even be avoided by prepopulating the table with an appropriate range of
> > spids.
> >
> As written, the EXISTS check in SetSessionRole will require a shared read
> lock on every row in session_role. This will block if another transaction
> has run SetSessionRole inside an open transaction. And if SetSessionRole
is
> run in SERIALIZABLE isolation, these shared read locks will be held until
> the end of that transaction.
> It's probably not a big deal, but it's a big difference from Oracle
package
> variables, and it's something to keep an eye on.
> David
>