0

I need to set up a SQL Server trigger to connect to a remote mysql linux server and insert data into a table. Is there a way to do this?

I'm leaning towards triggers rather than scheduled tasks because the trigger will execute each time data is entered/updated in the SQL Server table and only 1 record will be passed to the MySQL table at a time, cutting down on large data transfers and creating a more 'realtime' transfer appearance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sadmicrowave
  • 39,964
  • 34
  • 108
  • 180

1 Answers1

1

You don't need a trigger for this.

Create a linked server to the remote server, create a stored procedure to insert the data into the linked server and then schedule a task under SQL Agent to run the stored procedure.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Can you provide some tutorial or link explaining how to create a stored procedure please? Thanks! Also, will this work when connecting to a Linux MySQL server? – sadmicrowave May 19 '11 at 02:03
  • Also, why is using a scheduled task better than a trigger? A scheduled task will dump to mysql tables multiple records at a time while triggers will be executed for 1 record at a time keeping data transfer to a minimum and populating the mysql table in seemingly realtime...which seems more ideal – sadmicrowave May 19 '11 at 02:19