Oracle, HSODBC and old SQL Server versions



A particularly dry title but i think it's worth posting this as it had me stuck for a couple of days this week. As some of you may have read i wrote up a post about how to connect oracle to sql server through odbc for free using the freetds drivers - see here

This works great and i wanted to reuse that functionality again.

This time i wanted to connect 12.1.0.2 to SQL 2005 which is like connecting a cray supercomputer to a typewriter...

Anyway it should work fine (or so i thought) - i followed by own post (again feeling smug that this was so easy to now setup). After configuring everything i gave it a test and the damn thing didn't work.

I got the useless generic error message ORA-28500 and nothing else - the real error didn't even appear

So i activated trace by adding this to the init file for the hs service

HS_FDS_TRACE_LEVEL = Debug

Then i ran the process again and got this


Exiting hgopars, rc=28500 at 2015/10/12-14:12:14 with error ptr FILE:hgopars.c LINE:578 ID:SELECT list of size 0 is not valid


Again not really that useful, however i did discover while trying this out that describe worked fine - it was only the select statements that were failing - so the link was kind of there....

After much trial and error (and to be honest total guesswork) i discovered the fix

I had to add

TDS_Version=7.2


in the odbc.ini file for the connection definition - this tells freetds that the db i'm talking to is SQL2005 and to behave differently. I think basically the ODBC call order must be slightly different between versions and using the newer one against the older db was doing things in the wrong order.

There is a full list here of what setting you should use for each database version



Comments

Post a Comment