Problem attaching to main instance of Database in Release Mode

Question

I am having a very starnge problem trying to attach to my SQL Express 2008 database when in release mode. I built the database KM.mdf ( in VS2008 ) and created the dataset, which originally created a connection string for a user instance ( by default ). I used this user instance of the database fine, and then decided that I need to use the main instance of the database. Firstly I just changed the 'User Instance' setting in the connection string to False but this problem appeared. I can run my application fine in Debug mode, but if I change to Release mode I get the "An attempt to attach an auto-named database for file [C:\Users\.......................\bin\Release\KM.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."error message. I have removed the dataset and connection strings ( even in the app.config file ) and created them again but that doesnt help. I have opened Server Management STudio, and originally it showed a database attached from the Debug Folder ( which reappears if I run in Debug mode ) but never anything from the Release folder.
Any ideas please, I am new to using databases so please spell it out! 

Answer 1

It's hard to tell what's happening without seeing your connection string, but based on the error you're using the AttachDBFilename keyword in your connection string and it's trying to auto-attach your mdf file and create a database name based on the path, but that path/name has been previously attached so you're getting the error.

If you're going to use the Parent Instance of SQL Express with your application it is more correct to attach the database to the SQL Express instance manually and re-write your application to connect to that database by name rather than trying to auto-attach using AttachDBFilename. You can find details about how to attach a database manually in Books Online, search for CREATE DATABASE ... FOR ATTACH.

Regards,
Mike

---

If this post answers your question, please mark it as an Answer - it will help others searching the forum. This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Yes you are right I am using a connection string created in the Data Source Configuration wizard of VS2008 and it indeed uses AttachDBFilename. I understand what you say about the right way to use the Parent instance ( which I am doing so that when I give the app to clients, they can use it on their LAN and have another machine connect to it ) is to connect manually. Apologies for keeping it basic, but how does that work regarding deployed software? Obviously the client cannot be expected to manually ( through coding ) attach it themselves & how do I create a Dataset ( which I have already in my app ) with the Data Source Config Wizard or otherwise in VS2008 without specificying a connection string there?

Thanks again Mike

Answer 3

Hi,

One of user instance limitations is that only local connections are allowed. If you want to open a user instance connection on a client machine, you need to install SQL Server Express on the client machine and attach the user instance to the local SQL Server Express instance. The connection string is like:
<connectionStrings>
     <add name="TestVB1.Settings.Database1ConnectionString"
          connectionString="Data Source=.\SQLEXPRESS;
          AttachDbFilename=|DataDirectory|\Database1.mdf;
          Integrated Security=True;
          User Instance=True"
          providerName="System.Data.SqlClient" />
</connectionStrings>

Additionally, when you deploy you application, you need to include the database file in the project folder.

If there are any more questions, please let me know.
Thanks.

---

***Xiao Min Tan***Microsoft Online Community***

Answer 4

Sorry I obviously wasnt clear but the very problem I have been having was getting away from user instances! I am creating an application to be sold online that requires the use of a database either just for the single machine setup (with multiple users potentially) or also for multiple-machine setup say on a small office LAN. I have therefore used SQL Express 2008 so that the later can be achieved. Following Mike's advice I have managed to attach the database manually to SQL Express ( I used Management studio but also understand how to do it via the comman prompt ) and can now use the application in Release mode , connected to the main instance. That is great but as a newcomer to all this I would really appreciate you explaining the following:

1) I understand that my customers will require SQL Express on their main machine to run my application. Is there a way in which it can be coded so that the setup installation file loads SQL Express onto their machine and attaches it to the database ( that is also in the installation file ) automatically so that they are not required to do any command line coding ( e.g. CREATE DATABASE...FOR ATTACH ) themselves, because that is not what someone expects to have to do when they buy a bit of software.

2) When my customers want to connect other computers to the database via the LAN. is there a way of coding this situation within my application so that in their version of the app they can say click a checkbox, or perhaps identify a location of the SQL Express on the main machine, and it connects to the main computer automatically or do they also HAVE to be required to do some command line coding themselves?

3)I am aware that any other computer that want to attach to the main computer on the LAN and run the application would not need SQL Express installed -  would I need to offer two versions of the software, one for the main machine and one for any remote connectors?

Any help in pointing me in the right directions would be very much appreciated as right now it is slightly overwhelming!

Regards,
Paul

Answer 5

Hi,

If you don’t want to use the user instance, you only need to attach the database to an SQL Server instance on your server machine.  To connect the database which is hosted by the server on each client, you could configure a correct connection string and don’t need to attach the database again.  For more information about connection string, please see http://www.connectionstrings.com/sql-server-2005.

If there are any more questions, please let me know.
Thanks.

---

***Xiao Min Tan***Microsoft Online Community***