Solved

MS SQL database for AsP website

Posted on 2012-03-22
11
179 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
The viewer will learn how to count occurrences of each item in an array.

920 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

17 Experts available now in Live!

Get 1:1 Help Now