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

Russ Sivak ruslan.sivak at reis.com
Fri Jun 16 07:06:57 PDT 2017


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.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20170616/c21847b7/attachment.html>


More information about the gdal-dev mailing list