Error importing shape file

Topics: Developer Forum, User Forum
Feb 16, 2008 at 12:47 PM
Hi, when i try to import a shape file i have this error

c:\Fuentes\GIS\MsSqlSpatial>msscmd -server=(local)\sqlexpress -db=LogicCM -table
=ARPtoArgentinaCM -import=shp -shpfilename="\Mapas\ARGENTINA\mapas y sistema
s\Club Mapfre\Ruteros\Argentina\arg_puntos2.shp"

Unhandled Exception: System.Data.SqlClient.SqlException: Incorrect syntax near t
he keyword 'UNION'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm
dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds
ParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName,
Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult res
ult, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at MsSqlSpatial.GeometryTable.ExecuteCreate(SqlCommand command, String schema
Name, String tableName, String columnName, String createColumns)
at StoredProcedures.ImportFromDatasource(IProvider datasource, String schemaN
ame, String tableName, String columnName, Int32 srid, String type, String connec
tionString, ImportFromDatasourceProcess process, Int32& importedRows, TimeSpan&
elapsedTime)
at StoredProcedures.ImportFromShapefileMain(String shapefile, String schema_n
ame, String tablename, String columnname, Int32 srid, String type, String conn
ectionString, Int32& importedRows, TimeSpan& elapsedTime)
at MsSqlSpatialConsole.Program.ImportShp(ArgParser arg, String schema, String
table, String column, Int32 srid, String type, String connectionString)
at MsSqlSpatialConsole.Program.Main(String[] args)

i try to do this from sql server stored procedure but i have this error and i cannnot resolve
Msg 6522, Level 16, State 1, Procedure ImportFromShapefile, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "ImportFromShapefile":
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)
at SharpMap.Data.Providers.ShapeFile.ParseHeader()
at SharpMap.Data.Providers.ShapeFile..ctor(String filename, Boolean fileBasedIndex)
at StoredProcedures.ImportFromShapefileMain(String shapefile, String schemaname, String tablename, String column_name, Int32 srid, String type, String connectionString, Int32& importedRows, TimeSpan& elapsedTime)
at StoredProcedures.ImportFromShapefile(SqlString shapefile, SqlString schemaname, SqlString tablename, SqlString column_name, SqlInt32 srid, SqlString type)

please, some help?
thanks
Apr 4, 2008 at 1:26 PM
I have seen this in several of our files. Thusfar, in our case this has come down to there being SQL reserved words (there are many so it may not say Union in all cases, ours were 'Key') in the attribute titles of the shape file (*.dbf file). From a technical standpoint it looks like the mssqlspatial code needs to be quoting idenifiers or wrapping column names in square brackets []. I have not had time (deadlines) to dig into the mssqlcode code from importing shapefiles to verify this or even verify the data access technology being used to bridge the shapefiles to the SQL tables.

As a workaround you can open up the dbf files with an editor capable of modifying their schema / structure and rename the column(s) in question. In our case I prefixed them with an underscore '_' and that resolved the issue. Hope this helps.

Jon