Modifying conection string to connect to multiple installed instances of SQL Server

I am struggling to figure out how to connect to multiple instances of SQL Server with my VB.NET application. I have SQL Server 2008 and 2012 installed on my machine. If I try to open an SQL Server 2008 database, it automatically opens it in 2012, updates it, and renders it unusable to SQL Server 2008. Is there a way to modify my connection string to open a 2008 database in SQL Server 2008, and a 2012 database in SQL Server 2012? I have found out how to differentiate the file version already.

My connection string is as follows:

Code:

---

m_cn.ConnectionString = "Server=.\SQLEXPRESS; AttachDBFilename=" & strFileName & ";Integrated Security = True; Connect Timeout = 30;" & "User Instance = True"

---

Thanks

Am I missing some great complication here? You have a connection string for each version and you apply it according to your file version detection method with a simple If. If you are correctly opening connections only when needed and closing them immediately after the necessary functions have completed then there will be no problems with this at all.

No, I think the problem is that I am missing something simple here.

I have attempted to create specific connection strings depending on the version of SQL Server to be used, but I am struggling to figure out the server name to connect to for SQL Server 2008. The ".\SQLEXPRESS" works just fine for the SQL Server 2012 version. I know this is simple to do, I think I am just overlooking what I need to be doing here. Any suggestions?

Ok - this is really simple

You have two installs of SQL - and one is called .\SQLEXPRESS - and that one is for the 2012 version.

What is the instance name of the SQL 2008 version you have installed??

Ah right. This is what you get for accepting default installations! You'll need to change the server name for one of the versions so that you can refer to it distinctly in the connection strings. What information I've gleaned suggests that the easiest way to do that is to reinstall and name at the appropriate stage. There is a non-reinstall option suggested here but as my own SQL installation is mush at the moment (I'm waiting for a new computer to do a complete overhaul - it may be rather a long wait unfortunately!) I'm not able to test it..

Quote:

---

Originally Posted by szlamany

Ok - this is really simple

You have two installs of SQL - and one is called .\SQLEXPRESS - and that one is for the 2012 version.

What is the instance name of the SQL 2008 version you have installed??

---

Er .... .\SQLEXPRESS, I'll warrant!

I've got three installs of SQL on this laptop - it takes several minutes to boot now - I really could use a new machine with a fresh O/S - wish I had the time (and funds!)...

Look at that bloat...

Gotcha. I thought that I had this working before, just by changing the "SQLEXPRESS" instance name to the instance name of the SQL Server 2008 installation. I did not realize that accepting a default installation would cause this trouble. The other instance name was what I thought to be MSSQLSERVER, but this does not work. When I log into the SQL Server 2008 program, the Server name is "(local)", and this doesn't seem to work too well in a my connection string.

I will try to reinstall these versions and enter specific instance names. Is the default instance the (local) or the SQLEXPRESS? Or is this up to me to figure out?

Try using the MACHINE name - not a "." - to connect to SQL.

That is the default instance - no name at all. That is what you see as MSSQLSERVER in my attached image.

\SQLExpress is a named instance - name is \SQLExpress - and I'm sure when you install SQL Express version it defaults to a named instance and said name is \SQLExpress

As you can see I named one \SQL2012...