Why can't I open the same database files (.mdbs) in multiple environments at the same time?
This variety. If I connect to a SQL Server database (.mdb file) via VS.Net Server Explorer, then I cannot connect to the same file via SQL Server 2008 Management Studio in the same file. The file is locked.
For example, I am getting the following error from SQL Server Management Studio
CREATE FILE encountered system error 32 (The process cannot access the file because it is being used by another process.) While trying to open or create the physical file 'C: \ SQL Server 2000 Sample Databases \ NORTHWND.MDF'. (Microsoft SQL Server Error: 5123)
I am playing with LINQ to SQL. So you will need to connect to VS.Net server to drag and drop your table objects and create .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. Same if I try to connect using LINQPad (great tool ... but useless thanks to this annoying file locking).
And does anyone know how I can connect to the same .mdb file at the same time from multiple programs like VS.Net Server Explorer, SQL Server Management Studio and LINQPad?
Thank!
By the way, this site is absolutely amazing and I love the fact that they made it in ASP.Net and used LINQ to SQL to access data ... well, off topic. Unfortunately.
The key is in the name "SQL Server " - it seems that you cannot attach a DB to two places, which are not completely unreasonable, because you cannot serve it twice (given that the server is responsible for things like transactions, logs, blocking, etc.).
So...
You want to "start" a database in an instance of SQL Server and then connect from Visual Studio to that instance and then to a file, rather than open the file directly (there is an option because it simplifies some deployment models), whereby you should get what you need.
As an aside, it is entirely possible to have "old school SQL" from Visual Studio, although I am not aware of the LinqPad equivalent
a source to share