Solved

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

Posted on 2013-07-01
4
583 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 110

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
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.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

679 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