August 13, 2002 at 7:38 am
I'm developing a application that modify database structure through SQLDMO. I'm trying to put a database in single user mode, but I can make two connections at same time with the same login.
It's possible to "lock" the database to other users and connections?
Marcelo Cid
August 13, 2002 at 9:49 am
Single user mode should do that. Can you post some code?
Andy
August 13, 2002 at 12:15 pm
quote:
Single user mode should do that. Can you post some code?Andy
http://qa.sqlservercentral.com/columnists/awarren/
Dim c1 As New SQLDMO.SQLServer
Dim c2 As New SQLDMO.SQLServer
Private Sub cmd1_Click()
c1.Connect "(local)", "sa", ""
c1.Databases("dbtest").DBOption.SingleUser = True
End Sub
Private Sub cmd2_Click()
c2.Connect "(local)", "sa", ""
c2.Databases("dbtest").DBOption.SingleUser = True
End Sub
What I need is that if I can enable SingleUser with cmd1_Click I won't be able to do it with cmd2_Click.
Marcelo Cid
August 13, 2002 at 5:25 pm
If the first attempt to put it in single user succeeds, the second one will result in a trappable error. I'd recommend disabling the 2nd command button when the first one is clicked, or just rethink your logic entirely. You'll also need to think about how you will handle it if there are users connected when you try it set it the first time.
Andy
August 14, 2002 at 12:52 pm
quote:
If the first attempt to put it in single user succeeds, the second one will result in a trappable error.
It won't. That's because I am so confused.
quote:
I'd recommend disabling the 2nd command button when the first one is clicked, or just rethink your logic entirely. You'll also need to think about how you will handle it if there are users connected when you try it set it the first time.Andy
http://qa.sqlservercentral.com/columnists/awarren/
I have found a solution.
If Singleuser is true then only one user can connect at once. But it isn't work with SQLDMO.SQLServe.Connect, but with ADO.Connection.Open.
If I can do
Dim c1 As New SQLDMO.SQLServer
Dim a1 As New ADODB.Connection
c1.Connect "(local)", "sa", ""
c1.Databases("dbtest").DBOption.SingleUser = True
a1.ConnectionString = "DRIVER={SQL Server};SERVER=localhost;DATABASE=dbtest;UID=sa;PWD="
a1.Open
I will have a locked database!
Thanks for your advice.
Marcelo Cid
Edited by - marcelocid on 08/14/2002 12:53:52 PM
November 7, 2002 at 12:46 pm
My last solution don't work either. I have to find another and I code this:
Private Sub DatabaseLock()
On Error GoTo ErrDatabaseLock
bDatabaseLocked = False
' Check if there is a lock already
If Not (oDatabase.DBOption.SingleUser Or oDatabase.DBOption.DBOUseOnly) Then
oDatabase.DBOption.DBOUseOnly = True
oDatabase.DBOption.SingleUser = True ' May generate a error
bDatabaseLocked = True
End If
Exit Sub
ErrDatabaseLock:
' Restore DBOUseOnly
oDatabase.DBOption.DBOUseOnly = False
End Sub
I need too one and only one user as DBO. If this user lock a database another process can't lock the database with the same user and finally I can have a "locked" database.
I will access the database with other user as member of db_dataread and db_datawriter roles.
Edited by - marcelocid on 11/07/2002 12:49:02 PM
November 7, 2002 at 12:58 pm
I may have read something wrong, however going back to your first solution, after pressing the button did you open QA or EM and check to see if you can connect to the database, or may somehow be rusing the same connection resource (don't think so). May not have taken affect, verify outside your app.
November 8, 2002 at 6:06 am
I haven't EM or QA. I'm coding with MSDE. I use some free applications like DbaMGR and SQL Server NameSpace Explorer.
I can, with DdbMGR or Access, connect to the database and modify its structure, but I can't access any data with it, Access or my application.
What I understand SingleUser do is that only one operation will access the database at same time. So I can't "lock" a database for a time.
November 10, 2002 at 7:13 pm
Both connections are using the same login and security. Therefore the second attempt is working with the same connection as the first. You would need to pass a SQL login with the second connection to make it fail.
Having Fun!
November 11, 2002 at 4:53 am
This was my first solution, but when I needed to execute some DDL with the same DMO connection I got an error.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply