March 2, 2008 at 2:34 am
I need to interface one of my applications with a third party application which uses MySQL while mine runs on SQLServer2000. My app (SQLServer) will import guest checkin and checkout records from the hotel's Front Office System (MySQL) and export restaurant bills signed by the guests back to the hotel's system.
Since the data interchange is supposed to be done realtime, I am of the opinion that triggers are my best bet. The problem is: is there a way to have a trigger update a table in a database on another platform?
March 2, 2008 at 8:15 am
Since you need it realtime, your best option may be to define a linked server for the mysql server.
Check BOL.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 3, 2008 at 8:34 am
March 4, 2008 at 1:03 am
one of the caveots is that your server gets dependent to another server.
This will make maintenance for the connected ones more difficult.
To be able to connect with mysql, you'll need to install the odbc/oledb provider for mysql at your sqlserver server.
A linked server is a direct link from sqlserver to another datasource.
I don't encourage the use of it, but it is better than using "openrowset" from within your applications/procs.
So if you realy need the online-realtime link, it may be your best option.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 4, 2008 at 1:59 am
March 4, 2008 at 2:29 am
I'm not familiar with mysql :blush:
But hooking it op with sql2000 can be done using a linked server;
The code below works for msaccess
USE [master]
GO
-- EXEC master.dbo.sp_dropserver @server=N'LSTEST', @droplogins='droplogins'
EXEC master.dbo.sp_addlinkedserver @server = N'LSTEST', @srvproduct=N'MSAccess', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'x:\accessfiles\test_data.mdb', @provstr=N''
GO
EXEC master.dbo.sp_serveroption @server=N'LSTEST', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LSTEST', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LSTEST', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LSTEST', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LSTEST', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LSTEST', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LSTEST', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LSTEST', @optname=N'use remote collation', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LSTEST', @locallogin = NULL , @useself = N'False'
GO
/* Query one table of a linked server using QueryAnalyser */
select *
from LSTEST...theaccesstable
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 4, 2008 at 8:01 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply