Sybase ODBC Linked Server from SQL Server 2005 doesn’t like underscores in table names

I hit an interesting issue this week trying to integrate with a Sybase ASE 15.x database via a linked server. I managed to properly formulate my provider strings, and I was able to issue queries against most tables. However, one of my queries failed, giving me the message

Msg 7314, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "RPS" does not contain the table "{MyLinkedServer}.{MyDB}.{MySchema}.{My_table_name}". The table either does not exist or the current user does not have permissions on that table.

Strange… my capitalization was right, and the table definitely existed. Some poking showed that this error occurred for any table with an underscore. Executing the query in form SELECT * FROM OpenQuery(MyLinkedServer, ‘SELECT * FROM {MyDB}.{MySchema}.{My_table_name}") worked fine. Strange. My surmise is that confusion occurred somewhere between the ODBC->OLEDB->Sybase Query Parser translations, especially since the underscore is a single character wildcard special symbol in Sybase. I hacked and slashed around the linked server and odbc settings, but didn’t make any progress. I ended up switching out the out of the box Sybase ODBC driver for the 3rd party solution from Data Direct. Rumour has it that they’re the official developers of the ODBC drivers Sybase is shipping.  It worked like a charm, converted my data source over for me painlessly, and is probably faster to boot. I wish I knew what was happening, but sometimes you have to go around a problem. Anyone out there found a better solution?

Technorati Tags: The Battle's Won and We Kinda Won,Sybase,SQL Server,Data Direct