[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql Server 2012 Express Install Issue

Posted on 2014-08-04
10
Medium Priority
?
237 Views
Last Modified: 2014-08-05
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!
0
Comment
Question by:Bob Schneider
  • 5
  • 3
  • 2
10 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1336 total points
ID: 40240300
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
 
LVL 11

Assisted Solution

by:HuaMinChen
HuaMinChen earned 664 total points
ID: 40240323
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
 

Author Comment

by:Bob Schneider
ID: 40241353
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40241514
What did you change currently?
0
 

Author Comment

by:Bob Schneider
ID: 40241540
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
 

Author Comment

by:Bob Schneider
ID: 40241589
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1336 total points
ID: 40241958
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
 

Author Comment

by:Bob Schneider
ID: 40242004
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
 

Author Closing Comment

by:Bob Schneider
ID: 40242019
Incredibly helpful...learned a lot.  thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40242723
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question