Opened 7 years ago
Last modified 5 years ago
#6930 closed defect
MSSQLSpatial Driver fails to bulk insert (n)varchar columns — at Initial Version
| Reported by: | rsivak | Owned by: | warmerdam |
|---|---|---|---|
| Priority: | high | Milestone: | closed_because_of_github_migration |
| Component: | OGR_SF | Version: | unspecified |
| Severity: | normal | Keywords: | ogr2ogr sqlserver sql server bulk load bcp |
| Cc: | tamas |
Description
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.
