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. 4 5858
Dave (fu*****@yahoo. com) writes: 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.
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, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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 <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>... Dave (fu*****@yahoo. com
) writes: 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.
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.
Dave (fu*****@yahoo. com) writes: 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....
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, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Tanamon |
last post by:
Hello All,
I am a MySQL newbie trying to write a query that selects file_name records
possessing the highest numbered version for that unique file_name. I show
sample data and two trial queries below. Logically I want to use
max(version) as a constraint in a Where Clause. However, the max() function
is not allowed directly in a where clause.
I have contemplated a second table to track the max version for each file
name. I would like...
|
by: Pratchaya |
last post by:
Hi, All
Can i write php code to connect 2 MySQL DB. like this case. ?
My Environment :
Server < ---- > PC Client
Server =
|
by: bballr |
last post by:
I have a problem that I'm not sure if it can be done. I'm trying to
use the MySQL C API to be able to use a normal sql insert statement
that will send the data or file to a directory and NOT the database.
I'm not sure if there is anything out there that will allow this, but
I've searched everywhere, and I have found no solution. If anyone has
any work around solutions, I also welcome those. Thanks for the help.
|
by: asenthil |
last post by:
Hai to all,,
i had to tried to retrive and write a single row to a file from the database..
But dont know to write all the retrived rows to a file from the database..
how to do that...
here are my codings for writing a single row to a file...
|
by: _mario.lat |
last post by:
hallo,
I use PHP and I'd like to not write in hardcoded way password
and login to access to mysql.
how to not write password in code for access to mysql?
How can I do?
I'd like that who see my code don't see my paswords.
there is a solution?
Thank you in advance.
Mario.
| |
by: musosmiffy |
last post by:
Hi,
I have been trying to get the following working for days - I wonder if anyone could help me?
I am trying to list a set of database entries as a newspaper column. I am using classic ASP (not NET). It would seem that things fail when the code gets to if DataArray(i,j) <> "" then
The following code has been working great when connected to an Access database. I wish to swop the database to MySQL. I have proven that I am connecting OK...
|
by: Ross |
last post by:
Hello,
I am trying to Read and Write to a text file on a web server using Microsoft Visual Basic 2005 Express Edition.
So far I have managed to complete my testing with a local text file using the StreamReader Class. Is it possible to use this class when trying to read and write to a text file located on a server? If so, could you please provide some examples?
I have setup a free web host with www.ifastnet.com and have some connection...
|
by: RAG2007 |
last post by:
Hi,
I'm creating a backend for my Access front end in MySQL, and am running tests. I am coming across the infamous "Write Error".
Here's an instance:
I open a form with a subform, and when I go to change a combo-box field, I get the Write Error. Once I click drop changes, I can go ahead and change the field without seeing the error again on any field on either the form or subform. I don't get the error when I change data in a field on the...
|
by: shivapadma |
last post by:
1. i want to write a stored procedure using mysql 5.0.45 version.
2. when i tried with the following code ,i am unable to write
mysql->delimeter//
->create procedure proc(out p1 int)
->select count(*) into p1 from emp;
when i pressed enter, the following error is appeared .
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |