[gdal-dev] MSSQLSpatial Driver fails to bulk insert (n)varchar columns

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[gdal-dev] MSSQLSpatial Driver fails to bulk insert (n)varchar columns

Russ Sivak
We are trying to use ogr2ogr to load shapefiles into SQL Server. It
works when we are not using bulk copy, but once we use a windows build
with bulk copy, it's super fast, but inserts garbage characters into the
varchar/nvarchar fields. The numeric and geometry fields seem to be fine.

We are using the latest stable build from here:

http://gisinternals.com/release.php

We tried SQL Server 2014, 2016 and 2017 and none work.

This is what I'm using to load a test shape file into SQL 2014

ogr2ogr -f "MSSQLSpatial"
"MSSQL:SERVER=localhost\SQL2014;DATABASE=test;DRIVER={SQL Server Native
Client 11.0};trusted_connection=yes" C:\russ\cb_2016_us_county_500k -nln
test -progress -overwrite
ERROR 4: Unable to open EPSG support file gcs.csv.
Try setting the GDAL_DATA environment variable to point to the directory
containing EPSG csv files.
ERROR 6: No translation for an empty SRS to PROJ.4 format is known.
0...10...20...30...40...50...60...70...80...90...100 - done.


Also the error about GDAL_DATA persists even after setting

GDAL_DATA="C:\Program Files\GDAL\gdal-data"

The nvarchar fields seem to contain one junk character (which is
different every time you run the load). Also all the fields have the
same character (at least for this shape file). For the last run every
single nvarchar field is 'v'

Here is a sample row.

ogr_fid ogr_geometry statefp countyfp countyns affgeoid
geoid name lsad aland awater
1
0x0000000001043F00000064C746205E1A57C000DF6DDE38C14440EC1681B1BE1657C0C34483143CC14440F92EA52E191657C0FE428F183DC14440663046240A1357C0F6E978CC40C144402FBDFDB9681257C0E44A3D0B42C1444040A374E95F0E57C04E999B6F44C14440CB811E6ADB0C57C058AB764D48C14440E8BB5B59A20B57C0CFDBD8EC48C1444062F54718060A57C0D8EDB3CA4CC1444039419B1C3E0557C0FA27B85851C1444011548D5E0D0557C0AC8A709351C14440CC069964E40457C05930F14751C14440F6CE68AB920457C0D5E940D653C14440F69507E9290457C0E063B0E254C144406C0377A04E0357C08CD5E6FF55C1444030F0DC7BB80257C033BE2F2E55C144404B749659840257C0B398D87C5CC14440802A6EDC620257C02C99637957C144400567F0F78BFC56C07BBB253960C144409F53909F8DFC56C033BF9A0304BB444028D53E1D8FFC56C05ED4EE5701BA4440307F85CC95FC56C084622B685AB6444055D7A19A92FC56C0A19E3E027FB4444013807F4A95FC56C08F32E202D0B044402A8E03AF96FC56C027F911BF62AF4440AD6C1FF296FC56C063096B63ECAE4440D05CA79196FC56C037A79201A0AE4440B229577897FC56C07D21E4BCFFAD44402F17F19D98FC56C00FD253E410AD4440041C42959AFC56C04FE449D235AB44409C2FF65E7CFC56C00C569C6A2DA044403A2009FB76FC56C0C556D0B4C49E4440FF0758AB76FC56C03F1878EE3D9E44407C5D86FF74FC56C0BDE13E726B9C4440F2423A3C84FC56C02C2CB81FF0944440368FC360FEFC56C09B374E0AF3944440DAAB8F87BEFD56C04C327216F69444408FE21C75740157C0C1A90F24EF944440342A70B20D0457C0A3737E8AE39444405708ABB1840B57C0A626C11BD29444406B7D91D0960E57C08AA9F413CE944440A833F790F01257C077853E58C6944440F6949C137B1457C0300DC347C494444026C5C727641557C002F1BA7EC19444405C52B5DD041A57C05F402FDCB99444405BAFE941411A57C0A6ECF483BA944440253ACB2C421A57C09291B3B0A795444086C43D963E1A57C0AE0B3F389F964440378B170B431A57C0F3B0506B9A9744407E37DDB2431A57C0F677B6476F98444090A2CEDC431A57C059349D9D0C9A4440F3FFAA23471A57C0630B410E4A9A44402A8F6E84451A57C079CC4065FC9F4440ECC039234A1A57C013984EEB36A24440FD1186014B1A57C067B62BF4C1A24440F14927124C1A57C0B854A52DAEA344408FA50F5D501A57C04AB3791C06AB44403526C45C521A57C0BD378600E0AC44408E3D7B2E531A57C0BD361B2B31AF444009C38025571A57C0C804FC1A49B444405BE9B5D9581A57C02BA391CF2BB644402F6CCD565E1A57C064AE0CAA0DBE444064C746205E1A57C000DF6DDE38C1444001000000020000000001000000FFFFFFFF0000000003
v v v v v v v 1500067253 1929323


The file I'm currently trying to load is
http://www2.census.gov/geo/tiger/GENZ2016/shp/cb_2016_us_county_500k.zip
(unzipped obviously), but it fails with every shapefile I've tried.


Disclaimer

The information contained in this communication from the sender is confidential. It is intended solely for use by the recipient and others authorized to receive it. If you are not the recipient, you are hereby notified that any disclosure, copying, distribution or taking action in relation of the contents of this information is strictly prohibited and may be unlawful.


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Loading...