Success Stories

Executing MySQL Query from SQL Server without Using Linked Server

You can query MySQL tables from SQL Server with creating and using Linked Server. Another method (but not usual) is using mysql client in SQL Server query with using xp_cmdshell.

Creating Test Table on MySQL Instance:

CREATE TABLE `TestTable` (
 `ID` INT(11) NOT NULL AUTO_INCREMENT,
 `Name` VARCHAR(20) NOT NULL,
 `Surname` VARCHAR(50) DEFAULT NULL,
 `City` VARCHAR(50) DEFAULT NULL,
 `LoginTime` DATETIME NOT NULL,
 PRIMARY KEY (`Id`)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

INSERT INTO TestTable(NAME,Surname,City,LoginTime)
VALUES('Name1','Surname1','City1',NOW()),
('Name2','Surname2','City2',NOW()),
('Name3','Surname3','City3',NOW()),
('Name4','Surname4','City4',NOW()),
('Name5','Surname5','City5',NOW()),
('Name6','Surname6','City6',NOW());

Query MySQL Table From SQL Server:

create table #cmdshell(ScriptOutput varchar(1000) null);
declare @mysqlcmd varchar(MAX)
declare @cmd varchar(MAX)
set @mysqlcmd = 'EXEC xp_cmdshell ''mysql -h1.1.1.1 -uroot -proot123 -e "select ID,Name,Surname,City,LoginTime from Test.TestTable"'''

insert into #cmdshell(ScriptOutput) exec(@mysqlcmd)

delete from #cmdshell where ScriptOutput like 'mysql:%' or ScriptOutput is null

create table #CmdOuput(ID1 int identity(1,1) ,ScriptOutput varchar(1000) null)
insert into #CmdOuput(ScriptOutput) select ScriptOutput from #cmdshell

select ID1,replace(replace(replace(ScriptOutput,' ','<>'),'><',''),'<>',' ') ScriptOutput
into #CmdOutput2
from #CmdOuput

delete from #CmdOutput2 where ID1 = (select MIN(ID1) from #CmdOutput2)

;with mycte as (
SELECT DISTINCT ID1
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS LoginTime
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS City
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Surname
, S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name
, S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS ID
FROM
(
SELECT *,CAST (N'' + REPLACE(ScriptOutput, CHAR(9), '') + '' AS XML) AS [data]
FROM #CmdOutput2) d 
CROSS APPLY d.[data].nodes('/H/r') S(a)
) 
select ID,Name,Surname,City,LoginTime from mycte order by ID;

drop table #cmdshell
drop table #CmdOuput
drop table #CmdOutput2

I used CHAR(9) character for parsing string because when querying MySQL on Windows command shell, windows added Horizantal Tab between columns data. ASCII code of Horizantal Tab is 9.

You have to install MySQL Client on SQL Server running Instance and add the mysql.exe location to the Windows PATH.

Sample Output: