Solved

MS SQL database for AsP website

Posted on 2012-03-22
11
180 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now