-1

I can successfully get a connection to my remote sql server database using:

ogrinfo "MSSQL:server=my_server_name;database=my_database;Trusted_Connection=True;"

But when I try to import an esri shape file (which works fine in QGIS) using the following command I found at various sites:

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=my_server;database=my_database;Trusted_Connection=True" "NZ_RoadLink.shp" -a_srs "EPSG:27700"

I just get the following error. Given none of the examples say you have to create tables first I presumed it would do this, especially given there might be lots of columns to create. I have owner permissions.

I've also tried using '-nln mytable' and get teh same error of invalid object name 'dbo.mytable'

ERROR 1: INSERT command for new feature failed. [S0002][Microsoft][ODBC SQL Server Driver][SQL 
Server]Invalid object name 'dbo.nz_roadlink'.(208)
ERROR 1: Unable to write feature 0 from layer NZ_RoadLink.
ERROR 1: Terminating translation prematurely after failed
translation of layer NZ_RoadLink (use -skipfailures to skip errors)

Thanks.

The full debug is:

GDAL: Auto register C:\OSGEO4~1\bin\gdalplugins\gdal_ECW_JP2ECW.dll using GDALRegister_ECW_JP2ECW.
GDAL: Auto register C:\OSGEO4~1\bin\gdalplugins\gdal_GEOR.dll using GDALRegister_GEOR.
GDAL: Auto register C:\OSGEO4~1\bin\gdalplugins\gdal_HDF5.dll using GDALRegister_HDF5.
GDAL: Auto register C:\OSGEO4~1\bin\gdalplugins\gdal_MG4Lidar.dll using GDALRegister_MG4Lidar.
GDAL: Auto register C:\OSGEO4~1\bin\gdalplugins\gdal_MrSID.dll using GDALRegister_MrSID.
GDAL: Auto register C:\OSGEO4~1\bin\gdalplugins\ogr_OCI.dll using RegisterOGROCI.
GDAL: Auto register C:\OSGEO4~1\bin\gdalplugins\ogr_SOSI.dll using RegisterOGRSOSI.
Shape: DBF Codepage = LDID/3 for NZ_RoadLink.shp
Shape: Treating as encoding 'CP1252'.
GDAL: GDALOpen(NZ_RoadLink.shp, this=000002464A4B8FE0) succeeds as ESRI Shapefile.
MSSQLSpatial: Use COPY/BCP: 0
ODBC: SQLDriverConnect(DRIVER={SQL Server};server=my_server;database=my_database;Trusted_Connection=True)
ODBC: SQLDisconnect()
GDAL: QuietDelete(MSSQL:server=my_server;database=my_database;Trusted_Connection=True) invoking Delete()
MSSQLSpatial: Use COPY/BCP: 0
ODBC: SQLDriverConnect(DRIVER={SQL Server};server=my_server;database=my_database;Trusted_Connection=True)
GDAL: GDALOpen(MSSQL:server=my_server;database=my_database;Trusted_Connection=True, this=000002464A4C2040) succeeds as MSSQLSpatial.
ODBC: SQLDisconnect()
GDAL: GDALClose(MSSQL:server=my_server;database=my_database;Trusted_Connection=True, this=000002464A4C2040)
GDAL: GDALDriver::Create(MSSQLSpatial,MSSQL:server=my_server;database=my_database;Trusted_Connection=True,0,0,0,Unknown,0000000000000000)
MSSQLSpatial: Use COPY/BCP: 0
ODBC: SQLDriverConnect(DRIVER={SQL Server};server=my_server;database=my_database;Trusted_Connection=True)
OGR_MSSQLSpatial: Using column ogr_fid as FID for table neilshape.
ERROR 1: INSERT command for new feature failed. [S0002][Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.neilshape'.(208)
ERROR 1: Unable to write feature 0 from layer NZ_RoadLink.
ERROR 1: Terminating translation prematurely after failed
translation of layer NZ_RoadLink (use -skipfailures to skip errors)
ODBC: SQLDisconnect()
GDAL: GDALClose(MSSQL:server=my_server;database=my_database;Trusted_Connection=True, this=000002464A4C2040)
Shape: 1 features read on layer 'NZ_RoadLink'.
GDAL: GDALClose(NZ_RoadLink.shp, this=000002464A4B8FE0)
GDAL: In GDALDestroy - unloading GDAL shared library.
10
  • You have the source and destination reversed, I think. – wingnut Apr 27 at 15:45
  • That's the format every site says and I have no destination set, just a_srs which is the epsg code of the data. I presume it is simply wanting to create a table with the same name as the shape/layer it also found. Or am I missing something from your answer? – Neil Apr 27 at 15:49
  • is by any chance 'dbo.nz_roadlink' Invalid object name? I have no idea what SQL server allows or doesn't allow. Failing that try creating the table – Ian Turton Apr 27 at 15:55
  • I've tried using the parameter to specify the table name and get the same error. All sources, e.g. alastaira.wordpress.com/ogr2ogr-patterns-for-sql-server, point to my command being right and ogr2ogr will create the table. Maybe it's a fault nothing to do with this but some other error not listed but not reported properly... – Neil Apr 27 at 15:59
  • @Neil my bad. I thought you were downloading. Is the translation error related to the -a_srs definition? 27700 seems to be in the UK. The source file name seems further, er, south. 27200 is in NZ. – wingnut Apr 27 at 16:05
0

I got it to work by adding:

-lco UPLOAD_GEOM_FORMAT=wkt

So the invalid object name error is complete nonsense.

However, after 40 minutes of a small 30mb shape file that is read in about 5 seconds in QGIS it was still on 1% progress because it refuses to use BCP or the native sql drivers that every other app uses. Adding this comment in case it helps anyone else.

2
  • If you want to debug you should capture the SQL commands that GDAL executes. Probably you must do that from the MSSQL side. – user30184 Apr 27 at 19:54
  • 1
    I found the problems using debug, thanks. I dunno why it needed wkt adding as other colleagues don't need it on same file. Also, it refuses to use any driver I specify despite every other app using native or odbc drivers fine, and so BCP is disabled. I had to give up in the end and use somebody elses computer with the same command.. – Neil Apr 28 at 16:05

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.