SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting my Current Session ID


Getting my Current Session ID

Author
Message
Andrew Jackson
Andrew Jackson
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1119 Visits: 1121
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
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57255 Visits: 9730
@@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
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71084 Visits: 40924
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Andrew Jackson
Andrew Jackson
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1119 Visits: 1121
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
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35270 Visits: 11361
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Andrew Jackson
Andrew Jackson
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1119 Visits: 1121
Thanks guys.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search