-
Update using Migration
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?
-
How would I go about updating my MySQL Database everytime something changes in the SQL?
-
-
-
DTS Package?
Can it be done using a DTS package component, if so which?
-
DTS will not give you real time update.
-
Trigger.
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.
-
Tried create trigger in query analyzer instead?
-
Triggers in the SQl Query Annalyzer?
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.
Last edited by demodav; 03-25-2009 at 11:12 AM.
Reason: Updated Responce
-
Sounds your MySql provider doesn't support distributed transaction, check with MySql to find out if they have new one.
-
It does not need to be real time.
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.
-
DTS in sql2k does similar thing as SSIS in sql2k5.
-
Sp? Stored Procedures?
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.
-
Can you help me with the queries? Thank you so much, I am learning so much.
-
You can set sql job to run sp in schedule, read sql books online for details.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|