Solved

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

Posted on 2013-07-01
4
580 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 109

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP Form Calculate Total Price 10 40
TSQL query to generate xml 4 31
SQL SERVER 2008 R2 Could not obtain information about Windows NT group/user 5 34
SQL view 2 26
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…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

785 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