I am upgrading to SQL Server 2012 on one machine and adding a new instance to another. Then I want to find the data using proprietary software. The upgrade went fine. The new install went fine but when I try to access it, I get an error.
On the upgrade, the data lies here: C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA and I "get at it" with this connection string: conn.Open "Provider=SQLNCLI10;Server=" & Environ("computername") & "\SQLExpress;Database=xxx;Trusted_Connection=yes;"
On the new install the data lies here: C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA Obviously the old connection string won't work. So here are my questions:
1) Why the new location? Note that the upgrade has the same directory path as the new install but the data resides in the other directory and sql server finds it.
2) I want the same software to run effectively on both computers which means that, unless I find a way to adjust the data home on one or the other I will need to create two different connections strings depending on the computer being used.
Any help would be much appreciated!