Who's logged into to SQL through code

[DroopyA]

This is been an ongoing problem for me that the SQL people have helped me out with grarely. Currently, here is my situation:

The user clicks the "Restore" button and the program is suppose to automatically Restore a backed up SQL Server database. But, the program finds out that there are other users currently logged into the SQL Server. So, I bring up a message box that says "Users are loged on to SQL do you want to kick them out". If the users click yes, then I need to run "sp_who", get their ID numbers, and kill them from the system, and then run the restore. Wonderful, this will work. But, through code (Visual Basic) when I execute "sp_who" how do I get the returned ID numbers?

Thank you
-N473

 

[jebenson]

This code will execute the sp_who stored proc. and save the results in a recordset. Of course, you will need to supply your own connection information for the connection's ConnectionString property.

Private Sub GetUsers()
Dim conn As New Connection
Dim rs As New Recordset

conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=PE6400"
conn.CursorLocation = adUseServer
conn.Open

Set rs = conn.Execute("sp_who&quot
End Sub

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[tb]

A. List all current processes
This example uses sp_who without parameters to report all current users.

USE master
EXEC sp_who

B. List a specific user's process
This example shows how to view information about a single current user by login name.

USE master
EXEC sp_who 'janetl'

C. Display all active processes
USE master
EXEC sp_who 'active'

D. Display a specific process with process ID
USE master
EXEC sp_who '10' --specifies the process_id



"All I ask is the chance to prove that money can't make me happy." - Spike Milligan

 

[DroopyA]

I love you guys Thanks alot, you've just made my day alot better.

Thanks
-N473

 

[DroopyA]

Aww, so close yet so far away. Ok, this is the code I'm using (it's a test program with alot of copy & paste from my real program so I know the naming conventions are wrong) but the RecordCount = -1 and the Restore still gives me the error "Exclusive Access.. database in use"

'N473 ******************************* N473
'N473 * Backup/Restore the Database * N473
'N473 ******************************* N473
Dim g_strMainDBName As String
Dim g_strMainServer As String
Dim g_strMainDBPath As String
Dim rsUsers As ADODB.Recordset
Dim m_cn As ADODB.Connection

On Error GoTo GeneralError

g_strMainDBName = "TagLinkSQL"
g_strMainServer = txtComputerName.Text
g_strMainDBPath = "C:\PROGRA~1\TAGLINK\DATA\TagLinkSQL"

Set m_cn = New ADODB.Connection
m_cn.ConnectionString = "Provider = SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog = TagLinkSQLCS; Data Source = " & txtComputerName.Text & ";Initial File Name = C:\PROGRA~1\TAGLINK\DATA\TagLinkSQL.mdf"
m_cn.CursorLocation = adUseServer
m_cn.Open
Set rsUsers = m_cn.Execute("sp_who&quot

' Are their any users loged on to the System
If rsUsers.RecordCount > 0 Then
MsgBox "There are " & rsUsers.RecordCount & " users loged on to the server at this time. Would you like to kick them off?", vbYesNo, "TagLink"
Else
m_cn.Execute ("RESTORE DATABASE [TagLinkSQLCS] FROM DISK = N'C:\Program Files\TagLink\Data\TagLinkBackup.dat' WITH FILE = 12, NOUNLOAD , STATS = 10, RECOVERY&quot
End If

rsUsers.Close
m_cn.Close
Set rsUsers = Nothing
Set m_cn = Nothing
Exit Sub

GeneralError:
MsgBox err.Description, vbExclimation, "TESTING"
End Sub

 

[jebenson]

If you change m_cn.CursorLocation to adUseClient you will get a correct record count, not -1. Sorry about that.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[DroopyA]

Thanks alot. I was trying to figure out why it was comming back as -1 when I could still do a While Loop to get the ID numbers.

Peace
-N473