OPENQUERY with INET_NTOA (or reason # 5,643,532 varchar(16) is better)

July 20th, 2010 No Comments

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?

Read More »

Creating tables with SELECT INTO on MS SQL Server

April 27th, 2010 No Comments

I learned an interesting little tidbit about databases today. I’ve been working to create a materialized view (ie a static table) and needed to create it from two distinct queries. I ran the first query as a SELECT INTO which pumped the results of the data into the new materialized view. Then I ran the second query as an INSERT INTO (since the table was already previously created by the SELECT INTO). Even though all the columns were the same between the two queries, the INSERT INTO kept failing, saying that it couldn’t convert columnX into an int (even though the column returned by both queries was a varchar).

I beat my head against this for a while until I finally looked at the table definition. It appears that when a table is created using SELECT INTO, SQL Server 2005 will create the columns based on the largest data and type it sees, not the definition of the column that it was coming from. Therefore, when I ran the first query in which columnX was all NULL values, it created the column type as INT instead of type VARCHAR(MAX).

So I guess the moral of the story is that when you create a table with SELECT INTO, you should check the column definitions.

Read More »

What I’ve been listening to Jan 2010

January 2nd, 2010 No Comments

I thought I would share what I've been listening to recently.  Between the no-fi of the Japandroids and Wavves to the Dubstep of Black Chow and Darkstar, I have a feeling that this list might not appeal to everyone, but there are some good ones on there.

  1. Purple Smoke – Black Chow (5:Five Years of Hyperdub) 
  2. Wet Hair – Japandroids (Post-Nothing)
  3. Warm Heart Of Africa – The Very Best Ft Ezra Koenig (Warm Heart of Africa)
  4. Kick Drum Heart – The Avett Brothers (I and Love and You)
  5. Silvia – Miike Snow (Miike Snow)
  6. The Horizon Is A Beltway – The Low Anthem (Oh My God, Charlie Darwin)
  7. Cosmic Love – Florence and the Machine (Between Two Lungs)
  8. Aidy's Girl's A Computer – Darkstar (5: Five Years of Hyperdub)
  9. To The Dregs – Wavves (Wavves)

Posted via email from Sean’s posterous

Read More »

What the World Looked Like 10 Years Ago Today

December 30th, 2009 No Comments

Foreign Policy has a reall interesting pice about what was going on today (Dec 29th) in 1999.  Looking back on it it looks almost like a different world. Some examples:

  • Something called Inktomi was the world’s largest search engine.
  • Everybody was clamoring for the new file-sharing program Napster
  • llinois State Senator Barack Obama campaigned for a spot in the House of Representatives.
  • Boris Yeltsin was preparing to step down and make way for the young pragmatist Vladimir Putin
  • You could meet your loved ones at their arrival gate.
  • China’s GDP was $1.4 trillion, half of Germany’s.
  • Beltway pundits believed Al Gore and George W. Bush were centrists who would govern similarly.

Full story here

Posted via web from Sean’s posterous

Read More »

Counting cash around the world [video]

December 6th, 2009 No Comments

I saw this video today and it is really interesting. There seems to be some debate about how accurate the video is. The connecencous of most of the commenters that I’ve seen seems to be that this is a pretty broad generalzation and not completely accurate. But alot were say that they had either seen money counted like this or actually did count money like this. The thing that got me though,  was that people actually do count money differently depending on where you come from.  Give it a look:

 

Posted via web from Sean’s posterous

Read More »

The best thing I’ve read all day

December 4th, 2009 No Comments

I weep for you if this makes you laugh as hard as it made me laugh...

 

… Every time you attempt to parse HTML with regular expressions, the unholy child weeps the blood of virgins, and Russian hackers pwn your webapp. Parsing HTML with regex summons tainted souls into the realm of the living. HTML and regex go together like love, marriage, and ritual infanticide. The <center> cannot hold it is too late. The force of regex and HTML together in the same conceptual space will destroy your mind like so much watery putty. If you parse HTML with regex you are giving in to Them and their blasphemous ways which doom us all to inhuman toil for the One whose Name cannot be expressed in the Basic Multilingual Plane, he comes. HTML-plus-regexp will liquify the n​erves of the sentient whilst you observe, your psyche withering in the onslaught of horror….

Have you tried using an XML parser instead?

 

whole thing at Stack Overflow (via Coding Horror)

Posted via web from Sean’s posterous

Read More »

Testing Posterous

December 3rd, 2009 No Comments

Tap tap tap…

…is this thing on?

via This isn't happiness http://feedproxy.google.com/~r/thisisnthappiness/~3/SM-LOkGDItU/263699137

Posted via email from seanelavelle’s posterous

Read More »

Yellowstone National Park

August 22nd, 2009 No Comments

It’s been two weeks since we got back from Yellowstone and I am now only finally getting around to putting up a post about it.  I do have good reason though since I was out of town the weekend we came back and then started a new job last week. Anyways, Yellowstone was awesome. If you’ve never been there, I would highly recommend it.

Old Faithful

Old Faithful

We camped for 5 nights there. 2 nights in Canyon Campground and 3 nights in Bridge Bay Campground. Both were nice, but I like Canyon better.  It was near the Grand Canyon of the Yellowstone River and centrally located to the park.

Yellowstone was somewhat crowded for a national park (I’m used to the Zion and Canyonlands where if you see one other person on the trail that day it is crowded) but the scenery is spectacular and there is tons of wildlife to see.

Bald Eagle

Bald Eagle

Since it was fly fishing country, I tryed it out and found I really enjoy it. I didn’t catch anything and managed to hook myself in the back several times, but it was something that was alot of fun. If you are out that way and want to give it a shot forcheap, there is a Walmart in Idaho Falls, ID (on the way to the park from Salt Lake City) that sells a beginners kit all pretied and setup for $25 dollars. I plan to try to post a little more about fly fishing when I get better at it.

Me fly fishing

Me fly fishing

Some of the things to remember if you head out there:

  • The gysers can get crowded. We went early in the morning to see Old Faithful and there was almost no one there. That is totally time to do it.
  • It gets COLD there. We were there in the first week of August and it was in the 70s during the day but dropped to the hig 30s low 40s at night.
  • The wildlife is somewhat used to people, but it doesn’t mean that it is harmless. We saw lots of idiots getting 15 feet from a 2000 pound buffalo for a picture. This is a BAD IDEA.
  • Also make sure any food you have is put away. There are bears, including Grizzlies.
  • Yellowstone like is beautiful and a lot of people don’t visit it. It is the largest high altitude lake in the country and really worth checking out.
  • Most of the bears we saw were in the north central part of the park around Tower Junction.
  • Most of the buffalo we saw were in the Hayden Valley.

Here is the link to the photoset I took at Flickr

Read More »

Griggs Reservoir Photos

July 12th, 2009 1 Comment

Jess wanted to do some fishing last night, so we drove down to Griggs Reservoir.  I don’t really fish, but I really enjoy going sometimes since the light at dusk can be so good for taking pictures.

3713037258_06e1dcc1a7

I find that the more I shoot and edit pictures, the less I care about them being an accurate representation of things. I’ve been finding my “arty” shots have become a lot less photo realistic and more impressionistic, and I am pretty happy with the results.

3713035600_7695cb0da4

Here is the full set on flickr.

Read More »

Twitter usage FAIL

June 27th, 2009 No Comments

Last night, my wife had one of the most hilarious conversations on twitter I have ever seen. A friend of hers had signed up for a twitter account earlier in the day and just followed her.  He also chose to turn on the option to receive tweets as text messages.

I have included the timeline unedited for your enjoyment. The names and pictures have been removed to protect the innocent, but otherwise has not been edited.

For reference, my wife Jess’ name is blotted out in pink

Her friends name is blotted out green

Other conversations are blotted out in blue.

Read from the bottom to the top:

twitter-fail

At the end, she finally called him and explained what was going on.

Read More »
« Older Entries
Subscribe to RSS Feed