SqlServer连接mysql


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

方案有二种:
1,OPENROWSET
SELECT *
FROM
OPENROWSET('MSDASQL','DRIVER={MySQL ODBC 3.51 Driver};
SERVER=192.168.16.210;PORT=3306;DATABASE=mydb;USER=mas;PASSWORD=123456;
STMT=set names gb2312;OPTION=2049',
'select bill_time,mobile,sms_content from tbl_sm_bill_120809;')

2,建立链接服务器。
  1)安装mysql ODBC
  2) 配置系统DSN,控制面板 – 管理工具-数据源 (ODBC)
  3)sqlserver中增加链接服务器。
参考:How To Setup SQL Server Linked Server to MySQL

  4)使用:SELECT * FROM mysqlsms…tbl_sm_bill_120809
     或者:SELECT * FROM OPENQUERY(mysqlsms, 'SELECT * FROM tbl_sm_bill_120809;')

  对于mysql中列是char型的查询会报错,如:

链接服务器 'cti' 的 OLE DB 访问接口 'MSDASQL' 返回的数据与列 '[MSDASQL].V_Caller' 所需的数据长度不匹配。所需的(最大)数据长度为 60,但返回的数据长度为 24。
  处理方法,在查询时转换一下
  

    SELECT TOP 10 * 
    FROM OPENQUERY(cti, 'SELECT cast(tel as char(30)) FROM mytable;')

INSERT INTO OPENQUERY(Servername,'select * from DBName.tablename WHERE 1=2') --1=2防止查询整个表
SELECT '1'

SELECT *
FROM OPENQUERY(Servername,'select * from DBName.tablename')

UPDATE OPENQUERY(Servername,'select * from DBName.tablename')
SET colname='2'
WHERE colname='1'

SELECT *
FROM OPENQUERY(Servername,'select * from DBName.tablename')