Web Site & MS SQL

I would create a web service that returns the data.

Can you be more specific?

What kind of hosting is your third party provider?  Microsoft's current PHP SQL driver only runs on Windows, not Linux.

My Web hosting company uses unix and our company is Windows Server 2008 based.

There are a couple of ODBC drivers for Linux that will connect to SQL Server 2008 but they're not free.  'php_mssql' and 'FreeTDS' won't work with SQL Server 2008.  While you can develop software on your WAMP platform that will work on Windows and connect to SQL Server 2008, you will not be able to transfer it to your Unix hosting and make it work.  The drivers are not available for Linux.  This has come up several times this year and no one has been able to make it work (for free).

Is there a way to connect from Network Solutions's unix server that is hosting my site back to my Sql Server to retrieve my data to display data for our customers?  The only other thing i can think of is to export the needed data into a MySQL database on the web server.

Does your SQL Server 2008 have a public IP address?  Are you running IIS on it?  How often does the data get updated?

A RESTful interface is the way that web services are provided between servers.  TheHiTechCoach gave you good advice and I provided an example of how it is done.

Best of luck with it, ~Ray

I can forward sql traffic to it via the router's port 1433 forwarding and yes IIS is running on it.

First of all If you wish to give access of some objects to that user then create a SQL user for that particular database and give read write or some permission.

You can also give SQL object level permission using Grant and Revoke functions.

I'm not familar with the RESTful interface.  What has to been done on the web site server and the corporate server?

Thanks

You have to create two pages or programs, one on each server.  The one on the server where the SQL Server is accepts requests from outside, does a query on the database, and passes the results back as a response to the original request.  As shown in Ray's code,  you can use something like

file_get_contents('http://laprbass.com/RAY_REST_get_last_name.php?key=ABC&resp=XML&name=Ray');

on the outside web server to make a request to the corporate server.  The query string at the end has the data that you want from the database.

Does the SQL Server have to be running PHP and IIS?  DO you know where I can get some examples of a complete SQL data sample?

Thanks

You said that SQL Server 2008 was running on Windows Server 2008 with IIS.  You can use any language you want on that server to return data.  You can install PHP if that makes it easier for you.  Just make sure that the version you install is compatible with the server.  If you install PHP, then you must install the Microsoft PHP SQL driver and the SQL Native client so that PHP can talk to the SQL Server.

Is their anything I should know about getting SQL data from the company's SQL server into the PHP pages at our hosting company?

We've told you basically what you need to do.  What is it that you don't understand?  Maybe if it is too complicated, you should hire a developer to do this project.

Sorry to bother you. I'll figure it out myself.

It's not a bother.  Ray and I and TheHiTechCoach and others spend a lot of time trying to help people here.  So far, I can't tell if anything we have said makes any sense to you.  You have to give us something to work with so we'll be able to figure out what comes next.

If you have never programmed or done PHP pages, then there is a lot that you will need to learn.  And programming is lots and lots of details that have to be correct for programs to work.  I can't tell you in a single post here what you have to do.

I have 30+ years as a programmer and have coded in over 40 languages but I'm new to PHP.  I just wanted a leg up to save some time.

Ray made a post a couple of days ago about PHP saying it's a lot like a 'real' programming language (somewhat 'C' like)... but made to be 'easy' with loose typing and the odd kinds of things you find in javascript.  I find myself mutiplying variables by 1 sometimes to make sure PHP thinks its a number.  The biggest advantage of PHP is their web site which has all their docs online.  http://www.php.net/  I consider it the best reference site of it's type on the web.

The problem you will confront is that Microsoft doesn't care if you are able to connect to MS SQL Server from Linux.  UnixODBC and FreeTDS worked up to SQL 2000 but nothing has been added to them after that.  But Microsoft has changed their interfaces with SQL 2005 and 2008 so the only drivers I know about are some ODBC drivers for Linux that you can buy or use the Microsoft drivers on Windows.

If you have used a language to create web pages, the RESTful interface is slightly different.  Instead of returning a full page, you just return the data to the page that is asking for it.  Hope that makes some sense.

How about importing the necessary tables into mySQL on the hosting server from MS SQL on the company server?  There are only a few thousand rows and we only add about 50 new rows per day.  Then I could do all my querries against the mySQL database on the web site.

If you can do that once a day at a slow time, that would probably be better.  Just less trafffic overall.  I've never exported for SQL Server so I'd have to look that up.  Network Solutions has phpmyAdmin available to manage your MySQL databases so that would be easy for the initial export/import.

You would still need a pair of programs, one on each server, to do the daily updates.  It be easier to schedule the company server and have it contact a secure page on Network Solutions to do it.  That would eliminate any need to make changes to the firewall or anything else.

Sounds like a winner.

Is there a way to have my website do updates to it's MySQL database on some kind of timer using PHP or a SQL script?

Network Solutions does provide for Scheduled Tasks / Cron Jobs.  The problem with doing it on their server is that you won't have access to your SQL Server unless it has a public IP address where it can be accessed directly from the internet.  Your web site does allow that kind of access so you can write program on the SQL Server that can contact it.

I would create a program on the SQL server that can be launched by Windows Scheduled Tasks every night.  It would query your database for the updates and collect them.  Then it would do a page request to a PHP page on the web hosting and send the updates as a POST so that the PHP program could do the INSERTs into the MySQL database on the hosting.  I make sure there is a ridiculously long access code when I do something like that so it would be difficult to type it in.  Something like "access=1acode2thebase4data3could5for9zee73quirt8jj".  Or longer.

I'm not sure how to access a web page programmatically from within a Transact SQL script.

Thanks

I meant that you could use VB or C# to create a program that will get the data from the database and then make a POST to the web page.

Okay I'll try it.  Thanks