GDAL SQL Syntax Error "Unexpected End of String"

Question

I am trying to convert from SQL to shapefile, from EPSG 28355 to 4283, however I am getting the error:

ERROR 1: SQL Expression Parsing Error: syntax error, unexpected '.', expecting end of string. Occurred around : ME_ABBR, GEOMETRY1_SPA FROM Internal.dbo.RD_CL_Roads_Register referring to the dot after dbo.

I can't see any odd characters. The script is

pushd \\blahblah\data\ogr2ogr

ogr2ogr -f "MSSQLSpatial" -s_srs "EPSG:28355" "MSSQL:server=blahblah;database=Internal;trusted_connection=yes" -t_srs "EPSG:4283" "\\blahblah\data\Grass_GIS_Projects\roads_reprojected.shp" -sql "SELECT ID,SPEED_ZONE,ROAD_NAME_ABBR, GEOMETRY1_SPA FROM Internal.dbo.RD_CL_Roads_Register" -lco "GEOM_TYPE=geometry" -lco "GEOM_NAME=GEOMETRY1_SPA"

popd \\gisdataserver\data\ogr2ogr

Is it incorrect ordering that's the problem?

• Ian Turton: Internal.dbo.RD_CL_Roads_Register looks odd - I would expect it to be dbo.RD_CL_Roads_Register
• Konan Pruiksma: What exactly is "Internal.dbo.RD_CL_Roads_Register"? Is it database.schema.table? You only need to refer to schema.table.
• user30184: Does your SELECT work in SQL console? If it works you can try to put the table name within double quotes. It requires escaping, this might work "...FROM \"Internal.dbo.RD_CL_Roads_Register\""
• Josh: Thanks. dbo.RD_CL_Roads_Register gives me the error ERROR 1: Unable to open secondary datasource dbo' required by JOIN.`. The select does work in SQL also.
• Josh: If I don't include the database or schema I get ERROR 1: SELECT from table RD_CL_Roads_Register failed, no such table/featureclass.. I have scripts that reproject from one SQL table to another that work fine. The difference here is I'm going from SQL to shp, so perhaps it's the syntax or ordering of operators?

Answer 1

This works

ogr2ogr --config GDAL_DRIVER_PATH \\blahblah\data\ogr2ogr\gdalplugins -f "ESRI Shapefile" "\\gisdataserver\data\Grass_GIS_Projects\Meals_Hastings_reproj.shp" -s_srs "EPSG:28355" "MSSQL:server=gisdbserver;database=Internal;trusted_connection=yes" -sql "SELECT * FROM Internal.dbo.vw_CL_Meals_Hastings_w_startpoint" -lco "GEOM_TYPE=geometry" -lco "GEOM_NAME=RoadPoint" -overwrite -t_srs "EPSG:4283"