0

A fun question for a Friday afternoon, no?

As the question states, is it possible to write a SQL Server trigger that inserts data into a MySQL table?

EDIT: Yes, I know this is basically a dumb idea and that the application should be doing it and so on. But this is a legacy Classic ASP application which we're transitioning to MySQL; and until we get rid of the ASP bit we really would like to be able to squirt data over to the MySQL system without touching the ASP code.

For there be dragons. Just mentioning it tends to make it crash.

Thanks all!

Improve this question
6
  • Yes, but why would you give that much power to your SQL server and (potentially) take that much time in a trigger? There're timeouts to worry about, authentication, etc. I think it would be better to put an insert/delete request in another module, then let it handle touching each of the databases it needs to. Commented Dec 4, 2009 at 20:30
  • Errr.strike that "yes." I assume it's possible but I can't find any reference to it. I'm still sticking with my original thought that seperating the inserts into a separate module is the better way to go. Commented Dec 4, 2009 at 20:37
  • @Michael: I don't disagree with you - talking about triggers instinctively makes me seriously question the data model. Commented Dec 4, 2009 at 20:46
  • @Michael Todd - Because architecturally this is (believe it or not) the easiest point to grab data in a live system. It's ugly, I know, I know... Commented Dec 4, 2009 at 21:03
  • 1
    Ah (after update), now I understand; you have to do it this way. Commented Dec 4, 2009 at 21:10

2 Answers 2

Reset to default
2

In order to write data to a different database from SQL Server, you'd have to first create a linked server instance on SQL Server pointing to the other db (MySQL in this example) with appropriate credentials/permissions.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

OK, so how would I go about this? (I'm a developer, not a database expert, so excuse me!)
SQL Server - Linking Servers: msdn.microsoft.com/en-us/library/ms188279.aspx
@RickNZ Thanks! That one looks perfect - we'll give it a whirl.
1

One way to do this would be to use SQL CLR. You could have a CLR stored procedure or function that accesses the MySql DB.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.