Ask Your Question
0

HELP! Problems accessing a Microsoft SQL server database from Linux

asked 2018-05-25 15:12:44 +0100

unfa gravatar image

updated 2018-05-28 15:38:32 +0100

Introduction

I need to prepare a complex report from a big table database. I'm running Linux Mint 18.3, the database resides on a Microsoft SQL Server. I have success with sqsh - I can run queries and I have even mirrored all tables to local CSV files, but I need to be able to access the database in a more user-friendly environment. I have to explore table relationships and reverse-engineer the database structure to be able to produce that report. As I'm learning SQL as I go, I don't really feel I can get any further using just sqsh for my exploration.

ODBC

I managed to connect to the database with LibreOffice Base 5.1.6.2 - I can list the tables but trying to open them I get SQL syntax errors:

image description

Maybe I should set some flags somewhere?

I have installed LibreOffice 6.0. Whe nI open the same database file I created with LibreOffice 5 - it prompts me for user and password, but then it fails to load the ODBC driver:

image description

Even though it's present in my system:

$ file '/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so'
/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, BuildID[sha1]=307e2b20ed1be01cab757c3ecd4d6e85f4a18b7b, stripped

I've tried creating a new database file to define the connection anew, but it fails the same way when I press "Test Connection" - so that's not a thing with loading LO 5 file.

I've tried preloading that library:

$ LD_PRELOAD="/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so" libreoffice6.0 
ERROR: ld.so: object '/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so' from LD_PRELOAD cannot be preloaded (wrong ELF class: ELFCLASS64): ignored.

Maybe I should reinstall the ODBC driver?

JDBC

I tried using JDBC instead of ODBC to avoid this problem. From what I found Microsoft SQL Server has JDBC support since version 2008, so I should be able to use that driver. However - I have no idea how to configure that. I got the JRE installed and the Microsoft JDBC driver .jre file added to class path. I've tried various things and dug on forums but didn't have any success:

image description

What should I put in these fields? I am really out of ideas for now.

Apache OpenOffice

I've tried the ODBC connection with another package, but it has the same behaviour. However, it showned me more detail about the problem:

The SQL command leading to this error is:

SELECT * FROM "DATABASE"."dbo"."A4B_IntTmpE_DBA_View"

I've tried this exact query with sqsh and it worked just fine. Not sure why LibreOffice chokes on that.

Bugs?

I have a feeling that what I am struggling with often is cause by some problems in the software, so I have filed two bug reports:

https://bugs.documentfoundation.org/s...

https://bugs.documentfoundation.org/s...

Finale

I've put many hours now into trying to solve this. I really need to make some progress finally. Any help will be greatly appreciated ... (more)

edit retag flag offensive close merge delete

Comments

Did you get this working?
There are other drivers to try.

LibreTraining gravatar imageLibreTraining ( 2018-05-28 01:08:28 +0100 )edit

Not yet. I'm now back to bang my head against this after the weekend.

unfa gravatar imageunfa ( 2018-05-28 11:53:06 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-05-25 18:57:39 +0100

Ratslinger gravatar image

updated 2018-05-26 00:21:59 +0100

Hello,

Don't have access to your DB type but do know (from another question I dealt with) an ODBC connection does work.

However, from what you show & what I see in the MS connector for Ubuntu (Mint 18) you are using the wrong connector. From the Ubuntu 17.10 packages here -> Download ODBC Driver for SQL Server, the driver is libmsodbcsql-17.

Sorry, but that is probably the most I can help with.

Edit:

Also found this which may be of some help -> SQL Server on Linux: Using LibreOffice Base as a Client Application

edit flag offensive delete link more

Comments

Thanks! I've installed the driver you mentioned. However using that one - LibreOffice 5 says it can't find the library file just like LibreOffice 6 does with the FreeTDS driver. With FreeTDS driver, LO 5 still partially works under Linux. On Windows 10 it just works with built-in ODBC sources manager. I'd like to avoid the overhead of using a virtual machine though.

unfa gravatar imageunfa ( 2018-05-28 12:42:27 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-25 15:12:44 +0100

Seen: 800 times

Last updated: May 28 '18