<div dir="ltr"><div dir="ltr"><div>Thanks, this is Windows 10. I turned debug on and noticed it said BCP 0. BCP is available and I use it so not sure why it says 0?</div><div><br></div><div>However, I will test using that flag to see if it works or enables it. Like I said, the other odd thing is it told me to try one of three drivers (odbc, sql server, sql server native client). I have and use all 3 but when I tried to use native client ogr2ogr failed to run with an error.<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, 28 Apr 2021 at 08:17, Hector muro <<a href="mailto:muro.hector@gmail.com">muro.hector@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto"><div>Hi Neil,<div dir="auto"><br></div><div dir="auto">I found a similar situation once. Are you using Linux? If so, I think the ogr2ogr MSSQLSpatial driver for Linux does not use BCP (for multiple inserts, which is why the CSV goes so fast).</div><div dir="auto"><br></div><div dir="auto">If not, have you tried using the MSSQLSpatial Configuration Options for the command? I don't remember exactly what they are but it's well documented. It was something on the lines of "MSSQLSPATIAL_USE_BCP" and "MSSQLSPATIAL_BCP_SIZE"</div><div dir="auto"><br></div><div dir="auto">Another thing I found useful was to check the SSMS Profiler to see what's going on as you run.</div><div dir="auto"><br></div><div dir="auto">Finally, I was forced by circumstances to use SQL Server, but if that's not your case, I strongly recommend you to get away from it :)</div><div dir="auto"><br></div><div dir="auto">Hope this helps,</div><div dir="auto"><br></div><div dir="auto">Hector</div><br><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, 27 Apr 2021, 19:47 Neil Walker, <<a href="mailto:neil.walker.1973@gmail.com" target="_blank">neil.walker.1973@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><p class="MsoNormal">Hello,</p><p class="MsoNormal">First time user, I hope this is the right mailing list :)<br></p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">I have a fairly small (37mb - 160,000 linestrings) shp
file and it loads into QGIS in a couple of seconds. I want to import
this into sql server, so used the following ogr2ogr command:</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">ogr2ogr -f "MSSQLSpatial" "MSSQL:server=myserver;database=mydatabase;Trusted_Connection=True;" "NZ_RoadLink.shp" -a_srs "EPSG:27700" -nln "neilshape" -progress -lco UPLOAD_GEOM_FORMAT=wkt</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">It's UK Ordnance Survey open roads data if anyone is interested. After 40 minutes it got to 3% complete then I gave
up. <br></p><p class="MsoNormal"><br></p><p class="MsoNormal">So I instead saved it as a CSV and it took about 1 minute, I then imported
this CSV into sql server and that took 1 minute too.</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">I then ran the following command and it worked ok too after a few seconds, I don't know if it's right or not but looks ok...</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">alter table add geom GEOMETRY</p>
<p class="MsoNormal">update table set geom=replace(wkt,'Z','')</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">So, can anyone let me know what could be causing
this massive slowdown because my database connection is fast and there are no network
issues. I have tried using all the available sql server drivers (SQL Server, default ODBC) but it's
still this slow. I actually also have 'sql server native 11.0' but ogr2ogr refused to use this.<br></p><p class="MsoNormal"><br></p><p class="MsoNormal">If nobody has a clue can you confirm if my removal of 'Z' in this command is ok or what a better way is?</p><p class="MsoNormal"><br></p><p class="MsoNormal">Also, I don't know if it's just the way it is but I kept getting 'invalid database object mydatabase' until I entered the UPLOAD_GEOM_FORMAT=wkt parameter. Clearly 'mydatabase' has nothing to do with the error...</p><p class="MsoNormal"><br></p><p class="MsoNormal">Thanks.</p><p class="MsoNormal"><br></p></div>
_______________________________________________<br>
gdal-dev mailing list<br>
<a href="mailto:gdal-dev@lists.osgeo.org" rel="noreferrer" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
</blockquote></div></div></div>
</blockquote></div></div>