Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting my Current Session ID Expand / Collapse
Author
Message
Posted Wednesday, March 17, 2010 8:59 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:35 AM
Points: 166, Visits: 1,056
What command can I run to I get the session ID for my current connection to a database in TSQL? This is related to my previous question here about how to show current and past connections and connection information to a database.

Thank you
Post #884762
Posted Wednesday, March 17, 2010 9:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
@@SPID

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #884770
Posted Wednesday, March 17, 2010 9:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:08 PM
Points: 12,927, Visits: 32,333
I didn't see your previous post, but are you looking for @@SPID?
select @@SPID will give you your current connection id, is that what you are looking for?

here's a collection of some of the functions you can use to get more information about your own login:
select @@spid,
user_name() AS [user_name],
suser_name() AS [suser_name],
current_user AS [current_user],
system_user AS [system_user],
session_user AS [session_user],
user AS [user]



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #884774
Posted Thursday, March 18, 2010 1:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:35 AM
Points: 166, Visits: 1,056
Thanks guys. Yes, @@SPID is what I was looking for.

When adding columns to a table via our admin app, I need to show the admin user that the users X, Y and Z are still connected. They need to be disconnected to ensure data integrity in those tables. Normally the admin user would have told the users to exit the system, but I don't want to rely entirely on that (users can be an interesting bunch after all .) If the admin user okays it, the admin app will disconnect the users.

I was going to do it via a for loop in the app, merely KILLing all session ids that are not the current user's and that are connected to the target database. However, is there a better way? Over at bytes.com "Hugo Kornelis, SQL Server MVP" posted
ALTER DATABASE <dbname> SET SINGLE_USER

or
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK_IMMEDIATE

but my SSMS gives me a syntax error when I execute WITH ROLLBACK_IMMEDIATE. Is there a better way that ensures that transactions get rolled back? Should I just KILL the other sessions one by one and let their transactions roll back one by one or can I get all the transactions to roll back in parallel?

Thanks
Post #885283
Posted Thursday, March 18, 2010 4:14 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 9,928, Visits: 11,199
GDI Lord (3/18/2010)
Users can be an interesting bunch after all

Amen.

I was going to do it via a for loop in the app, merely KILLing all session ids that are not the current user's and that are connected to the target database. However, is there a better way?

The correct syntax is:

-- Kick everyone out, rolling back
-- their transactions if necessary
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

--
-- Do admin stuff
--

-- Restore access to the database
ALTER DATABASE AdventureWorks
SET MULTI_USER;

Notice the lack of an underscore between ROLLBACK and IMMEDIATE

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #885343
Posted Thursday, March 18, 2010 4:42 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:35 AM
Points: 166, Visits: 1,056
Thanks guys.
Post #885360
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse