[gdal-dev] ogr2ogr doesn't connect to MS SQL Server

[Reetz, Michael (NLPV)]

Hi Pedro,

I’ve found msodbcsql17.dll in C:\Program Files\QGIS 3.22.8\bin\1033 and C:\Program Files\QGIS 3.22.8\bin.
Gdal-mss seems not to be on my computer. But since SQL Server Native Client isn’t supported by MS SQL Server 2019 , this shouldn’t be a Problem.

I have only the OSGeo4W Shell shipped installed with QGIS. So, the nice setup frontend is missing.

Best regards

Michael

Von: Pedro Venâncio <pedrongvenancio at gmail.com>
Gesendet: Donnerstag, 20. Oktober 2022 16:31
An: Reetz, Michael (NLPV) <Michael.Reetz at nlpvw.niedersachsen.de>
Cc: gdal-dev at lists.osgeo.org
Betreff: Re: [gdal-dev] ogr2ogr doesn't connect to MS SQL Server


ACHTUNG!! Diese E-Mail erreicht Sie von einem Absender außerhalb der niedersächsischen Landesverwaltungs-Infrastruktur mit TLS-Verschlüsselung. Bitte klicken Sie auf keine Links oder öffnen Sie keine E-Mail-Anhänge, falls Sie den Absender nicht kennen und nicht wissen, ob der Inhalt sicher ist.
Michael,

Check if you have gdal-mss and msodbcsql libs installed, like here: https://github.com/PedroVenancio/export_to_sql_server/issues/2#issuecomment-1240675079

Best regards,
Pedro



Reetz, Michael (NLPV) <Michael.Reetz at nlpvw.niedersachsen.de<mailto:Michael.Reetz at nlpvw.niedersachsen.de>> escreveu no dia quinta, 20/10/2022 à(s) 15:13:
Dear members of the GDAL-DEV group,

we are using MS SQL Server 2019 as basis for Geoserver and GIS software. There are a lot of tables that have attributes of the types geometry and/or geography. I can load these tables into QGIS (3.22.8) projects without any problems.

Now I’m asked to add an geography attribute to one table that has already an geometry attribute and populate it with coordinates by transforming the geometry values. I’ve found code how to do this with ogr2ogr here:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/335d3f6b-11f4-4e02-8d6d-b49cb59ed98d/using-ogr2ogr-to-reproject-within-a-sql-table?forum=sqlspatial


Since ogr2ogr is part of the OSGeo4W Shell installed with QGIS, I’ve tried to run this command there.

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=<my server IP>;driver=ODBC Driver 17 for SQL Server;database=<my database>;trusted_connection=yes;" "MSSQL:server=<my server IP>;driver=ODBC Driver 17 for SQL Server;database=<my database>;trusted_connection=yes;" -sql "SELECT <my attributes>, GEOM.STAsBinary(), WGS84 FROM <my table>" -s_srs "EPSG:25832" -t_srs "EPSG:4326" -update -lco "GEOM_TYPE=geography" -lco "GEOM_NAME=WGS84" -nln "<my table>"

But I receive the error message:

“Unable to open datasource `MSSQL:server=<my server IP>;driver=ODBC Driver 17 for SQL Server;database=<my database>;trusted_connection=yes;' with the following drivers. …”

MSSQLSpatial is not listed.


I’ve tried some other commands but all with MSSQL in it leads to an error.

Any idea what I can do to make it run?

Thanks in advance and best regards,

Michael


Mit freundlichen Grüßen
Im Auftrag

Michael Reetz

*****************************************
Michael Reetz
Datenbanken und Anwendungsentwicklung
Nationalpark-Verwaltung Niedersächsisches Wattenmeer
Virchowstr. 1 | 26382 Wilhelmshaven
Tel.: +49 (0) 4421 911 291
e-mail: michael.reetz at nlpvw.niedersachsen.de<mailto:michael.reetz at nlpv-wattenmeer.niedersachsen.de>
Web: https://www.<https://www./>nationalpark-wattenmeer.de<http://nationalpark-wattenmeer.de>
[cid:image001.png at 01D8E4A4.399CB390]

_______________________________________________
gdal-dev mailing list
gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/gdal-dev
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221020/0f617a06/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 6534 bytes
Desc: image001.png
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221020/0f617a06/attachment-0001.png>