On Feb 11, 11:46 am, Michael <michaelfromtheb...@gmail.com> wrote:
> On Feb 11, 10:44 am, Michael <michaelfromtheb...@gmail.com> wrote:
>
>
>
> > I have an application that uses M$SQL 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 M$SQL backend data and that is updated
> > when changes are made to the M$SQL 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 M$SQL 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 M$SQL 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 M$SQL 2005 and MySQL 5.0.26. I'm looking for some good
> > documentation to follow for the setup. I can setup so that M$SQL
> > pushes the data changes or so MySQL pulls the data changes to the
> > database in question.
>
> I've found a product, dbqwiksynch, that I used to create the identical
> structure from M$SQL to MySQL. It worked as advertised. Now I need to
> get this M$SQL database to update the MySQL database without from
> third-party apps.

Now I have created tiggers in M$SQL 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 M$SQL 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.