November 20, 2002 at 6:15 pm
My VB program connects to a SQL database using ADO on startup. When I select restore database from the menu, I am closing the existing ADO connection first, and then attempting to restore the database. It works fine when I manually F8 through the code after creating a break just before the code runs to close the existing ADO connection.
When I run the code without a break, it does not work. I am getting the error "Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally" The restore is attempted 3 times, but each time it fails because "exclusive access could not be obtained......" error.
Is there a way to put a pause in the program after the code that closes the existing ADO connection? Or is there some other technique to solve this problem?
Thanks
November 20, 2002 at 6:48 pm
connect to master, not the db to be restored.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 20, 2002 at 7:31 pm
Sounds like your running into a race condition in that VB gets the connection closed message but SQL has not finished cleanup on it's end. I agree with Steve attach to master instead.
November 21, 2002 at 9:03 am
I am using the Andy Warren's code to do the restore, and it does not connect to any database to perform the restore, it connects to server only. Like this:
With oServer
.Connect ServerName
End with
So I'm not sure what you mean by connecting to master instead. My program connects to its own database on startup using ADO, and that is the connection that I close when the user selects Restore Database from the menu. I need the program to pause somehow to let SQL Server finish doing the cleanup after closing the ADO connection to this database that is about to be restored.
Is there a way to do this or am I missing something here? Thanks.
November 21, 2002 at 7:45 pm
It probably is the ADO connection that hasnt cleared. Connection pooling can be the culprit. To be really safe, I'd recommend connecting to master, then checking sysprocesses for connections to the db in question, then do a kill on any open connection. Optionally you can set it to dbo only so that no one else can connect.
Andy
November 22, 2002 at 9:04 am
Thanks, Andy. Do you have any code samples or can you point me to a resource on how to check sysprocesses for connections to the db and then killing them. Hopefully this can be done using SQLDMO.
November 24, 2002 at 7:26 pm
There is a killprocess method on the server object. Dont know if there is a DMO way to get the processes directly (can always use execute to run 'select * from sysprocesses' to get a queryresults object).
Andy
November 25, 2002 at 4:44 am
EnumProcesses method of the SQLServer object will list them. See BOL for more details.
December 2, 2002 at 8:16 am
Andy,
You can kill the Connection ID with SQLDMO.
This is a VB module I used to kill all other connections except my connection. I hope this may help other.
'--------------------------------------------------------------------------
' Kills all of the user connections to the database
'--------------------------------------------------------------------------
Private Function KillDatabaseConnections(oDatabase As SQLDMO.Database, _
oSQLServer As SQLDMO.SQLServer, _
) As Boolean
On Error GoTo ErrorHandler
Dim sQueryStmt As String
Dim qResults As SQLDMO.QueryResults
Dim iCounter As Integer
'-----------------------------------------------------------------
' Query for a list of IDs in the database
'-----------------------------------------------------------------
sQueryStmt = ("select p.spid from master..sysprocesses p, " & _
"master..sysdatabases d where p.dbid = d.dbid and d.name= '" & _
oDatabase.Name & "'")
Set qResults = oDatabase.ExecuteWithResults(sQueryStmt)
'-----------------------------------------------------------------
' Kill all the connection based upon the ID
' If Kill its own process, the SQL will generate error. This case
' Resume next. Never kill its own process
'-----------------------------------------------------------------
If qResults.Rows > 1 Then
For iCounter = 1 To qResults.Rows
oSQLServer.KillProcess qResults.GetColumnString(iCounter, 1)
Next
End If
'-----------------------------------------------------------------
' Need to make sure that everyone has been disconnected
'-----------------------------------------------------------------
Set qResults = oDatabase.ExecuteWithResults(sQueryStmt)
If qResults.ResultSets > 1 Then
oLogger.LogErrorMessage "Was not able to remove all processes.", Err, Log_To_Both
KillDatabaseConnections = False
Exit Function
End If
KillDatabaseConnections = True
Exit Function
ErrorHandler:
Select Case Err.Number
'-----------------------------------------------------------------
' Error (-2147215398) is for attempting to kill a process that
' no longer exists
'-----------------------------------------------------------------
Case Is = cErr_KillProcessNotExist
Resume Next
'-----------------------------------------------------------------
' Error (-2147215400) is for attempting to kill your own process.
' Just ignore error and attempt to contine.
'-----------------------------------------------------------------
Case Is = cErr_KillOwnProcess
Resume Next
'------------------------------------------------------------------
' Error (-2147215397): Cannot kill something other than a user
' process, we can continue with system processes running
'------------------------------------------------------------------
Case Is = cErr_KillNonUserProcess
Resume Next
Case Else
MsgBox "Unable to kill this connection '" & qResults.GetColumnString(iCounter, 1)
KillDatabaseConnections = False
End Select
End Function
December 10, 2002 at 3:40 pm
Connection Pooling is the problem.
Append OLE DB Services = -2 to the end of your ado connection string
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply