I have been using MS SQL Server Management Studio Express to attach and detach my database for some time and have not had any problems until today. When I tried to attach a database, MS SQL Server Management Studio Express gave me error 5123:

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

Additional Information:

…

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:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xxxx.mdf’. (Microsoft SQL Server, Error:5123)

I tried the following methods but none of them could fix the problem:

• Close out Server Management Studio Express and re-launch it to attach the database
• Close out Server Management Studio Express and Visual Studio 2005, then re-launch Server Management Studio Express to attach the database
• Reboot my computer
• Completely shut down my computer
• Restart “SQL Server (SQLEXPRESS)” service
• Login SQL Server Management Studio Express with sa account

After several hours of trial and error, I finally figured it out. I have both MS SQL Server 2000 and SQL Server 2005 Express installed on my computer, and somehow SQL Server 2000 is holding my database which causes the above error when SQL Server 2005 Express tries to attach the same database. So I stopped “MSSQLSERVER” service (for SQL Server 2000) and “MSSQLSERVER (SQLEXPRESS)” (for SQL Server 2005 Express), restarted “MSSQLSERVER (SQLEXPRESS)”, and then launched SQL Server Management Studio Express and successfully attached my database.

My questions are: how come I didn’t have this probem before? How come SQL Server 2000 can use a SQL Server 2005 database?