Heterogeneous Query Error

Some background… My company is migrating a Oracle database to Sybase. The new database is pretty radical compared to the old, so there’s a lot of work to be done in the transition. I’m using an Access database as a kind of go between right now.

My problem, I’m trying to do a simple select query between my Access database and the Sybase, and when I say simple, I mean dead simple. But it fails, “[Microsoft][ODBC Microsoft Access Driver] ODBC–call failed.” I can’t figure out what to do to make this work. Any help would be greatly appreciated. Thanks!

Do you have any issues when using ODBC to connect to that Sybase database?

Actually, yes… and no. When I make a linked table in Access to the Sybase database it is incomplete. I am linking to a view, not to the table directly. It shows a few of the fields on the view, but not all of them, in fact it is missing the field I am trying to join on which is probably the big issue there.

When I try to make a linked table to the underlying table that the view depends on I get an error, “Reserved error (-7748; there is no message for this error.” I’ve looked into that error and found a very through explaination by Sybase, http://search.sybase.com/kbx/solvedcases?id_number=10855510 Apparently there is a workaround that needs to be entered in the ODBC DSN string. How can I modify Toad’s ODBC DSN string?

Can you try using the ODBC driver that we ship with TDA, “Adaptive Server Enterprise (TDA)” and see if you have the same issues.

The connection strings for heterogeneous queries are created on the fly and cannot be edited.

When I try using Adaptive Server Enterprise (TDA) I get an error:

ODBC–call failed.

[Sybase][ODBC Driver][Adaptive Server Enterprise]Stored procedure ‘sp_odbc_tables’ not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

What I was using previously was Sybase ASE ODBC Driver, version

The older Sybase ODBC drivers were provided by DataDirect and I believe with version 15.X and up they are no longer supported. The version we ship is the new ODBC driver developed by Sybase. The new driver requires stored procedures to be installed on the server. More info can be found at: http://infocenter.sybase.com/help/topic/com.sybase.help.sdk_12.5.1.aseodbc/html/aseodbc/aseodbc28.htm

Would it be possible for the DBA to install the ODBC stored procedures or are you tied to the DataDirect driver? Let me know.