Load Data from MySQL into MS SQL via Web Service 1

[tadd]

CHALLENGE:

I need to create some kind of process that grabs data from a MySQL database and puts it into a MS SQL 2005 database.

One database lives on a Linux server and the other lives on a Windows server. The only way the two databases can connect to each other is through the internet.

The linux server has the PHP library installed. The Windows server has .NET 2.0 library installed. I am a .NET programmer.

The process has to run once every night in the wee hours.

It occured to me that the best way to do this would be to create a web service on the Linux side using PHP that can publish data from the MySQL database out to the internet. If I could do that, then it would be easy for me to consume that webservice by writing some code on the Windows side.

Has anyone ever done this before? Or does anyone have any better ideas?

Thanks.

 

[SQLDenis]

so you are saying

A) port 80 is open on the SQL Box
B) the SQL Box is accessible from the Wild West Web?


Very, very bad

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jbenson001]

Is it possible to created a linked server between the 2 servers?

 

[SQLDenis]

>>Is it possible to created a linked server between the 2 servers?

yes, you need to install the MySQL driver and you can set up a linked server or use OPENROWSET

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[tadd]

If this approach of a linked server would be more secure then that's what I should do. It sounds like it might be easier too? Have you done this before?

 

[tadd]

I should add that, I am not an expert on Web Services or Security, but surely there must be some way to secure data published by a Web Service?

I wouldn't be opening anything on the MS SQL side, only on the MySQL side - the source of the data.

 

[SQLDenis]

>>Have you done this before?

yes, I answered a similar question in 2005

see here:

http://groups.google.com/group/micr...ql+denis+mysql+linked+server#420f9e0b7c5d72a0

on the SQL server box
Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Use Microsoft OLE DB Provider for ODBC Drivers


Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3


And in Provider Options select:


level zero only
Non-transacted updates (something)
Allow InProcess


the linked thing will work as


mylinkedserver...tablename




Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[tadd]

OK thanks. I'll give this a try when I get home...

 

[tadd]

After a lot of hacking and with the help of some other detailed documents I found, I was able to link to the remote MySQL database from my instance of SQL 2005 on my local machine at home. (Were your instructions for SQL 2000 EM?)

Thanks for pointing me in the right direction. I have made some progress, and this is a cool technique.

However, (isn't there always a "however?"), my problem is trickier than I suppose I led on at first. The MS SQL server from which I need to link to the MySQL database is hosted on GoDaddy shared hosting and they do not give me the ability to configure Linked Servers.

So now I am trying to figure out how I might create some automated process to update the data in the MS SQL server hosted on GoDaddy. This is only a temporary thing - the process will only run for a few weeks - so it doesn't have to be beautiful.

Have any ideas for how I might do this? At the moment I am thinking I will run an automated SQL Job from my local machine to grab the data from the remote MySQL server and create CSV files (or whatever) which would then be uploaded to the remote host on GoDaddy where the MS SQL database lives. The remote host at GoDaddy could have have an EXE file (that I will write) which would be running in the background and listening for the CSV file. Once a CSV file is detected, it could import the CSV into the MS SQL database.

Got any better ideas?

I guess I should also check to see if it is possible to write SQL Jobs on the MS SQL database hosted on GoDaddy. That would be easier than creating an EXE file.

Thanks.

 

[SQLDenis]

can you do OPENROWSET?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[tadd]

Good news / bad news:

I can execute the following statement in a query window (SQL 2005) on my computer AT HOME, and it works!

SELECT * FROM OPENROWSET
(
'MSDASQL',
'Driver={MySQL ODBC 5.1 Driver};
DB=MySqlDB;
SERVER=MyServer.com;
uid=MyUserName;
pwd=MyPassword',
'SELECT * FROM userAccounts'
)

HOWEVER, when I try to execute the same statement on my MS SQL database hosted on GoDaddy's shared hosting, I get the following error:

"The OLE DB provider "MSDASQL" has not been registered."

I guess I don't blame them.

So...I guess I am either going to have to move my new website off of GoDaddy and on to a host where I can intall MSDASQL...OR...I'll have to build a two-step process like what I described earlier where I first download some data from the MySQL database to my local machine and then upload it to the MS SQL database on the GoDaddy host.

Unless there are any better ideas out there...