Connection String Question

Question

In connectionstrings.com it says this (quote below) -- amongst many other things.

Quote below draws attention to the fact that the connection string in question ONLY works on SQL Server 2005.

I am working with Vista Professional + Visual Studio 2005 + SQLserver 2005.

Users of the software I am writing will have  problems because they won't have . . . 2005.

How should I change what I am doing so the application, when done, will run on different boxes?

Many thanks,

Zach24/7

 

" ..... Using an User Instance on a local SQL Server Express instance

The User Instance functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 2005 instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrative rights on the computer.

    

Data Source=.\SQLExpress;Integrated Security=true;
AttachDbFilename=C:\MyFolder\MyDataFile.mdf;User Instance=true;

To use the User Instance functionality you need to enable it on the SQL Server. This is done by executing the following command: sp_configure 'user instances enabled', '1'. To disable the functionality execute sp_configure 'user instances enabled', '0'. ....."

Answer 1

Hi ,

see as below :

Connection strings in Visual Studio 2005

http://www.codeproject.com/Articles/10846/Enhancements-done-for-storing-connection-strings-i

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 2

Hopefully what I am writing will be sold, but then there could be problems because users will not have . . . 2005.

How should I change what I am doing so it will work on different boxes?

The issue isn't really related to the connection string.  You just have to make sure SQL Server 2005 Express is installed and configured before your application is usable.  You could either provide the user with download and installation instructions or redistribute/embed with your application.

If you don't need the features of the full database engine, you might consider using SQL Server Compact Edition.  That will greatly simplify the installation.  If you need the full engine, consider moving to SQL Server 2012 Express.  Mainstream support ended for SQL Server 2005 Express over a year ago.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 3

How should I change what I am doing so the application, when done, will run on different boxes?

....

The User Instance functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 2005 instance and only when connecting using windows authentication over local named pipes.

Hello,

You are using a "User Instance" of SQL Server (already deprecated feature), which works only on a local machine (as the message says) and this mean, your app works only in single-user mode; you can't use it with several boxes in a network.

You have to attach the database permanetly to the SQL Server, enable remote connections and change the connection string, so it connects to the one SQL Server and the attached database.

---

Olaf Helper

Blog Xing

Answer 4

Hi Olaf, Re: "How should I change what I am doing so the application, when done, will run on different boxes?" Apologies: I used poor English. What I meant to say was that I want to ensure that the application in question is installable on a client system. Additionally that client system might be on a LAN with the database on a network drive. So in fact there are two issues.   

Answer 5

Hi Dan, I will have to investigate the difference between "SQL Server Compact Edition" and "Sql Server 2005", and find out what "full functionality" means.  I was hoping that a different connection string would make the application independent of "OS- + SQLServer-versions", given that requirements are simple: two tables that are not inter-linked, for writing data, retrieving data and updating data. Multi-using should be enabled.

Answer 6

I was hoping that a different connection string would make the application independent of "OS- + SQLServer-versions"

The connection string is fully independent of the OS + SQL Server version, it depends only on the machine / SQL Server instance name.

---

Olaf Helper

Blog Xing

Answer 7

Olaf wrote "The connection string is fully independent of the OS + SQL Server version, it depends only on the machine / SQL Server instance name."

My connection string is "Data Source=.\SQLExpress;Integrated Security=true;
AttachDbFilename=C:\MyFolder\MyDataFile.mdf;User Instance=true;"

BUT -- SQL Compact is not SQLExpress.

So I would have thought that the connection string would need to be different, or am I missing your point?