Update using Migration

[demodav]

What I have is a MS SQL server 2000 and I am migrating to a MySQL server daily by using dts packages. One thing I noticed is that this works great for record ids that are unique.

But sometimes my data in my MS SQL server gets updated. I also need to update my mysql server. How do I do this using migration?

[demodav]

How would I go about updating my MySQL Database everytime something changes in the SQL?

[rmiao]

May try with trigger.

[demodav]

I've tried a trigger before see post http://forums.databasejournal.com/sh...=1#post123983.

[demodav]

Can it be done using a DTS package component, if so which?

[rmiao]

DTS will not give you real time update.

[demodav]

Yeah I sort of found that out. Do you think that you can help me setting up the trigger again. Ok here is what I have and can do. So far.

I have a MS SQL Server 2000 that is linked to a MySQL server.

In my SQL Query Analyzer, I am successfully able to update data in the MySQL server.

However, when I tried setting up a trigger by going to the SQL Enterprise Manager right clicking on the table in the database and choosing ALL Tasks manage Triggers. I typed

CREATE TRIGGER test_insert ON [dbo].[test]
FOR INSERT
AS
SET XACT_ABORT ON
INSERT INTO OPENQUERY(MYSQL, 'SELECT * FROM test')
SELECT a, b, c FROM INSERTED

I replaced the SET XACT_ABORT ON for SET ARITHABORT ON.

SQL Server Enterprise Manager Error reads:


[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: [MySQL][ODBC 3.51 Driver] Optional feature not supported]
[Microsoft][ODBC SQL Server Driver][SQL Server] The operation could not be performed because the OLE DB provider ‘MSDASQL’ was unable to begin a distributed transaction.

[rmiao]

Tried create trigger in query analyzer instead?

[demodav]

I didn't know one could do that.

Ok, I did that, all it did was place the trigger in the manage trigger place in my table, but I still get the same error.

[rmiao]

Sounds your MySql provider doesn't support distributed transaction, check with MySql to find out if they have new one.

[demodav]

I do not have to have it real time I just need it to work.

What? I guess I am looking for is two queries maybe from doing research.

They should be

First step: update tlgi_survey where record_id exists
Second step: insert tlgi_survey where record_id does not exists

I am sort of syfering from

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116433

here are the queries that I have tried, but I am new and am not good at writing them

UPDATE OPENQUERY (MySQL, 'SELECT status FROM tlgi_survey WHERE record_id = [dbo].[tlgi_survey].record_id')
SET status = [dbo].[tlgi_survey].status;


update tlgi_survey where exists ('select status from tlgi_survey where where record_id = dbo.tlgi_survey.record_id')


Here's my info:

db = listenerbase
table = tlgi_survey
change attribute = status
based on = record_id

And I have know idea why the example has 2 where's in the example?

Remember that I have a Windows 2000 no SSIS available, that I know of only DTS.

[rmiao]

DTS in sql2k does similar thing as SSIS in sql2k5.

[demodav]

I keep reading about this thing called stored procedures that one can make into a job to execute daily.

Can stored procedures be placed in a DTS?

I want to execute one function that will update needed data and insert the rest from MS SQL to MySQL.

Seems so simple, I don't know why it is so hard. Oh, wait probably alot simpler if the server stuff was updated.

[demodav]

Can you help me with the queries? Thank you so much, I am learning so much.

[rmiao]

You can set sql job to run sp in schedule, read sql books online for details.