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

Ok, I have progressed a little bit.
You have to rename the section in the odbcinst.ini file. QGIS and ogr2ogr
expects the section [SQL Server] and not [ODBC Driver 17 for SQL Server] as
written by Microsoft package.

Then I can use ogr2ogr to connect to my database and convert some table
with this:
ogr2ogr -f "ESRI Shapefile" "/tmp/Carto.vPole"
"MSSQL:server=myserver;database=MyBase;uid=carto;pwd=*******;" -overwrite
-sql "SELECT id, position FROM Carto.vPole"

Now I need to understand the SQL Server syntax, and understand why I have
hieroglyphs in my text fields (encoding I presume...)

Post by kimaidou
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
mssql-cli -S myserver -U carto -P ******* -d MyBase
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
I tested with
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
[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

Jorge Gustavo Rocha

2018-11-17 23:55:49 UTC

Hi Michaël,

My first guest is that you need to update your GDAL. I was able to
import MSSQL tables with GDAL 2.3.2. With previous versions, I was
getting those hieroglyphs too.

I've imported a MSSQL Server database to Postgresql that was written by
Geomedia.

To recover the geometry, I had to use the MSSQL function:
geometry_spa.STAsBinary()

Example of the script:

(...)
#Export using ogr2ogr
ogr2ogr -overwrite -skipfailures -preserve_fid -f "PostgreSQL"
PG:"host=$pghost user=$pguser dbname=$pgdb password=$pgpwd
active_schema=$pgschema" -nln $table_name /vsistdin/ <<EOF
<OGRVRTDataSource>
<OGRVRTLayer name="$table_name">
<GeometryField encoding="WKB" field="the_geom" />
<SrcDataSource>MSSQL:driver={ODBC Driver 17 for SQL
Server};server=$msserver,59228;database=$mssqlschema;tables=$table_name;UID=$msuser;PWD=$mspassword</SrcDataSource>
<LayerSRS>EPSG:3763</LayerSRS>
<SrcSQL>SELECT *, geometry_spa.STAsBinary() as the_geom FROM
$table_name</SrcSQL>
</OGRVRTLayer>
</OGRVRTDataSource>
EOF

I hope it helps.

Regards,

Jorge

Post by kimaidou
Ok, I have progressed a little bit.
You have to rename the section in the odbcinst.ini file. QGIS and
ogr2ogr expects the section [SQL Server] and not [ODBC Driver 17 for SQL
Server] as written by Microsoft package.
Then I can use ogr2ogr to connect to my database and convert some table
ogr2ogr -f "ESRI Shapefile" "/tmp/Carto.vPole"
"MSSQL:server=myserver;database=MyBase;uid=carto;pwd=*******;"
-overwrite -sql "SELECT id, position FROM Carto.vPole"
Now I need to understand the SQL Server syntax, and understand why I
have hieroglyphs in my text fields (encoding I presume...)
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
mssql-cli -S myserver -U carto -P ******* -d MyBase
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
I tested with
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
[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
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

J. Gustavo

--
Jorge Gustavo Rocha
Departamento de Informática
Universidade do Minho
4710-057 Braga
Tel: +351 253604480
Fax: +351 253604471
Móvel: +351 910333888
skype: nabocudnosor

Nyall Dawson

2018-11-18 23:12:02 UTC

Post by kimaidou
Hi all,
I am trying to connect to a MSSQL server from a QGIS installed in a Linux Debian Jessie computer.
mssql-cli -S myserver -U carto -P ******* -d MyBase
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

Post by kimaidou
I tested with
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
[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
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

kimaidou

2018-12-05 19:37:41 UTC

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)

Have anyone seen this behaviour before ?

Cheers
MichaÃ«l

Post by kimaidou
Hi all,
I am trying to connect to a MSSQL server from a QGIS installed in a

Linux Debian Jessie computer.

Post by kimaidou
I have checked that the credentials are ok with the mssql-client from
mssql-cli -S myserver -U carto -P ******* -d MyBase
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

Post by kimaidou
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

Post by kimaidou
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.

Post by kimaidou
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

Post by kimaidou
I tested with
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
[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
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Nyall Dawson

2018-12-05 22:55:32 UTC

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

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

kimaidou

2018-12-08 17:11:23 UTC

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.

Cheers,
MichaÃ«l

Post by kimaidou
Thanks for your answers. I managed to connect with QGIS MSSQL native

provider under my Debian box with the following steps

Post by kimaidou
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

Post by kimaidou
Have anyone seen this behaviour before ?
Cheers
MichaÃ«l

Post by kimaidou
Hi all,
I am trying to connect to a MSSQL server from a QGIS installed in a

Linux Debian Jessie computer.

Post by kimaidou
curl https://packages.microsoft.com/config/debian/8/prod.list >

/etc/apt/sources.list.d/mssql-release.list

Post by kimaidou
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

Post by kimaidou
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.

Post by kimaidou
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

Post by Nyall Dawson
for how the Travis testing environment sets up a connection, there may
be some clues there.
Nyall

Post by kimaidou
I tested with
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
[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
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer

Nyall Dawson

2018-12-10 00:03:36 UTC

Post by kimaidou
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

Post by kimaidou
Cheers,
Michaël

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