MS SQL, TDSODBC, and Base?

[bmcgrady]

I'm currently trying to setup an Ubuntu (7.10) desktop replacement for my current Windows XP desktop at work. On the XP desktop, I am currently using the built-in ODBC driver for MS SQL to connect to a database on our SQL Enterprise server. I'm accessing data using MS Access 2003 using that ODBC data source.

So, I've downloaded and installed unix-odbc as well as the tds odbc packages. I've got access to my database configured to the point that I can use isql to connect to my database and run simple sql statements (such as select * from tablename). From within Base I can also connect to the database using that same odbc connection. I can even see the table names... but that's where the trouble begins. I have been unable to execute a query constructed with the query wizard. When I try, I get an error that says "The data content could not be loaded", with "Incorrect syntax near 'LME' (where LME is my data source name). Clicking the "More" button reveals that the statement that is trying to be executed is SELECT "id" FROM "LME"."dbo"."driver", where LME again is the data source name, "id" is the field name, and "driver" is the table name. If I use the SQL editor instead of the wizard and just type the sql statement directly, it still fails. The only way I can get it to work is if I select "Run SQL command directly" and type something like SELECT id from [driver]. That returns a result set. I can also enclose the field name in square brackets... that works ok too. So, it all seems to boil down to a failure in how the field names and table names are delimited.

So, the question is, are there any sort of parameters or anything that I can set that will take care of this issue, so that queries can be constructed using the graphical query designer?

Thanks,
-Barry

[DrewJensen]

Just as a quick thought - I have this but haven't had a chance to try it yet - Microsoft released a new JDBC driver for MS SQL for both windows and Unix just a couple of months ago.

http://www.microsoft.com/downloads/deta ... 66235EFC7C

You could give this a try instead of the unix-odbc and see if it performs better for you.

If you do please let us know how it goes for you..

Drew