I was wondering if there is a way to link an MySQL server inside a SQLServer so the tables look like local for the sqlserver , thus making the SP's easy and clean to see.
Both servers are in the same machine and have the TCP/IP access enabled (albeit restricted)

SQLServer should only do read and presentation tasks , no writting or updating of the MySQL server tables is allowed , only reading. A PHP frontend takes care of the actualization of the values.

Of course , the SPs could be done in the MySQL , but feel more at ease at the SQLServer and i would like to integrate the data being poured in the MySQL server with data coming from other sources into the sqlserver.

Any ideas (even involving ODBC ) ?

If i find the solution on my own , i'll share ;)

EDIT : i have linked the server and i can go about with a openquery. What i would like would be to have all the tables in the target database as non-updatable views.
This is my setup

Download ODBC 32  driver
http://downloads.mysql.com/archives/mysql-connector-odbc-5.1/mysql-connector-odbc-5.1.4-win32.msi

unrar and install

Setup SYSTEM odbc connection named "my"

link the server

sp_addlinkedserver 
    @server=  'mylinkedserver' ,
@srvproduct= '',
    @provider = 'MSDASQL',
    @provstr=  'DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;USER=externcall;PASSWORD=mypa$$;OPTION=3;' ,
    @datasrc = 'My',
    @catalog=  'querieddatabase'
GO

SELECT *
FROM OPENQUERY(my, 'SELECT username FROM usertable')
GO

Now , what i would like would be to make some views automatically that reflect the makeup of the target database but in terms SQLServer would be confortable with , to avoid problems like this
-----
SELECT *
FROM OPENQUERY(my, 'SELECT * FROM usertable')
GO

El proveedor OLE DB 'MSDASQL' para el servidor vinculado 'my' devolvió datos que no coinciden con la longitud de datos esperada para la columna '[MSDASQL].is_admin'. La longitud de datos (máxima) esperada es 10 y la de los datos devueltos es 8.

What is to say , a varchar(10) returns a char(8) when in fact it should be a bit field (easy to manage using a case select )

Did someone ever tried to do some work like this , interfacing mysql and sqlserver so it looks the same to the programmers?

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall

If you successfully link to a MySQL Server, and your ODBC is properly written, you can get to all database objects via a four-part reference. You don't need the Openxxx functions. You can then create any views or functions you like. Having said that, I've never linked to MySQL, but it all works nicely with SQLite.

Sounds good to me , i'll give it a try this evening. Would love to see an example with sqlite , since i have some plans to test ideas with sqlite , albeit i was thinking of it as an user (client) side tool.

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall