Cannot copy the mdf file after processing

I have a windows service that connects to a regular sql server 2005 database and basically bulk copies data into a SQL express database. Afterwards, I just want to copy the MDF file into a different directory. I keep getting the "Process cannot access file because it is being used by another process" error. I've tried changing the connection strings but nothing seems to work. I'm closing the connections in the code as well. Here is the code. Any thoughts or help would be appreciated. RefreshDB calls Sync 3 times and if all three calls are successful, it attempts to copy the database file to the specified location. This is where I get the error.

Public Function RefreshDB() As Boolean
Dim success As Boolean = True
Dim tables() As String = {"Job", "Equipment", "PMScheduled"}
For Each tableName As String In tables
If SyncTable(tableName) = False Then
success = False
Exit For
End If
Next
If success Then
'copy the new database to the target directory
Try
File.Copy(My.Settings.DBFilePath, My.Settings.TargetDirectory + "\EMField.mdf")
Catch ex As Exception
My.Application.Log.WriteEntry(ex.Message + "(RefreshDB)", TraceEventType.Critical)
Return False
End Try
End If
Return success
End Function

Private Function SyncTable(ByVal tableName As String) As Boolean
Dim reader As SqlDataReader
Dim sourceViewName As String = String.Format("EMField{0}View", tableName)
Dim connectionString As String = My.Settings.EMFieldConnectionString
'insert the path to the database into the connectionstring
connectionString = connectionString.Replace("[DBFilePath]", My.Settings.DBFilePath)
Try
'clear the target table first
Using targetConnection As New SqlConnection(connectionString)
Using truncateCommand As New SqlCommand(String.Format("TRUNCATE TABLE {0}", tableName), targetConnection)
targetConnection.Open()
truncateCommand.ExecuteNonQuery()
targetConnection.Close()
End Using
End Using
'create a datareader from the source database
Using sourceConnection As New SqlConnection(My.Settings.EMLiteConnectionString)
Using readCommand As New SqlCommand(String.Format("SELECT * FROM {0}", sourceViewName), sourceConnection)
sourceConnection.Open()
reader = readCommand.ExecuteReader
BulkCopy(tableName, reader, connectionString)
reader.Close()
sourceConnection.Close()
End Using
End Using
Return True
Catch ex As SqlException
My.Application.Log.WriteEntry(ex.Message + "(sync)", TraceEventType.Critical)
Return False
End Try
End Function

Public Sub BulkCopy(ByVal tableName As String, ByRef reader As SqlDataReader, ByVal connectionString As String)
Try
'bulk copy from source to target database
Using bulkCopy As New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = tableName
bulkCopy.WriteToServer(reader)
bulkCopy.Close()
End Using
Catch ex As SqlException
'throw exception back to calling sub
Throw ex
End Try
End Sub

hi,

the database is probably still in use and an active connection is still referencing it, so even if the database autoclose property is set (for SQLExpress created databases), the physical file(s) is/are still locked and file system operations are not allowed..

first verify no active connection is still alive.. consider that connection pooling will keep them in the pool for about 1 minute after the connection has been disposed and released..

again, even if the autoclose property set is the default for SQLExpress instances, consider first "detaching" the database via sp_detach_db before performing file system operations...

regards

|||

Thanks for your help. I can say with certainty that no other process is connected the sql express database because I am testing it on my own machine. That leaves the connection pooling, but I put pooling=false in the connection string. I have never "detached" a sql express database before. So where exactly (ie what database) do I run this sp_detach_db and are there any consequences in doing so? In other words when the service runs again, will I need to reattach the database before connecting to it?

Thanks,

Bob

|||

hi Bob,

Coach24 wrote:

Thanks for your help. I can say with certainty that no other process is connected the sql express database because I am testing it on my own machine. That leaves the connection pooling, but I put pooling=false in the connection string. I have never "detached" a sql express database before. So where exactly (ie what database) do I run this sp_detach_db

sp_detach_db "unregister" the database from it's related SQL Server instance so that file system operations are allowed on the database's physical files..

and are there any consequences in doing so? In other words when the service runs again, will I need to reattach the database before connecting to it?

Thanks,

Bob

no consequences but, obviously, any connection to the unregistered database will fail until you "reattach" it via the

USE [master]

GO

CREATE DATABASE [test] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\test.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\test_log.LDF' )

FOR ATTACH;

GO

statement... so, yes, you have to reattach the database before referencing it..

regards

|||

While I'm certain your suggestion works, I ended up not needing it. As it turns out, even though I had pooling = false in the connection string, and I closed the connection in the code, the connection was still open when I attempted the File.Copy statement. I put a System.Threading.Thread.Sleep(5000) right before the File.Copy statement and that fixed the problem. Thanks again for your help.

Bob