MSSQLSpatial Driver fails to bulk insert (n)varchar columns

[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.

[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.

[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

[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.

[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.

[tamas]

fixed in trunk (r39755)

[tamas]

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

[Even Rouault]

This ticket has been automatically closed because Trac is no longer used for GDAL bug tracking, since the project has migrated to GitHub. If you believe this ticket is still valid, you may file it to ​https://github.com/OSGeo/gdal/issues if it is not already reported there.