Solved

How to update MySQL web server with data from SQL Server 2000

Posted on 2013-07-01
4
577 Views
Last Modified: 2013-07-02
Hi all.

I have a website whose database is MySQL and I would like to update some fields in some of the tables from my Access application whose backend is SQL Server 2000.

Is this possible? To be able to update a MySQL table that is on a web server from my application in our office? How?

Thank you in advance.
0
Comment
Question by:Sim1980
  • 2
4 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39291949
I think a dependable way to do this would be to write a "web service" script.  You would install this script on your office application and expose the URL to the WWW.  Then your website with MySQL could call upon the URL to retrieve the information.  It's not rocket science, but it has a lot of moving parts, so you might consider hiring a professional developer to help you through this.
0
 
LVL 9

Accepted Solution

by:
edtechdba earned 500 total points
ID: 39292705
What about setting up a linked SQL server on your SQL Server 2000 database to the MySQL database and then perform updates that way? This may be an option if the update processing isn't too intensive.

Here's an example of how to set up a SQL linked server to a MySQL database:
http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/

Once your linked server is set up to your MySQL database, then you should be able to perform update commands using the linked server to update your MySQL tables.

Here's an example of an update command using OPENQUERY:
UPDATE OPENQUERY (LinkedServerName, 'SELECT name FROM student WHERE id = 1')
SET name = 'StudentNameTest';

I hope that helps!
0
 

Author Closing Comment

by:Sim1980
ID: 39293510
Thanks edtechdba.
0
 
LVL 9

Expert Comment

by:edtechdba
ID: 39293581
You're welcome, good luck!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 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

15 Experts available now in Live!

Get 1:1 Help Now