Sql Server 2012 Express Install Issue

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!
Bob SchneiderCo-OwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
The location of the data files is totally irrelevant:  They could be anywhere or even multiple places.  Once you log on that will be resolved, by which instance / database you are connected to.
0
HuaMin ChenProblem resolverCommented:
Please ensure your connection string is something like
	<connectionStrings>
		<add name="conn2" connectionString="Data Source=192.?.?.?\your_db_instance;Initial Catalog=your_db_schema;Integrated Security=False;User ID=your_db_login;Password=??????" providerName="System.Data.SqlClient"/>
	</connectionStrings>

Open in new window


within Web.config/app.config file in your project, to ensure the project will work fine with the database.
0
Bob SchneiderCo-OwnerAuthor Commented:
My other connection string was working fine.  Just wondering why it isn't now?  I don't have a Web.config/app.config file in my project.  It is a vb6 project.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

HuaMin ChenProblem resolverCommented:
What did you change currently?
0
Bob SchneiderCo-OwnerAuthor Commented:
I haven't changed anything yet since upgrading from Sql Server 2008 r2 Express to Sql Server 2012 Express.  Works on most machines...just having issues on this one.
0
Bob SchneiderCo-OwnerAuthor Commented:
I know you said that the location doesn't matter and I certainly trust you but I just changed this: conn.Open "Provider=SQLNCLI10;Server=" & Environ("computername") & "\SQLExpress;Database=xxx;Trusted_Connection=yes;"

to this: conn.Open "Provider=SQLNCLI11;Server=" & Environ("computername") & "\SQLExpress;Database=xxx;Trusted_Connection=yes;"

(Note: changed "Provider=SQLNCLI10" to "Provider=SQLNCL11") which matches the location of the data files on the new system and it works.
0
Anthony PerkinsCommented:
I know you said that the location doesn't matter and I certainly trust you
I would rather you think about what I am saying, than trust me.  Let's suppose one day you read up that it is very bad practice to save your data files on the same drive as the O/S, namely drive C:.  (Or that the Transaction Log files should not be on the same drive as the data files),  You therefore decide that the data files need to be moved to drive D:, and you do it over a weekend when there is not much, if any activity.  Why do you think that would require a connection change?

Here is another example, you are running out of space on C: drive and decide to create a new data file on D: drive and move some of the data to that data file.  Again why do you think that it is necessary to change your code, let alone your connection string?

This happens all the time, we change drive locations, add data files on different drives, place transaction log files on different drives.  None of these changes have any impact on the application.  Nor should they.

Having said that, if you feel that changing the connection string solved the problem, than more power to you, go with it.

The real cause of the problem I suspect is that Your SQL Server 2012 setup requires a newer provider, namely SQLNCL11 and has nothing to do with the location of any of the data files.

This site should help in future:
The Connection Strings Reference

And in particular:
SQL Server Native Client 11.0 OLE DB Provider connection strings
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob SchneiderCo-OwnerAuthor Commented:
I appreciate the guidance but I was not being sideways or patronizing when I said I trust you...I have had enough experience on this site to know that the help here is top shelf.  You are certainly no exception!  I was simply puzzled by your advice in light of the fact that what appeared to be a different location was what "caused it to work."  I will review your links and re-post what I find.  You may have just saved me as I am a little under the gun on this one.
0
Bob SchneiderCo-OwnerAuthor Commented:
Incredibly helpful...learned a lot.  thanks!
0
Anthony PerkinsCommented:
I appreciate the guidance but I was not being sideways or patronizing when I said I trust you
Trust me I did not take it that way at all.  :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.