Automatical Import from MySQL-Table?

Hi All!
is it possible to create a job in the SQL Enterprise Manager that regularly
imports the data from a table of a MySQL-database?
how could I achieve this aim without having to program a lot?
any hints are highly appreciated!
cheers, jensTake a look at OPENROWSET if you don't want to create a linked server
or OPENQUERY if you do create a linked server
http://sqlservercode.blogspot.com/|||Hi SQL,
for me a so called "linked server" sounds better, how could I create a
linked server in the MS SQL ENterprise Manager?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134485484.301414.309720@.g43g2000cwa.googlegroups.com...
> Take a look at OPENROWSET if you don't want to create a linked server
> or OPENQUERY if you do create a linked server
> http://sqlservercode.blogspot.com/
>|||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
http://sqlservercode.blogspot.com/|||Hey SQL, that worked perfectly, thanx a lot!
now I see the tables of "DatabaseXYZ" the "MyLinkedMySQLServer". But how can
I now create a job that imports all data from "Table1" of "DatabaseXYZ"
into "DatabaseABC" of my MSSQLServer?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134491324.820321.274410@.z14g2000cwz.googlegroups.com...
> 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
>
> http://sqlservercode.blogspot.com/
>|||Create a job and copy this code (obviously you will have to change it,
for example i don't know what your join condition is)
Then create a schedule and that should do it
insert into DatabaseABC.dbo.table1
select * from DatabaseABC.dbo.table1 ms left join
MyLinkedMySQLServer.DatabaseXYZ.Owner.Table1 my
on ms.id =my.id
where my.id is null
To Create a job go to Managment-->Sql server Agent-->Jobs
Right click --> New Job add a step paste the code
add the schedule and that should do it
http://sqlservercode.blogspot.com/