Solved

MS SQL database for AsP website

Posted on 2012-03-22
11
182 Views
Last Modified: 2013-02-11
I finally got MS Sql server Express running on my Windows 7 Pro 64bit machine after a bit of cajolling.

I'm new to this so bear with me. I have built a website before using MS Access, the Access database gets uploaded to the webserver to allow connections, how do i set an sql database up so it can be accessed by my web hosting server?

This is the first step of many i am sure so i want to get the theory right. obviously i don't want to upload the whole SQL server instance, just the database files, i guess, does this mean i install the new instance in the sql server folder on my C: drive and the data file in inetpub (to test)?

I'm using Dreamweaver to create the site, test it locally then upload to my web hosting service when it all works.

This is probably a very basic question as i can't find anything about it when i search on google, all comments seem to be about setting up the SQL server and instances, they then jump to connection strings nobody talks about how the 2 meet.
0
Comment
Question by:GegH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37751300
0
 

Author Comment

by:GegH
ID: 37751404
Thankyou for the reply.

This is what i don't quite understand, i have set up the sql server (locally), the connection strings are similar to those i used when building an intranet for a company i used to work for which used an access database, this was fine. However, if i connect to a database which is kept locally on my c: drive, when the pages are syncronised with the webserver somewhere else in the world they won't connect to anything.

What part of the SQL server or Database is synchronised? and where should it be set up?
0
 
LVL 14

Expert Comment

by:quizwedge
ID: 37751413
Unlike Access databases, your hosting environment needs to be running SQL server. You can't just upload the SQL files and access them from your code.

There are a few different ways to get the data to the SQL server with your host, but it's probably best to get the directions from them. Some may allow direct access. For others you may be able to upload the SQL server files. Worst case scenario is you'd have to write some code to manually insert all of the records that are on your server.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:GegH
ID: 37751624
Thanks.
So i set it all up locally and get it working. I have access to my web hosting service which has a database container available to me which runs MS SQL (i'll get hold of the guys who run it to tell me how to get the data there). It's partly because they support MS SQL not MS Access, that i'm going down this route.

I'm using SQL Server Management Tools as suggested in an earlier post, to connect to the sql server i connect using PC Name\Installation Name. If i set a connection string like this on my web page how does it open the connection from the web hosting server?
I realise this is probably very basic stuff but after using MS Access for so long i'm struggling with the concept.

Along the same lines, what is the best way to transfer MS Access to MS SQL?
0
 
LVL 14

Expert Comment

by:quizwedge
ID: 37751651
The sql server connection will probably use an IP address with a username and password. See the Standard Security example at http://www.connectionstrings.com/sql-server-2005 

Depending on what the hosting company offers, you may be able to use the same IP address to access the database using SQL Server Management Tools as well.

To get the data from MS Access to MS SQL, you'll want to use the Upsizing Wizard. See http://office.microsoft.com/en-us/access-help/use-the-upsizing-wizard-HP005273009.aspx

Oh and don't worry about asking questions that are too basic. Everyone starts somewhere and you're here to learn. :)
0
 

Author Comment

by:GegH
ID: 37751742
So, i think i've got this.

My local site is set up as 'localhost\da da da' (or 127.0.0.1\da da da) this essentially connects to  127.0.0.1\SQL Server Name.

When everything is uploaded LocalHost ipaddress is changed to web ipaddress and the SQL Server name on the web server is registered as the SQL data source to use whenever it wants to connect to 127.0.0.1.

Is that the general gist of it?
0
 
LVL 14

Expert Comment

by:quizwedge
ID: 37755073
Basically, but I don't even use the computer name in my connection string on my web server. Below is a connection string I use (confidential information has been changed.) It's probably helpful to have both connection strings in your code and just comment out the one you're not using. That way you can easily switch between your testing and production environments.

Persist Security Info=False;Data Source=db.example.com;Initial Catalog=mysqldb;User ID=myID;Password=mypassword;Connect Timeout=200;

Open in new window

0
 

Author Comment

by:GegH
ID: 37764737
I've set it up as a DSN Connection rather than write a connection string for the moment to make sure i can access the database etc...

I think i may have gone wrong somewhere as when i try to read the database from mty test site i get this message

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'tbl_Plants', database 'master', schema 'dbo'.

/PlantManager/PM_Main.asp, line 13

This is obviously a permission error but i'm not sure where to change permission or what to change it to?
0
 
LVL 14

Accepted Solution

by:
quizwedge earned 500 total points
ID: 37773266
Sorry for the delay. Sounds like the permissions you need to change are on the server. Are you able to connect to the server with SQL Server Management Tools? If so, you can go to your database -> security -> users. Right click on your user name and choose properties. Make sure you have db_datareader and do not have db_denydatareader.
0
 

Author Comment

by:GegH
ID: 37776182
I added a permission to the database for IUSR which seemed to work but will this work when it gets to the big bad world?
0
 
LVL 14

Expert Comment

by:quizwedge
ID: 37776220
Iusr may work in the real world but I'd advise against it. You're basically giving the internet permission to your database. Much better to create a username and password and add those to the DSN (or connection string if you switch to that method.)
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

752 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