ArcToSQL2008 – An ArcCatalog Command to export FeatureClasses to MS SQL SERVER 2008
I had written this tool 3-4 months ago and it had been sitting on my machine waiting to be wrapped with an installer project. I finally got around to building a setup and deployment project for it and it is ready to distribute. The tool can be downloaded below
Once installed, you should be able to open the “Tools -> Customize” dialog in ArcCatalog
Under the “GeoSpatial.NET” Categories, the “ArcToSQL2008″ command should be available. Drag the command to any of the toolbars.
Select the FeatureClass you want to export to the SQL Server 2008 database and click the “ArcToSQL2008″ button on the toolbar. The dialog below should pop-up. Enter the name of the SQL Server 2008 database server and click connect. The Database drop-down will get populated with all available databases on the server. Select a database from the list and enter the name of the table that you want the FeatureClass exported to. Click OK. The FeatureClass will get exported and a message box should pop-up when the import is completed.
Some highlights of the tool:
-
Exports FeatureClasses in a Projected Co-ordinate System as SQLGeometry in SQL 2008.
-
Exports FeatureClasses in a Geographic Co-ordinate System as SQLGeography in SQL 2008.
-
Any errors that occur during the export are logged in a log file found under in the install directory.
-
The tool is built using a broader GIS framework that should enable developers to customize the export process to their liking. I still have a little bit of house cleaning on the code for the broader GIS framework. I will post when it is all done. Hopefully that will be real soon. Contact me if you want to look at it sooner…
Things left to do:
-
Better UI showing the progress of the export operation.
-
More flexibility to the export operation by allowing the user to select the fields to be exported.
-
The export operation can be made faster by using the geometry builder classes to construct the SQLGeometry and SQLGeography objects. I took the shortcut for now and just parsed out the geometries from WKT strings.
Please let me know if you find any bugs in the tool using the “contact me” link on the right pane of this blog.
Subscribe
Follow Me
Contact me
[...] way to move datasets from ArcGIS Desktop to Microsoft SQL Server 2008, Vish has you covered. ArcToSQL2008 is an ArcCatalog command to export FeatureClasses to SQL Server 2008. Much like Fletch deams of [...]
[...] Vish’s ramblings No TweetBacks yet. (Be the first to Tweet this [...]
Creates the table but there is no data in it
Hi Jason -
I am using ArctoSQLServer2008 and noticed you were havingteh same problem – it is creating the table without the data. Did you find anything that worked?
TuParks – Please check the log file that should have been created in the installation directory. That should tell the reasons why the features where not imported. Most probably, it should be problems with the geometry.
Thank You,
Vish
How does this play with ArcSDE? If I add features/tables through the backend, I assume since they will not be registered with ArcSDE that they wont be viewable through the Database Connections option in ArcCatalog. I plan to play around with this tonight but thought you would know the answer straight away.
Thank you,
Tom
Hi Tom,
Yes, you are right. This tool does not register the sql geometry/geography table with the SDE.
Thank You,
Vish
Just a tool I’m looking for!
Unfortunately I didn’t get it to work – it says “Please select a FeatureClass to export to SQL 2008″.
Assuming the featureclass should be the selected item in the ArcCatalog browser tree.
I tried to select both a shapefile, PGDB-fclass, ArcSDE fclass (inside and outside feature dataset).
Same error occurs. The logfile in the install directory does not say anything.
Using ArcGIS 9.3, Windows XP (Swedish)
Any ideas?
\Ingemar
Hi Ingemar,
The FeatureClass must be selected on the right pane in ArcCatalog (in the contents tab and not in the tree in the left pane). Also the tool currently only supports exporting one FeatureClass at a time.
Thank You,
Vish
Thanks Viswaug!
Now it works perfekt!
I should have tried that one
Thanks!
\Ingemar
Can the tool be scripted to run and export many specified feature classes?
Hi Cannonwt,
Yes, it is possible with a little bit of effort.
Thank You,
Vish
here is the log file message I am getting. The geometry does not appear to be corrupt.
2009-04-24 11:37:23,216 ERROR – Incorrect syntax near ‘.’.
Must declare the scalar variable “@SHAPE”. Feature ID:- 16
System.Data.SqlClient.SqlException: Incorrect syntax near ‘.’.
Must declare the scalar variable “@SHAPE”.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at GIS.Framework.SQL2008.SQLGeometry.Layers.SQL2008GeomTableLayer.Add(IGISFeature feature)
at ArcToSQL2008.ArcToSQL2008.Import(String connectionString, String tableName)
The table is created in SQL2k8, no data is transfered into the table and the logfile is empty. I’ve tried this with several different feature classes, all had the same results.
This would be a great tool!
Thanks
Davin