[QGIS-Developer] MSSQL driver not found & Can't open lib 'SQL Server' - QGIS 2.18 LTR on Debian
Jorge Gustavo Rocha
jgr at di.uminho.pt
Sat Nov 17 15:55:49 PST 2018
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
Às 16:55 de 16/11/18, kimaidou escreveu:
> 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...)
>
>
>
>
>
> Le ven. 16 nov. 2018 à 17:00, kimaidou <kimaidou at gmail.com
> <mailto:kimaidou at gmail.com>> a écrit :
>
> 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
>
> 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
>
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
More information about the QGIS-Developer
mailing list