I'm getting a strange SQLException

[Yuliya]

I have a Windows Form project which accesses an SQL database. It was working for a while, and now, all of a sudden, every time I run the project and it tries to load data from the database, it throws the following exception:

System.Data.SqlClient.SqlException was unhandled
Class=14
ErrorCode=-2146232060
LineNumber=65536
Message="An attempt to attach an auto-named database for file C:\Documents and Settings\Yuliya\My Documents\Visual Studio 2005\Projects\PesachSoftware3\PesachSoftware3\bin\Debug\PesachDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
Number=15350
Procedure=""
Server="\\.\pipe\584316AB-FBCD-4F\tsql\query"
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.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
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 PesachSoftware3.PesachDBDataSetTableAdapters.test_tableTableAdapter.Fill(test_tableDataTable dataTable) in C:\Documents and Settings\Yuliya\My Documents\Visual Studio 2005\Projects\PesachSoftware3\PesachSoftware3\PesachDBDataSet.Designer.vb:line 9412
at PesachSoftware3.Form1.Form1_Load(Object sender, EventArgs e) in C:\Documents and Settings\Yuliya\My Documents\Visual Studio 2005\Projects\PesachSoftware3\PesachSoftware3\Form1.vb:line 5
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 PesachSoftware3.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()


I even tried to create a completely new project and use the same database, and it did the same thing. Help! I am really lost.

 

[jmcilhinney]

You're trying to attach an MDF file to an SQL Server Express instance when that instance already has a database with the same attached. If you don't actually have another database with the same name then it would indicate that your database has failed to detach correctly. I had issues like this at one point when my project crashed and the database failed to detach. I'm not sure what the specifics of the issue were but to fix it at the time I had to delete the offending database from my Debug output folder. In order to do that I had to log off and back on again. Not ideal but it got the job done.

 

[Yuliya]

I tried deleting the database from the Debug folder. Didn't work.

Then I tried changing Copy to Output directory property for the .mdf file to Copy if newer. It worked!

Originally, I set it to Do not copy. But I don't want to keep it as Copy if newer. Is there a way to get it to work with Do not copy?

 

[jmcilhinney]

Why would you want to use Do Not Copy? If it's set to Copy If Newer then it will only ever be copied if you make changes to the original, at which point you'd surely want to create a new copy.

 

[Yuliya]

I read in the help files that Copy if Newer is not recommended, because something might accidentally get changed in the original database, and that would mess up the program.

 

[cjard]

I read in the help files that Copy if Newer is not recommended, because something might accidentally get changed in the original database, and that would mess up the program.

Copy if Newer is preferred, otherwise every time you update the schema in response to user requirements or whatever, the changes arent seen in your app!

 

[jmcilhinney]

I read in the help files that Copy if Newer is not recommended, because something might accidentally get changed in the original database, and that would mess up the program.

I've read that too on the Microsoft site and it's, quite frankly, a crock. The default is Copy Always and that has caused thousands of people to post on forums asking why their data isn't saved to their database when it actually is. It is partially their own fault for not actually testing to see if the data was saved when they saved it, but still...

If you use Copy Never then you'll keep using the same test data indefinitely, even if you make a change to the database schema. You'll never see that change unless you either copy the file manually or else change the property value.

Copy If Newer makes perfect sense. You keep using the same test data over and over. If you want to refresh your test database you just delete the copy and a new one will be created next time you run. Any time you change the data or the schema in your original database you will immediately see those changes in your test database the next time you run as a new copy will be created. It also means that every time you build a Release version you'll be using the most up-to-date version of the database.

 

[cjard]

I've read that too on the Microsoft site and it's, quite frankly, a crock. The default is Copy Always and that has caused thousands of people to post on forums asking why their data isn't saved to their database when it actually is.

Hear here!

Any time you change the data or the schema in your original database you will immediately see those changes in your test database the next time you run as a new copy will be created.

And we have a policy to strangle people who come complaining that they updated the schema of their clean-room database and then suddely "it wiped all the test data they had saved in it previously"