I have an application that uses MicrosoftSQL as the backend. I also have
associated applications that run on a LAMP server. I'd like to setup
MySQL to have a copy of the MicrosoftSQL backend data and that is updated
when changes are made to the MicrosoftSQL backend via the application so my
programmer can do his PHP magic on the data and make everything work
together.

I've found some older documentation that suggests that I could setup a
linked server on the MicrosoftSQL server and then create triggers to
synchronize the table data. I've performed some initial testing by way
of creating an idetical tablle in MySQL and then linking MicrosoftSQL to the
MySQL server with an ODBC connection. I'm stuck now because my older
documentation isn't what I need for the newer versions of the
software.

I'm using MicrosoftSQL 2005 and MySQL 5.0.26. I'm looking for some good
documentation to follow for the setup. I can setup so that MicrosoftSQL
pushes the data changes or so MySQL pulls the data changes to the
database in question.

On Feb 11, 10:45 am, Michael <michaelfromtheb... (AT) gmail (DOT) com> wrote:
I've found a product, dbqwiksynch, that I used to create the identical
structure from MicrosoftSQL to MySQL. It worked as advertised. Now I need to
get this MicrosoftSQL database to update the MySQL database without help from
third-party apps.

On Feb 11, 11:47 am, Michael <michaelfromtheb... (AT) gmail (DOT) com> wrote:
Now I have created tiggers in MicrosoftSQL that should insert/update/delete
to a table that is in both databases. Here is the syntax for the
triggers:

CREATE TRIGGER ScoffLaw_insert ON [dbo].[ScoffLaw]
FOR INSERT
AS
SET XACT_ABORT ON
INSERT INTO OPENQUERY(PRODUCTION, 'Select * from ScoffLaw')
SELECT Scofflaw_Key, Code, Type, Data, License_State,
License_Type, License_Color, Source, ExpDate, IssDate,
Entry_By, Entry_Date, Product_Source FROM INSERTED

GO

CREATE TRIGGER ScoffLaw_update ON [dbo].[ScoffLaw]
FOR UPDATE
AS
SET XACT_ABORT ON
DELETE FROM OPENQUERY(PRODUCTION, 'Select * from ScoffLaw')
WHERE Scofflaw_Key IN (SELECT Scofflaw_Key FROM DELETED)
INSERT INTO OPENQUERY(PRODUCTION, 'Select * from ScoffLaw')
SELECT Scofflaw_Key, Code, Type, Data, License_State,
License_Type, License_Color, Source, ExpDate, IssDate,
Entry_By, Entry_Date, Product_Source FROM INSERTED

GO

CREATE TRIGGER ScoffLaw_delete ON [dbo].[ScoffLaw]
FOR DELETE
AS
SET XACT_ABORT ON
DELETE FROM OPENQUERY(PRODUCTION, 'Select * from ScoffLaw')
WHERE Scofflaw_Key IN (SELECT Scofflaw_Key FROM DELETED)

GO

Now the issue is that I'm getting an error message through the
application that uses the MicrosoftSQL server as the back end.

SYSTEM ERROR ENCOUNTERED
LOCATION: cmdSave_Click
ERROR#: -2147467259
DESC: [Microsoft][ODBC SQL Driver ][SQL Server]OLE DB providor
"MSDASQL" for linked server "PRODUCTION" returned message "[MySQL]
[ODBC 3.51 Driver]Optional feature not supported"

I believe that this has something to due with the link and not having
sufficient privileges to make the insertion/update/delete action. I
have set the linked server object to use a username password
combination that has been setup on the mySQL server for this.

Michael (michaelfromtheboro (AT) gmail (DOT) com) writes:
I would interpret the message by what it says: you are trying to do
something which is not supported by the MySQL ODBC driver.

I think that if you can find an OLE DB prodvider for MySQL, you should
use that instead. That's at least one layer less.

Did you try using four-part notation instead? That is

INSERT INTO PRODUCTION...ScoffLaw (...) SELECT ...

(You will probably need to fill in something for catalog and schema.)

Other alternatives you could consider is to use replication or
SQL Server Integration Services. I have used neither, so I cannot
really give any advice, but the advantages is that the updates are
less connected.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hello,

Take a look at http://dbconvert.com/convert-mssql-to-mysql-sync.php

regards,
Dmitry

On Feb 11, 10:45*pm, Michael <michaelfromtheb... (AT) gmail (DOT) com> wrote: