Problem copying SQL DB file

Hi,

I have a VB project which uses SQL Server 2005 Express. When the project first runs, it copies its DB files from the data directory to a directory created on the C: drive. This works fine.

In a later part of the application, it is necessary to copy the DB file to another location e.g. memory stick so that it can be used to merge data onto another PC running the same application.

The data merging works too.

My problem is, despite the fact that it worked okay this morning, I am now unable to copy the DB into a second location (memory stick or location on local disc). I get an exception stating that the file is in use.

I have no other applications running (I shut down Visual Studio and was running the installed version).

What is more, the form that implements the file copying, first closes down all other forms in the application to ensure that nothing is connected to the DB.

Furthermore, the form that performs this task has no DB connections whatsoever - it simply allows the user to select a location, then creates a directory and copies the DB files to it.

If I copy/paste the code plus the FolderBrowserDialog into the first form that copies the original database out of the data directory - it works!

The code for the whole form is below - I would be hugely grateful for any ideas ...

Public Class Export

Private Sub Export_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub

Private Sub NextBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextBtn.Click

'Close all other windows to ensure that the databases are not in use.

Dim Counter As Integer = My.Application.OpenForms.Count

Do While Counter > 1

For Each f As Form In My.Application.OpenForms

If Not (f.GetType Is GetType(Export)) Then

f.Close()

Exit For

End If

Next

Counter -= 1

Loop

' Set the Help text description for the FolderBrowserDialog.

Me.FolderBrowserDialog.Description = _

"Select a location to export the data to e.g. a memory stick. Or, you can export to a local directory and then burn to CD or e-mail to another user."

' Do not allow the user to create New files via the FolderBrowserDialog.

Me.FolderBrowserDialog.ShowNewFolderButton = False

' Default to the My Documents folder.

Me.FolderBrowserDialog.RootFolder = Environment.SpecialFolder.MyComputer

Dim result As DialogResult = FolderBrowserDialog.ShowDialog()

Dim foldername As String

If result = DialogResult.OK Then

foldername = FolderBrowserDialog.SelectedPath

Try

If Not My.Computer.FileSystem.DirectoryExists(foldername & "\ClubSecExportMerge") Then

My.Computer.FileSystem.CreateDirectory(foldername & "\ClubSecExportMerge")

MsgBox("Merge Directory created.")

End If

My.Computer.FileSystem.CopyFile("C:\Clubsec\ClubSecDB.mdf", foldername & "\ClubsecExportMerge\MergeDB.mdf", True)

My.Computer.FileSystem.CopyFile("C:\Clubsec\ClubSecDB_log.ldf", foldername & "\ClubsecExportMerge\MergeDB_log.ldf", True)

MsgBox("Database merge files successfully copied to " & foldername & " \ClubSecExportMerge.", MsgBoxStyle.OkOnly, "Operation successful")

Catch ex As Exception

MsgBox("There was an error." & Chr(13) & "Export of database was not achieved." & Chr(13) & ex.ToString, MsgBoxStyle.OkOnly, "Operation failed.")

Exit Sub

End Try

Else ' Cancel button pressed

MsgBox("No path selected. Data not exported", MsgBoxStyle.OkOnly, "Operation cancelled.")

End If

Dim home As New HomeScreen

home.Show()

Me.Close()

End Sub

End Class

Accroding ot the mdf and ldf file your are using a SQL Server instance for storing some data. are the mdf files attached to any SQL Server ? If yes, is it a user instance or a server instance. If you don′t know post your connection string of your application here. The file is probably in use by any SQL Server instance.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

Thanks for the response. I don't know much about user instances but I thought they defaulted to False. When I looked at the connection strings, they are true. So I changed them to false. The app ran but I still couldn't copy the files to a new directory.

I noticed that in the Database Explorer, the database filename shown was different to what was intended and referred back to the project, within my Visual Studio directory. I changed this to point at the database files on the C: drive and now nothing works.

I get the first exception below when I try to run the app. Yesterday, most of it worked! I could even get the file copying to work if I pasted the code into a different form. Now nothing works!

The current connection string is as follows:

Data Source=.\SQLEXPRESS;AttachDbFilename=C:\ClubSec\ClubSecDB.mdf;Integrated Security=True;User Instance=False

Just by way of information, I will explain a little more about what I am trying to achieve. I'm new to VB and SQL Express and only know the stuff learned from the online videos and a book. I created the application without worrying about connection strings, user instances etc as they were all handled by the IDE. However, I then ran into a problem - if I update the app, the database files will overwrite the user data in the data directory. Seriously bad news!

Rather than try to be too clever, I thought ok, all I need do is chek whether a directory called C:\ClubSec exists on app startup. If it doesn't, it is the first ever run - create the directory and copy the DB files to it from the data directory and use these files in future. That way, any app updates won't affect the user data. This all semed to work fine.

I now wanted to add an Export/Import data feature as different users of the app could be changing data and there would be a requirement to merge it. To achieve this, the user is allowed to select a directory on any drive (intended to be a memory stick in real life) at which point their DB files are copied to a directory called ExportMerge in the chosen location. This worked yesterday and will work if I paste the code into a different form other than the one it's in!

Next, for an import, the ExportMerge directory is located and the contents copied to the Import Merge directory within Clubsec. This works fine and the merge happens (except for a few logic bugs that I've been too busy to address yet!)

Excuse me while a go and find a convenient wall upon which to bang my head several times!

UPDATE:

I have read that ADO.net maintains open connections sometimes which are eventually discarded. I have discovered that if I close all forms with data access then use a timer to try a copy my directories every so often, after 5-6 minutes they are freed up and I can perform the copy. I also tried to disable pooling but this had no effect although I can't be sure that I successfully disabled it because any time I change any connection properties - they change back again!

Regards,

Rich

First message

System.Data.SqlClient.SqlException was unhandled
Class=16
ErrorCode=-2146232060
LineNumber=65536
Message="Unable to open the physical file "C:\Clubsec\ClubSecDB.mdf". Operating system error 32: "32(error not found)".
Unable to open the physical file "C:\Clubsec\ClubSecDB_log.ldf". Operating system error 32: "32(error not found)".
Cannot open user default database. Login failed.
Login failed for user '048789520110\Richard'.
File activation failure. The physical file name "C:\Clubsec\ClubSecDB_log.ldf" may be incorrect."
Number=5120
Procedure=""
Server=".\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=101
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at ClubsecDev3.ClubSecDBDataSetTableAdapters.UsersTableAdapter.Fill(UsersDataTable dataTable) in D:\Documents and Settings\Richard\My Documents\Visual Studio 2005\Projects\ClubsecDev3\ClubsecDev3\ClubsecDev3\ClubSecDBDataSet.Designer.vb:line 7521
at ClubsecDev3.Logon.Logon_Load(Object sender, EventArgs e) in D:\Documents and Settings\Richard\My Documents\Visual Studio 2005\Projects\ClubsecDev3\ClubsecDev3\ClubsecDev3\Logon.vb:line 167
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ClubsecDev3.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

Then, as an experiment, tried to close the connection in Database explorer and got this one:

System.Data.SqlClient.SqlException was unhandled
Class=11
ErrorCode=-2146232060
LineNumber=65536
Message="Cannot open user default database. Login failed.
Login failed for user '048789520110\Richard'."
Number=4064
Procedure=""
Server=".\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at ClubsecDev3.ClubSecDBDataSetTableAdapters.UsersTableAdapter.Fill(UsersDataTable dataTable) in D:\Documents and Settings\Richard\My Documents\Visual Studio 2005\Projects\ClubsecDev3\ClubsecDev3\ClubsecDev3\ClubSecDBDataSet.Designer.vb:line 7521
at ClubsecDev3.Logon.Logon_Load(Object sender, EventArgs e) in D:\Documents and Settings\Richard\My Documents\Visual Studio 2005\Projects\ClubsecDev3\ClubsecDev3\ClubsecDev3\Logon.vb:line 167
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ClubsecDev3.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()