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/

Automatic Purge of Transaction Log Data?

Hello,

I have a database that I am setting up in SQL Server 2005. Initially, I am doing very large imports of data. Every time I run an import, I am having the increase the size of my transaction logs, and now they are approaching 2 GB. Should these be purging themselves? I have to keep increasing the max size of the log so that I can get my data in. While this will work for now, it is not a long term solution, because I can see the log size growing quite large and the amount of space on the server obviously isn't infinite. Is there a setting that I can change so they will automatically purge? If not, how do I purge this information myself?

Thanks so much!

Christine

The management of the transaction log depends on the recovery model used in your database. There is a good overview on http://msdn2.microsoft.com/en-us/library/ms189275.aspx about the available recovery models.

With a full recovery model, you will need to backup your transaction log to reclaim its space. With a simple recovery model, the system will do this for you, but there is a greater risk for data loss - be sure to make the right tradeoff for your database!

For the problem of large imports filling up the log space, you might also want to look into switching to bulk-logged recovery for the duration of the imports, although you might not need to do this if the one of the other models works for you after the log is under control (reclaimed by the system or by regular backups).

Another SQL BOL reference that will probably come in handy is Truncating the Transaction Log at http://msdn2.microsoft.com/en-us/library/ms189085.aspx, which explains a bit more about how log truncation works.

|||

Here are some good resources that will help you better understand and control the Transaction Log:

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

And especially, this article about why cautions about reducing the Log file size is worth reading:

FileSize -DB Shrink Issues
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

|||Thank you very much Marcelo and Arnie. I appreciate all of the information you have provided. I will spend this afternoon and tomorrow morning looking into it! :0)