Shape2SQL is an excellent little utility that can be used to load spatial data from shapefiles into SQL Server 2008. Considering that the shapefile format is pretty much ubiquitous throughout the spatial world, and SQL Server doesn’t provide any other mechanisms to load from this format (despite calls for it to be added), it’s pretty much on the toolset of any developer starting to use SQL Server spatial who doesn’t want to splash out on commercial alternatives such as Safe FME or Manifold.
The Shape2SQL front-end GUI is pretty self-explanatory, and looks like this:
You can also call Shape2SQL programmatically from the command line, although I’ve seen a couple of posts on the internet from people having problems with the syntax to do this. For what it’s worth, here are the command line arguments I use to load Ordnance Survey Open Data shapefiles from cmd:
shape2sql.exe -shp=”C:\TQ00\Settlement_Area.shp” -connstr=”Data Source=zangief\sqlexpress;Initial Catalog=OSVectorMap;Integrated Security=SSPI;” -table=”TQ00_Settlement_Area” -srid=”27700″ -oidname=”ID” -geomname=”geom27700″ -append=”true” -geography=false
I will admit, however, to having had problems calling Shape2SQL from Powershell. The problems seem to come from the semicolon character ; in the SQL connection string, which PoSH seems to treat as a statement-ending character, even when it is preceded by the escape character `. There’s also possibly issues with it not liking parameters containing “” marks (which I include in case the filepath contains a space).
I don’t know what the solution to this is yet (perhaps to ask somebody who knows more about PoSH than me, or else just continue to use good ol’ cmd.exe…)
Alastair Aitchison 
Pingback: Exporting Spatial Data From SQL Server to ESRI Shapefile | Alastair Aitchison
I was able to create a Powershell script that executes Shape2Sql, and then wait for the shape file to finish importing before starting the next one. Worked great!
CLEAR
$files = Get-ChildItem ‘C:\Temp\Shape2SQL\TIGER Data\Shapes’ -filter tl_2012_08*_edges.shp
“Processing ” + $files.Count + ” files”
$connstr = ‘-connstr=”Data Source=10.0.5.202;Initial Catalog=GeoCoder;Integrated Security=TRUE”‘
foreach ($file in $files)
{
$filename = $file.FullName
$filename
$event = ‘C:\Temp\Shape2SQL\Shape2Sql.exe’
$shp = ‘-shp=”‘ + $filename + ‘”‘
$table = ‘-table=”TIGER_Data”‘
$srid = ‘-srid=”4326″‘
$oidname = ‘-oidname=”ID”‘
$geoname = ‘-geomname=”geom”‘
$append = “-append=true”
$geography = ‘-geography=true’
$allArgs = @($shp + ” ” + $connstr + ” ” + $table + ” ” + $srid + ” ” + $oidname + ” ” + $geomname + ” ” + $append + ” ” + $geography)
####
# Uncomment the Write-Host line to see the generated command line for Shape2Sql
####
#Write-Host $event $shp $connstr $table $srid $oidname $geomname $append $geography
$p = Start-Process $event -ArgumentList $allArgs -NoNewWindow -Wait -PassThru