Solved

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

Posted on 2013-07-01
4
588 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

626 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