SqlServer链式服务器连接mysql

 

1、控制面板-管理工具-ODBC数据源（64位）

 

2、创建数据源

 

链接服务器 'MYSQL_DBLINK' 的 OLE DB 访问接口 'MSDASQL' 返回了对列 '[MSDASQL].board_city_name' 无效的数据，指定编码格式，然后删除重新建立链接服务器。 

 

 

 

3、创建链式服务器，选择数据源，安全性中录入密码

 

 

 

 

4、查询

 

 

 

SELECT ai.id,psg.user_id AS PsgId,psg.psg_cn_name AS PsgName,o.insert_by AS CreateBy,ai.insert_time AS CreateTime,o.user_code AS UserCode,
1 AS InvoiceSource,ai.order_no AS OrderNo,
sg.board_city_name AS DepartureCity,sg.off_city_name AS ArriveCity,sg.board_point_code AS DepartureCode,sg.off_point_code AS ArrivalCode,
sg.departure_date AS DepartureTime,sg.arrive_date AS ArrivalTime,
sg.cabin_class_code AS SeatCategoryCode,
psg.refund_fee AS Amount,
'TP' AS FeeType,'JP' AS InvoiceType,psg.ticket_no AS InvoiceNum 
FROM OPENQUERY(INTERNATIONAL_AIR_TICKET,'select * from t_gwzj_intl_air_order_invoice') ai
INNER JOIN OPENQUERY(INTERNATIONAL_AIR_TICKET,'select * from t_gwzj_intl_air_order_passenger') psg ON ai.order_no=psg.order_no AND ai.psg_id=psg.id
INNER JOIN OPENQUERY(INTERNATIONAL_AIR_TICKET,'select * from t_gwzj_intl_air_order') o ON o.order_no=ai.order_no AND (o.order_status='TP' OR o.order_status='TH') AND o.order_type='T'
INNER JOIN OPENQUERY(INTERNATIONAL_AIR_TICKET,'select * from t_gwzj_intl_air_order_segment') sg ON sg.order_no=ai.order_no
WHERE ai.invoice_type='RP' 
AND EXISTS(SELECT * FROM OPENQUERY(INTERNATIONAL_AIR_TICKET,'select * from t_gwzj_intl_air_order_invoice_header') h WHERE h.order_no=o.order_no AND h.invoice_head_type='U')