Solved

MS SQL database for AsP website

Posted on 2012-03-22
11
178 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
The viewer will learn how to dynamically set the form action using jQuery.
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…

744 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

12 Experts available now in Live!

Get 1:1 Help Now