2

I'm trying to export some data from a SQL Server table to a shapefile.

I have tested this on a 2014 SQL Sever and it works but when I try and run it on a 2012 SQL Server it creates a blank output.

The original that works SQL Server, 2014. Result is as expected with a shapefile created:

ogr2ogr -overwrite -f "ESRI Shapefile" "C:\temp\sqlexport_Works.shp" "MSSQL:server=[SERVER];database=[DB];trusted_connection=yes;GeometryFormat=native;" -sql "SELECT TOP 1000 [ID] ,GEOMETRY::STGeomFromWKB([geom].STAsBinary(),3857) As [geom] FROM [DB].[dbo].[RoadNetwork] WHERE [geom].STGeometryType() = 'MULTILINESTRING'" -lco "SHPT=ARC" -mapFieldType Binary=String

The alternative version, that does not work on SQL Server, 2012. Result is an empty shapefile with only a populated dbf file created:

ogr2ogr -overwrite -f "ESRI Shapefile" "C:\temp\sqlexport_DoesNotWork.shp" "MSSQL:server=[SERVER];database=[DB];trusted_connection=yes;GeometryFormat=native" -sql "SELECT TOP 1000 [ID] ,GEOMETRY::STGeomFromWKB([geom].STAsBinary(),3857) As [geom] FROM [DB].[dbo].[RoadNetwork] WHERE [geom].STGeometryType() = 'MULTILINESTRING'" -lco "SHPT=ARC" -mapFieldType Binary=String

Can anybody explain how to get this working on SQL Server 2012?

  • Compare what you get with ogrinfo command ogrinfo [your connection and sql] --debug on. – user30184 Nov 17 '16 at 7:05
  • Ok thanks for this, there is a difference. The 2012 server doesn't have a block with the coordinate system information in it. How do I track down if it's a SQL Server issue of OGR install issue? – user19466 Nov 18 '16 at 0:18
  • Ogrinfo acts here only as a client that makes a SQL query. I am quite sure that there is some difference in databases. – user30184 Nov 18 '16 at 5:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.