Yesterday I posted a question regarding setting up a MySQL db as a linked server in MS SQL Server, after much messing around I managed it using the following :
The key was to instal the ODBC driver on the box that SQL Server was sitting on. Once I did that I defined a System DSN - (Make sure that the user you use has access permissions to the MysSQL db) once the DSN tested Ok. I went into Add Linked Serevrs on the SQL Server box (not over the network, you need to either use the console or something like VNC) defined the Linked Server using the OLE DB ODBC connector and the DSN that I had just defined - here I found you need to set on the Security tab, under "For a login not defined in the list above" the option "Be made using this security context" and enter the user defined as the UID for the DSN - and password of course.
After that the tables appeared and using query analyser I could get at the data using the openquery function.. one last trick, I found I had to use the "Trim()" function on all character columns in my queries, otherwise I got errors about incorrect lengths.. something along the lines of
select a.* from openquery(MySQLLinkedServer, 'select trim(company_name), trim(costreet), creditlimit from company') as a
This also works fine to create views, stored procs etc
Hope this helps if anyone is stuck on this
-- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc To unsubscribe: http://lists.mysql.com/myodbc?unsub=myo...@freebsd.csie.nctu.edu.tw
-----Original Message----- From: Pierfrancesco Consolo [mailto:diPie...@www.economia.unipd.it] Sent: Tuesday, 2 March 2004 0:01 To: myo...@lists.mysql.com
Cc: 'Mike Harknett' Subject: R: RE MySQL -> Linked Server in MS SQL Server
Ok! This works, but what about modifying fields in the MySQL Linked Server ? I have this problem from long ago.=20 I'm waiting for a new ODBC driver with at least the ability to send two distinct command as a workaround for the need for the openquery call to have something in return.=20
Eg:=20 select a.* from openquery(MySQLLinkedServer, 'INSERT INTO company (company_name, costreet, creditlimit) VALUES ("A", 10.10 , 10.10); select trim(company_name), trim(costreet), creditlimit from company')
Or there is something I don't understand (or some trick with MySQL stored procedures) ?
Bye
Pier
P.S.: Sorry for my english
> -----Messaggio originale----- > Da: Mike Harknett [mailto:Mi...@kiwiplan.co.nz]=20 > Inviato: gioved=EC 26 febbraio 2004 20.20 > A: myo...@lists.mysql.com > Oggetto: RE MySQL -> Linked Server in MS SQL Server >=20 >=20 > Hi, >=20 > Yesterday I posted a question regarding setting up a MySQL db=20 > as a linked server in MS SQL Server, after much messing=20 > around I managed it using the following : >=20 > The key was to instal the ODBC driver on the box that SQL=20 > Server was sitting on. Once I did that I defined a System DSN=20 > - (Make sure that the user you use has access permissions to=20 > the MysSQL db) once the DSN tested Ok. I went into Add Linked=20 > Serevrs on the SQL Server box (not over the network, you need=20 > to either use the console or something like VNC) defined the=20 > Linked Server using the OLE DB ODBC connector and the DSN=20 > that I had just defined - here I found you need to set on the=20 > Security tab, under "For a login not defined in the list=20 > above" the option "Be made using this security context" and=20 > enter the user defined as the UID for the DSN - and password=20 > of course. >=20 > After that the tables appeared and using query analyser I=20 > could get at the data using the openquery function.. one last=20 > trick, I found I had to use the "Trim()" function on all=20 > character columns in my queries, otherwise I got errors about=20 > incorrect lengths.. something along the lines of >=20 > select a.* from openquery(MySQLLinkedServer, 'select=20 > trim(company_name), trim(costreet), creditlimit from company') as a >=20 > This also works fine to create views, stored procs etc >=20 > Hope this helps if anyone is stuck on this >=20 > --=20 > MySQL ODBC Mailing List > For list archives: http://lists.mysql.com/myodbc > To unsubscribe: =20 > http://lists.mysql.com/myodbc?> = unsub=3DdiPie...@www.economia.unipd.it >=20 >=20
-- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dmyo...@freebsd.csie.nctu.edu.tw
I've used DTS as a scheduled job to do something similar. You also = should be able to it using openquery in triggers. What I've done is set up views = in SQL Server using openquery as below
create view dbo.MySQLTableName as select MySQLTableName.* from openquery(MySQLLinkedServer, 'select * from MySQLTableName) as MySQLTableName
You can then set triggers on this using normal SQL statements and the openquery function is hidden from view
Again is data only going one way SQL Server -> MySQL or MySQL -> SQL = Server or is it going both ways?
If it's going both ways and you are using triggers watch out for = circular references where you update a table, it fires a trigger which updates a table in the other database which then fires a trigger which tries to = update the first table etc etc...
-----Original Message----- From: Pierfrancesco Consolo [mailto:diPie...@www.economia.unipd.it] Sent: Tuesday, 2 March 2004 22:39 To: myo...@lists.mysql.com
Cc: 'Mike Harknett' Subject: R: RE MySQL -> Linked Server in MS SQL Server
I have to work with two real power database as MS SQL Server and MySQL Server on two distinct server machine (Windows 2000 and True 64 Unix).
No problem using Access to (near) 'manually' modify both database tables, but I think that the daily data migration I need should be done on one of the two server in a more automatic way (eg: with DTS & MS SQL batch jobs or, better, with field triggers, or, on the other side, perhaps by Unix cron scripts).
Bye.
Pier
> -----Messaggio originale----- > Da: Mike Harknett [mailto:Mi...@kiwiplan.co.nz]=20 > Inviato: marted=EC 2 marzo 2004 1.52 > A: diPie...@www.economia.unipd.it; myo...@lists.mysql.com > Oggetto: RE: RE MySQL -> Linked Server in MS SQL Server >=20 >=20 > Hi Pier, >=20 > Are you wanting to use SQL Server or Access as your=20 > database...it makes a difference >=20 > cheers >=20 > mike >=20 > -----Original Message----- > From: Pierfrancesco Consolo [mailto:diPie...@www.economia.unipd.it] > Sent: Tuesday, 2 March 2004 0:01 > To: myo...@lists.mysql.com > Cc: 'Mike Harknett' > Subject: R: RE MySQL -> Linked Server in MS SQL Server >=20 >=20 > Ok! This works, but what about modifying fields in the MySQL=20 > Linked Server ? I have this problem from long ago.=20 > I'm waiting for a new ODBC driver with at least the ability=20 > to send two distinct command as a workaround for the need for=20 > the openquery call to have something in return.=20 >=20 > Eg:=20 > select a.* from openquery(MySQLLinkedServer, 'INSERT INTO=20 > company (company_name, costreet, creditlimit) VALUES ("A",=20 > 10.10 , 10.10); select trim(company_name), trim(costreet),=20 > creditlimit from company') >=20 > Or there is something I don't understand (or some trick with=20 > MySQL stored procedures) ? >=20 > Bye >=20 >=20 > Pier >=20 > P.S.: Sorry for my english >=20 >=20 > > -----Messaggio originale----- > > Da: Mike Harknett [mailto:Mi...@kiwiplan.co.nz] > > Inviato: gioved=EC 26 febbraio 2004 20.20 > > A: myo...@lists.mysql.com > > Oggetto: RE MySQL -> Linked Server in MS SQL Server > >=20 > >=20 > > Hi, > >=20 > > Yesterday I posted a question regarding setting up a MySQL db > > as a linked server in MS SQL Server, after much messing=20 > > around I managed it using the following : > >=20 > > The key was to instal the ODBC driver on the box that SQL > > Server was sitting on. Once I did that I defined a System DSN=20 > > - (Make sure that the user you use has access permissions to=20 > > the MysSQL db) once the DSN tested Ok. I went into Add Linked=20 > > Serevrs on the SQL Server box (not over the network, you need=20 > > to either use the console or something like VNC) defined the=20 > > Linked Server using the OLE DB ODBC connector and the DSN=20 > > that I had just defined - here I found you need to set on the=20 > > Security tab, under "For a login not defined in the list=20 > > above" the option "Be made using this security context" and=20 > > enter the user defined as the UID for the DSN - and password=20 > > of course. > >=20 > > After that the tables appeared and using query analyser I > > could get at the data using the openquery function.. one last=20 > > trick, I found I had to use the "Trim()" function on all=20 > > character columns in my queries, otherwise I got errors about=20 > > incorrect lengths.. something along the lines of > >=20 > > select a.* from openquery(MySQLLinkedServer, 'select > > trim(company_name), trim(costreet), creditlimit from company') as a > >=20 > > This also works fine to create views, stored procs etc > >=20 > > Hope this helps if anyone is stuck on this > >=20 > > -- > > MySQL ODBC Mailing List > > For list archives: http://lists.mysql.com/myodbc > > To unsubscribe: =20 > > http://lists.mysql.com/myodbc?> = unsub=3DdiPie...@www.economia.unipd.it > >=20 > >=20 >=20
-- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dmyo...@freebsd.csie.nctu.edu.tw
|
