[QGIS-Developer] MSSQL driver not found & Can't open lib 'SQL Server' - QGIS 2.18 LTR on Debian

[Nyall Dawson]

On Sun, 9 Dec 2018 at 03:11, kimaidou <kimaidou at gmail.com> wrote:
>
> Hi Nyall,
>
> Thanks for your answer. I had already checked that the layers had integer primary keys. I have discovered that the "wrong" layers had "nvarchar" fields, instead of "varchar". We changed the types of the view fields from nvarchar to varchar, and it solved the issue.

Can you elaborate here? Was the issue "nvarchars" in general? Or
nvarchars used as primary keys?

Nyall

>
> Cheers,
> Michaël
>
> Le mer. 5 déc. 2018 à 23:55, Nyall Dawson <nyall.dawson at gmail.com> a écrit :
>>
>> On Thu, 6 Dec 2018 at 05:37, kimaidou <kimaidou at gmail.com> wrote:
>> >
>> > Thanks for your answers. I managed to connect with QGIS MSSQL native provider under my Debian box with the following steps
>> > https://gist.github.com/mdouchin/64f7733a3c4ecfe441f65d5529351a30
>> >
>> > I have a weird behaviour. For some layers, QGIS Desktop cannot display the features attributes : the attribute table is empty, and the identify tool returns no feature. BUT I can see the points in my map, so QGIS can query the geography field from the database. For some other layers, wich seems identical (same structure for example), I can see the attributes (full attribute table and identify OK)
>> >
>>
>> Sounds like an issue with fetching by primary key/feature ID. Is there
>> any difference between the primary key setup on these tables vs the
>> ones which work?
>>
>> Nyall
>>
>>
>>
>>
>> > Have anyone seen this behaviour before ?
>> >
>> > Cheers
>> > Michaël
>> >
>> > Le lun. 19 nov. 2018 à 00:12, Nyall Dawson <nyall.dawson at gmail.com> a écrit :
>> >>
>> >> On Sat, 17 Nov 2018 at 02:01, kimaidou <kimaidou at gmail.com> wrote:
>> >> >
>> >> > Hi all,
>> >> >
>> >> > I am trying to connect to a MSSQL server from a QGIS installed in a Linux Debian Jessie computer.
>> >> > I have checked that the credentials are ok with the mssql-client from Microsoft:
>> >> >
>> >> > mssql-cli -S myserver -U carto -P ******* -d MyBase
>> >> >
>> >> > I installed the following packages from debian and Microsoft repos :
>> >> >
>> >> > curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
>> >> > curl https://packages.microsoft.com/config/debian/8/prod.list  > /etc/apt/sources.list.d/mssql-release.list
>> >> > apt-get update
>> >> > ACCEPT_EULA=Y apt-get install -y mssql-cli unixodbc-dev mssql-tools msodbcsql17 msodbcsql odbcinst libqt4-sql-tds libqt4-sql-odbc
>> >> >
>> >> > When I open QGIS and try to set up a connection, I use not DSN, and I put my server "myserver" as the host. I add the user and password.
>> >> > Then try to list the databases, but I get this error
>> >> >
>> >> > [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found
>> >>
>> >> This is all super-fragile. (Just another symptom of SQL Server's
>> >> inferiority IMO).
>> >>
>> >> Have a look at https://github.com/qgis/QGIS/blob/master/.ci/travis/linux/docker-build-test.sh#L128
>> >> for how the Travis testing environment sets up a connection, there may
>> >> be some clues there.
>> >>
>> >> Nyall
>> >>
>> >> >
>> >> > I tested with
>> >> >
>> >> > root at carto:~# odbcinst -j
>> >> > unixODBC 2.3.1
>> >> > DRIVERS............: /etc/odbcinst.ini
>> >> > SYSTEM DATA SOURCES: /etc/odbc.ini
>> >> > FILE DATA SOURCES..: /etc/ODBCDataSources
>> >> > USER DATA SOURCES..: /root/.odbc.ini
>> >> > SQLULEN Size.......: 8
>> >> > SQLLEN Size........: 8
>> >> > SQLSETPOSIROW Size.: 8
>> >> >
>> >> > So I checked /etc/odbcinst.ini which seems ok
>> >> >
>> >> > root at carto:~# cat /etc/odbcinst.ini
>> >> > [ODBC Driver 17 for SQL Server]
>> >> > Description=Microsoft ODBC Driver 17 for SQL Server
>> >> > Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
>> >> > UsageCount=1
>> >> >
>> >> > [ODBC Driver 13 for SQL Server]
>> >> > Description=Microsoft ODBC Driver 13 for SQL Server
>> >> > Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
>> >> > UsageCount=1
>> >> >
>> >> > Should I install more packages ?  Any help appreciated.
>> >> >
>> >> > Regards,
>> >> > Michaël
>> >> >
>> >> > _______________________________________________
>> >> > QGIS-Developer mailing list
>> >> > QGIS-Developer at lists.osgeo.org
>> >> > List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>> >> > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer