Trying to Connect to SQL Express with Visual Studio

[Buffie]

Using the connection wizard I am trying to connect to a SQL Express database and I get the following error(s):

++++
Unable to open physical file "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\myfile.mdf". Operating system error 32: 32(The
process cannot access the file because it is being used by another process.)"
An attempt to attach an auto-named database for file "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\myfile.mdf" failed. A database
with the same name exists, or specified file cannot be opened, or it is
located on UNC share.
++++++

I am not trying to create a web project I want to create a simple windows form and use the data found in the database. I have found many references to this problem but all seem to deal with a web application which is not what I am doing. Thank you in advance......

 

[SQLBill]

It sounds like your application is trying to CREATE another database using that database file.

-SQLBill

Posting advice: FAQ481-4875

 

[Buffie]

Being new to all of this, is there a way that I can check to see if it is creating a new database or a way to stop it from creating a new one. TIA.

 

[SQLBill]

Check your connection code. Is it trying to create the database? Is it trying to connect to the file itself (the .mdf) instead of the database?

You might want to consider posting your connection code string.

-SQLBill

Posting advice: FAQ481-4875

 

[Buffie]

This is the connection string the connection wizard is trying to use:

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\CommonFiles\SecretarySystem\SecretarySystem_Data.MDF";
Integrated Security=True;Connect Timeout=30;User Instance=True

I read in some other forum that maybe the solution would be making User Instance=False but I don't have any idea how to change it and let Visual Studio try to connect that way. Is there a way I can do that? TIA.

 

[SQLBill]

I don't use Visual Studio and am not sure how the coding should look (you might try and see if there is a forum on this site for Visual Studio), but one thing 'pops out'. Yuo are attaching to a database file and not the database. In SQL Server, attaching a database file means that the database is offline and unavailable. Then you attach the files to make it available. With the error you are getting, that command (AttachDBFilename) seems to be doing the same thing. Since it is already online (attached) you can't attach it again and get the error you reported.

-SQLBill

Posting advice: FAQ481-4875

 

[Buffie]

Again I trying to find a solution to my dilema but the following works with a sample data base that I used prior to my real data. It is the same connection string as mine except the data of course. Again any enlightment from anyone would be appreciated.

Works:
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

Does Not Work:
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Common Files\SecretarySystem\SecretarySystem_Data.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True

Ug. TIA

 

[SQLBill]

Check the properties of AdventureWorks database. Right click on the database and go to Properties and then Options. See if the database is set for AutoClose. If so, check the SecretarySystem database to see if it is set to AutoClose.

-SQLBill

Posting advice: FAQ481-4875

 

[Buffie]

They both have AutoClose set to True.....

 

[SQLBill]

Okay, with AutoClose set to TRUE, that means when you close your connection to the database it is placed offline. Then the next connection places the database back online. With a DEMO database like AdventureWorks, this might work since very few people will be using it at the same time. With any database that several people are using, AUTOCLOSE is a bad idea. Your connection is expecting the database to be offline and is trying to bring it back online. It is in use by someone, so it is already online and you can't bring it online again.

So, you don't want to AttachDBFilename. You just want to set up a connection.

Now, I don't use those commands, so I can't help you further....but let me give you these suggestions.

1. find the syntax to connect to the database, not open it.
2. request the dba or owner set the database to AUTOCLOSE = FALSE.
3. if you can't do #2, find a way for your connection string to test to see if the database is online - then if it is online connect and if it is not online attach it.

-SQLBill

Posting advice: FAQ481-4875

 

[Buffie]

I will try to follow your instructions. I certainly appreciate all you have tried to do for me. Thank you very much.