Opened 4 months ago

Last modified 3 months ago

#6930 new defect

MSSQLSpatial Driver fails to bulk insert (n)varchar columns

Reported by: rsivak Owned by: tamas
Priority: high Milestone:
Component: OGR_SF Version: unspecified
Severity: normal Keywords: ogr2ogr sqlserver sql server bulk load bcp
Cc: tamas

Description (last modified by rsivak)

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.

Change History (9)

comment:1 Changed 4 months ago by rsivak

Description: modified (diff)

comment:2 Changed 4 months ago by Even Rouault

Cc: tamas added
Component: UtilitiesOGR_SF

comment:3 Changed 4 months ago by rsivak

It seems that this only happens on 64 bit builds, possibly because you are linking to the 32 bit SQL Server files (Program Files (x86)) in the build options.

The 32 bit build seems to work fine.

comment:4 Changed 4 months ago by tamas

I've tried that with the x64 installer and it was working fine. Have you installed the x64 version of the SQL Server native client package? https://www.microsoft.com/en-us/download/details.aspx?id=29065

comment:5 Changed 4 months ago by rsivak

I tried the x64 installer and it is not working fine. I've tried it on multiple computers and we were all able to see the issue.

Have you checked the data to make sure that it looks good?

I just tried it on a brand new server with just SQL 2016 Developer x64 installed on there (en_sql_server_2016_developer_x64_dvd_8777069.iso) and gdal-201-1800-x64-core.msi as well as gdal-201-1800-x64-filegdb.msi and I was seeing the same issue.

I tried installing the SQL Server native client package that you've linked to, but that is for 2012 and it says I have a newer version installed.

comment:6 Changed 4 months ago by carriaga

I have experienced the same problem. After loading the shape with ogr2ogr the columns in the table have the length of the original values in the shape but the content is "blank".

We where using the GisInternals? x64 binary (zipped) distribution (GDAL 2.1.3, released 2017/20/01, http://www.gisinternals.com/query.html?content=filelist&file=release-1800-x64-gdal-2-1-3-mapserver-7-0-4.zip).

As suggested in previous comments I have tried the equivalent x32 version (http://www.gisinternals.com/query.html?content=filelist&file=release-1800-gdal-2-1-3-mapserver-7-0-4.zip).

And it works fine!

But, would like to use de 64bit version... should I redirect this to GisInternals?? Note that in both cases pre-requistes are the same (SQL Native client installed) and same GDAL path, etc.

And as stated in the initial post, with numeric values the 64bit version works fine.

Last edited 4 months ago by carriaga (previous) (diff)

comment:7 Changed 3 months ago by tamas

Owner: changed from warmerdam to tamas

comment:8 Changed 3 months ago by tamas

fixed in trunk (r39755)

comment:9 Changed 3 months ago by tamas

Fixed in branch-2.2 (r39756) and branch-2.1 (r39757)

Note: See TracTickets for help on using tickets.