Problem attaching a new database file to a windows form application.

Question

1.00/5 (1 vote)

See more:

I just created a new database file using SSMS and then tried to attach it to a new Windows Form Application using the Data Source app. When I Browse to the data file (.mdf) I got an error message stating I didn't have permission to use this file. So, I went in and changed the permissions on the data file. The when I want back to again attach the new database I get another error message, "File in use. Enter a new name or close the file that's open in another program." In investigating I find that the file is open in the SQL Server and I have no idea how to close this. I am using SQL Server Express 2014, Visual Studio Community 2017 Visual Basic on Windows 10 Pro. How do I overcome this?

What I have tried:

Just what I've stated, I have no Idea how to fix it.

Posted 7-Feb-18 7:50am

Member 10376725

Updated 7-Feb-18 8:30am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-02-07T08:12:00

Solution 1

Attaching databases is a special "Mode" of SQL where a new instance of the server is "spun off" to look after that one DB file, and it intended only for development so that your code can;t crash out any other database access. It isn't available in "full" SQL Server for that very reason, just in the Express edition.
When you attach a DB, a new instance of SQL Server engine is spun up and takes control of the file. Until the file is detached or the instance closed, the file is in use and will remain that way. While it's in use, SQL server has an exclusive lock on the file and you cannot open it in a different application.
I would strongly suggest that you import your DB to SQL, and let it manage it completely instead of attaching.

Posted 7-Feb-18 8:12am

OriginalGriff

Comments

Member 10376725 10-Feb-18 15:16pm

The db was created in SQL, I created it on this computer and am trying to attach it to a Windows Form so I can manipulate it with a Visual Basic program. I have gotten past the server problem but now have a new problem. Now when I try to attache it I am getting this error message.

Unable to open physical file "C:\Program Files\Microsoft SQL Server\MSSSQL12.Elyea_SQLEXPRESS\MSSQL\DATA\Temp_Emp_Log.ldf Operating System error 5: "5(Access Denied)"
An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server \MSSQL12.ELYEA_SQLEXPRESS\MSSQL\DATA\Temp_Emp.mdf Failed. A database with the same name exists, or specified file cannot be opened or it is located on UNC Share (?)

I have created and attached data files on this system previously, but the last update of Windows 10 Pro has created numerous problems with my systems. I managed to work through them all but now this rears it's ugly head and I'm not sure if it is a Windows 10 problem or a Mocrosoft SQL 2014 problem or a Visual Studio 2017 problem.

OriginalGriff 11-Feb-18 5:44am

If you created it in SQL via SSMS, or VS then it's already attached and controlled by SQL Server, and you only need to connect to it. You can't attach a DB that is already in use by SQL Server - and that is exactly what the error message is telling you.

All you need is a connection string. This may help:
https://www.codeproject.com/Tips/1198443/Simple-SQL-Connection-String-Creation