VS2010 SQLEXPRESS 2008 - file used by another process

[tgreer]

I'm coding a project in C#, VS2010, using SQL Express 2008 as the data source. Whenever any other application connects to the database (such as Management Studio), the next time my C# application runs, it cannot access the database.

The error is "Operating system error 32".

Unable to open the physical file "... .mdf".

The process cannot access the file because it is being used by another process.

Cannot open user default database. Login failed.

It doesn't matter what other application touches the database, my app will fail unless and until I restart the SQL Server service.

I use SSMS, and I also have created a DSN to the database for a third party application to query and update. When either of these run (and they do, with no errors), then I have to restart the SQL Server service before I run my C# app.

I'd obviously like to understand why this is so and fix it!

[Momerath]

It sounds like your application is not properly disposing of the SQL connections.

[tgreer]

Interesting. All of the data-related objects are auto-generated, I would hope that VS2010 would properly dispose objects it creates!

The error occurs at Form Load with this code:

private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'timeTrackDataSet.project' table. You can move, or remove it, as needed.
            this.projectTableAdapter.Fill(this.timeTrackDataSet.project);
        }

Which objects would I close/dispose, and at what event?

[tgreer]

To reiterate, Sql Server Management Studio has no problems connecting to the database after the C# app runs, nor does any app which uses the DSN... but if run my app, then run SSMS, then run the app again, I get the error.

[Momerath]

I would suspect that it is SSMS then that isn't releasing resources.

[tgreer]

Not so. I'm not being clear, I guess. I have two external applications which use this database. SQL Server Management Studio is one of them. I have a document processing workflow tool called PlanetPress which can attach to a DSN as a DataSource. I can run either of these, or even both at the same time, with no issues. As a further test, I just created an MS-WORD Merge that uses this DB as a data source. No issues.

But after running any of these applications, the C# app fails on the .Fill() method with the error in my original post. If I restart SQL Server, it will run, but only as long as nothing else accesses the DB.

So, I'm thinking it is something about VS2010 Express and how it attaches data sources. I ran the Data Source Wizard, and the only choice for SQL was "Microsoft SQL Server Database File". Does that fact that VS2010 Express has no option for SQL Server per se, but only a "Database File" mean that it requires EXCLUSIVE access to the database?

[Momerath]

I believe VS Express only has the ability (built in, you can always use your own connection string) to attach to SQL Compact edition databases (which is file based), so that is why you only get the database file option in the Data Source Wizard.

This doesn't explain why it would want exclusive access to SQL Express edition, though. Have you looked at the exception you get, including the inner exception? It might provide more information on what is happening and how to solve it.