Solved

MS SQL database for AsP website

Posted on 2012-03-22
11
185 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

630 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