Today I was trying to pull some IP Addresses through a Linked Server connection on SQL Server 2008 from a MYSQL server. The IP Addresses were stored as integers using MYSQL’s INET_ATON() function. The problem was when I ran the query through openquery I started getting gibberish back:
SELECT * FROM OPENQUERY(MYSQL, 'SELECT d_ip,INET_NTOA(d_ip) FROM IP_ADDRS ')
d_ip INET_NTOA(d_ip)
65540 0x302E312E302E34
3475948832 0x3230372E34362E3139372E3332
16843010 0x312E312E312E32
2840339703 0x3136392E37362E33362E323437
2840331313 0x3136392E37362E342E3439
2887125782 0x3137322E32322E31312E3232
2887126037 0x3137322E32322E31322E3231
2887136681 0x3137322E32322E35332E313639
Running this query on the MYSQL Server returned the correct results:
>SELECT d_ip, INET_NTOA(d_ip) FROM `IP_ADDRS` ;
d_ip INET_NTOA(d_ip)
65540 0.1.0.4
3475948832 207.46.197.32
16843010 1.1.1.2
2840339703 169.76.36.247
2840331313 169.76.4.49
2887125782 172.22.11.22
2887126037 172.22.12.21
2887136681 172.22.53.169
After some digging, I found that INET_NTOA returns a binary type string which the ODBC driver passes along to SQL Server.
So we need to cast the string after it is return to display it in a usable format:
SELECT d_ip, CAST(DEST AS varchar)
FROM OPENQUERY(MYSQL, 'SELECT d_ip, INET_NTOA(d_ip) AS DEST FROM IP_ADDRS ')
d_ip (No column name)
65540 0.1.0.4
3475948832 207.46.197.32
16843010 1.1.1.2
2840339703 169.76.36.247
2840331313 169.76.4.49
2887125782 172.22.11.22
2887126037 172.22.12.21
2887136681 172.22.53.169
Aren’t databases fun?







