Write from MS SQL to MySQL

[Dave]

I have a MS sql 2000 db that needs to sync with a Mysql db.

When a password is changed in the MS SQL table, I need to make sure
that the same value is updated in the Mysql db.

I dont have any control of the gui that is initiating this event. So,
I was thinking a trigger might be an alternate route.

I just dont know how to get the username and password values that have
just been updated from MS sql to Mysql.

I do understand how to build the sql statements for both sides, but
this has to be automated and fire real time.

example:
if username "joe" just changed thier password to "pass" then I need to
fire a query, event, function, whatever, that will update the password
field in the Mysql table where the username is "joe" to "pass".

Any ideas or samples would be greatly apperciated.

[Erland Sommarskog]

Re: Write from MS SQL to MySQL

Dave (funkdm1@yahoo. com) writes:[color=blue]
> I have a MS sql 2000 db that needs to sync with a Mysql db.
>
> When a password is changed in the MS SQL table, I need to make sure
> that the same value is updated in the Mysql db.
>
> I dont have any control of the gui that is initiating this event. So,
> I was thinking a trigger might be an alternate route.
>
> I just dont know how to get the username and password values that have
> just been updated from MS sql to Mysql.
>
> I do understand how to build the sql statements for both sides, but
> this has to be automated and fire real time.
>
> example:
> if username "joe" just changed thier password to "pass" then I need to
> fire a query, event, function, whatever, that will update the password
> field in the Mysql table where the username is "joe" to "pass".
>
> Any ideas or samples would be greatly apperciated.[/color]

A presumption for this to work, is that there is an OLE DB provider
that can talk to MySQL. I would not expect there there is an targeted
provider, but if there is an ODBC driver, then you can use MSDASQL,
OLEDB over ODBC.

If it's only a single value that you need to replicate, a trigger
that updates a table on a linked server would be the way to go. You
set up the linked server with sp_addlinkedser ver, see Books Online
for details. You can also do it from Enterprise Manager. In the trigger
you would say:

UPDATE MYSQL.db.schema .tbl
SET password = i.password
FROM inserted i
JOIN MYSQL.db.schema .tbl m ON i.userid = m.userid

If there are plnetyful of tables you need to replicate, you should
probably look into replication.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Dave]

Re: Write from MS SQL to MySQL

Erland,

Thanks for responding to my post. It appears there isnt too much help
out here regarding what I need to do.

I have created the linked server in ms sql, and I can see the table in
Enterprise manager just fine. However when I try to run the following
query from ms query analyzer I cant get results, only errors.

("Editor" is the name of my mysql dsn)
("test" is the name of the mysql database)
("userinfo" is the name of my table in mysql)

SELECT * FROM OPENQUERY(Edito r, 'SELECT * FROM userinfo')

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [MySQL][ODBC 3.51
Driver][mysqld-3.23.58]Table 'test.userinfo' doesn't exist]
OLE DB error trace [OLE/DB Provider 'MSDASQL'
IColumnsInfo::G etColumnsInfo returned 0x80004005: ].

Any ideas on this one....

Thanks again,
Dave




Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns955AEE 7C73B1EYazorman @127.0.0.1>...[color=blue]
> Dave (funkdm1@yahoo. com[/color]
) writes:[color=blue][color=green]
> > I have a MS sql 2000 db that needs to sync with a Mysql db.
> >
> > When a password is changed in the MS SQL table, I need to make sure
> > that the same value is updated in the Mysql db.
> >
> > I dont have any control of the gui that is initiating this event. So,
> > I was thinking a trigger might be an alternate route.
> >
> > I just dont know how to get the username and password values that have
> > just been updated from MS sql to Mysql.
> >
> > I do understand how to build the sql statements for both sides, but
> > this has to be automated and fire real time.
> >
> > example:
> > if username "joe" just changed thier password to "pass" then I need to
> > fire a query, event, function, whatever, that will update the password
> > field in the Mysql table where the username is "joe" to "pass".
> >
> > Any ideas or samples would be greatly apperciated.[/color]
>
> A presumption for this to work, is that there is an OLE DB provider
> that can talk to MySQL. I would not expect there there is an targeted
> provider, but if there is an ODBC driver, then you can use MSDASQL,
> OLEDB over ODBC.
>
> If it's only a single value that you need to replicate, a trigger
> that updates a table on a linked server would be the way to go. You
> set up the linked server with sp_addlinkedser ver, see Books Online
> for details. You can also do it from Enterprise Manager. In the trigger
> you would say:
>
> UPDATE MYSQL.db.schema .tbl
> SET password = i.password
> FROM inserted i
> JOIN MYSQL.db.schema .tbl m ON i.userid = m.userid
>
> If there are plnetyful of tables you need to replicate, you should
> probably look into replication.[/color]

[Erland Sommarskog]

Re: Write from MS SQL to MySQL

Dave (funkdm1@yahoo. com) writes:[color=blue]
> Thanks for responding to my post. It appears there isnt too much help
> out here regarding what I need to do.
>
> I have created the linked server in ms sql, and I can see the table in
> Enterprise manager just fine. However when I try to run the following
> query from ms query analyzer I cant get results, only errors.
>
> ("Editor" is the name of my mysql dsn)
> ("test" is the name of the mysql database)
> ("userinfo" is the name of my table in mysql)
>
> SELECT * FROM OPENQUERY(Edito r, 'SELECT * FROM userinfo')
>
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [MySQL][ODBC 3.51
> Driver][mysqld-3.23.58]Table 'test.userinfo' doesn't exist]
> OLE DB error trace [OLE/DB Provider 'MSDASQL'
> IColumnsInfo::G etColumnsInfo returned 0x80004005: ].
>
> Any ideas on this one....[/color]

I don't know about MySQL, but the normal scheme is a three-part notation:
catalog.schema. table, as defined by ANSI. So when MySQL says that it
cannot find test.userinfo that makes me believe it's looking in schema
called test, not a database called test. But that is only my prejudice.

If you run the command with full qualification from a MySQL query tool,
how would you write?

How does your sp_addlinkedser ver command with which you defined the
server look like?

Have your tried a MySQL forum?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[cazz]

Re: Write from MS SQL to MySQL

A good migration tool - Navicat can do the trick.

It enables migrating access to mysql, excel to mysql and ms sql to mysql.

Please download a trial version here: http://www.navicat.com/download.php3