Why can't I open the same database files (.mdbs) in multiple environments at the same time?

Question

This kind'a sucks. If I connect to a SQL Server DB (.mdb file) through VS.Net's server explorer, then I can't connect to the same file via SQL Server 2008 Management Studio at the same file. The file is locked.

For example, I get the following error from SQL Server Management Studio

CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file 'C:\SQL Server 2000 Sample Databases\NORTHWND.MDF'. (Microsoft SQL Server, Error: 5123)

I'm playing around with LINQ to SQL. So you would need to connect to VS.Net server explorer to drag in your table objects and create the .dbml files. However, if I want to query the database using old school SQL from management studio, I can't... I get the above error. Ditto if I try to connect using LINQPad (great tool...but useless thanks to this irritating file lock).

So does anyone out there know how I can connect to the same .mdb file from multiple programs like VS.Net's server explorer, SQL Server's management studio and LINQPad all at the same time?

Thanks!

By the way, this site is simply awesome and I love the fact that they made it in ASP.Net and used LINQ to SQL for data access... okay, off topic. Sorry.

Answer 1

The sql engine will lock the file once connected to it. nothing prevents you from attaching a file to an instance of SQL server and then connect to it from multiple applications.

Answer 2

The clue is in the name "SQL Server" - its seems that you can't attach the DB to two places which is not entirely unreasonable because you can't serve it twice (given that the server has responsibilities for things like transactions, logs, locking, etc).

So...

You want to "run" the database in an SQL Server instance and then connect from Visual Studio to that instance and then to the file rather than open the file directly (the option is there because it simplifies some deployment models) at which point you should get what you need.

As an aside, its perfectly possible to to "old school SQL" from within Visual Studio - although I'm not aware of an equivalent to LinqPad