RE MySQL -> Linked Server in MS SQL Server

Hi,

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=gcdmo-myodbc@m.gmane.org

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:MikeH@kiwiplan.co.nz]=20
> Inviato: gioved=EC 26 febbraio 2004 20.20
> A: myodbc@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=3DdiPierre@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=3Dgcdmo-myodbc@m.gmane.o rg

RE: RE MySQL -> Linked Server in MS SQL Server

Hi Pier,

Are you wanting to use SQL Server or Access as your database...it makes =
a
difference

cheers

mike

-----Original Message-----
From: Pierfrancesco Consolo [mailto:diPierre@www.economia.unipd.it]
Sent: Tuesday, 2 March 2004 0:01
To: myodbc@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:MikeH@kiwiplan.co.nz]=20
> Inviato: gioved=EC 26 febbraio 2004 20.20
> A: myodbc@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=3DdiPierre@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=3Dgcdmo-myodbc@m.gmane.o rg

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:MikeH@kiwiplan.co.nz]=20
> Inviato: marted=EC 2 marzo 2004 1.52
> A: diPierre@www.economia.unipd.it; myodbc@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:diPierre@www.economia.unipd.it]
> Sent: Tuesday, 2 March 2004 0:01
> To: myodbc@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:MikeH@kiwiplan.co.nz]
> > Inviato: gioved=EC 26 febbraio 2004 20.20
> > A: myodbc@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=3DdiPierre@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=3Dgcdmo-myodbc@m.gmane.o rg

RE: RE MySQL -> Linked Server in MS SQL Server

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...

Good luck


Mike

-----Original Message-----
From: Pierfrancesco Consolo [mailto:diPierre@www.economia.unipd.it]
Sent: Tuesday, 2 March 2004 22:39
To: myodbc@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:MikeH@kiwiplan.co.nz]=20
> Inviato: marted=EC 2 marzo 2004 1.52
> A: diPierre@www.economia.unipd.it; myodbc@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:diPierre@www.economia.unipd.it]
> Sent: Tuesday, 2 March 2004 0:01
> To: myodbc@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:MikeH@kiwiplan.co.nz]
> > Inviato: gioved=EC 26 febbraio 2004 20.20
> > A: myodbc@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=3DdiPierre@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=3Dgcdmo-myodbc@m.gmane.o rg