Skip navigation links

MySQL Forums :: Stored Procedures :: MySQL and MSSQL interaction: Stored Procedures?


Advanced Search

MySQL and MSSQL interaction: Stored Procedures?
Posted by: Kobus Myburgh ()
Date: October 04, 2012 04:01AM

Hi all,

I am a newbie to stored procedures and need some advice, please.

A client of mine has a MySQL database with over 120 tables, and 30K products in the products table, with a PHP application managing the workflow of day-to-day operations of the company, using this database.

The client also has Pastel Evolution, using MSSQL as the database. All stock management is done via Pastel Evolution, but workflow is managed via the PHP application. Therefore, these two databases, as far as stock movement (branch transfers, sales, manufacturing, damaged goods, etc.) need to be in sync with each other.

Currently, there is a VERY ineffecient PHP script in place that checks all 30K products, and then verifies this with the data in Pastel, to ensure that we have the same amount of stock in both databases.

I believe there should be a way to utilize MySQL stored procedures and triggers to retrieve ONLY products where stock on hand is NOT the same between the databases, and update the MySQL, thereby cutting out the PHP script inbetween. This will then most likely make the script run closer to 10 minutes (or whatever) as opposed to the 9 hours it currently takes to complete.

The two servers (MySQL and MSSQL) are in the same data centre, and nothing prohibiting talk between the two, and no bandwidth issues.

I have no idea on where to start with this. Somehow, I need to compare the data in MySQL directly with the data in MSSQL, and update only records that have changed (about 100 records a day) as opposed to 30K records per day.

Any advice will be greatly appreciated,

Thanks!

Kobus

Options: ReplyQuote


Subject Views Written By Posted
MySQL and MSSQL interaction: Stored Procedures? 1140 Kobus Myburgh 10/04/2012 04:01AM
Re: MySQL and MSSQL interaction: Stored Procedures? 603 Peter Brawley 10/04/2012 08:27AM
Re: MySQL and MSSQL interaction: Stored Procedures? 485 Kobus Myburgh 10/04/2012 09:18AM
Re: MySQL and MSSQL interaction: Stored Procedures? 419 Peter Brawley 10/07/2012 10:05AM
Re: MySQL and MSSQL interaction: Stored Procedures? 473 Rick James 10/07/2012 06:27PM
Re: MySQL and MSSQL interaction: Stored Procedures? 400 Kobus Myburgh 10/08/2012 01:19AM
Re: MySQL and MSSQL interaction: Stored Procedures? 429 Kobus Myburgh 10/08/2012 01:19AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.