Exporting to Microsoft SQL Server

SIS is able to export data to a number of databases. This topic deals with exporting data to a Microsoft SQL Server database.

Loading data into Microsoft SQL Server

SIS provides the ability to export (load) data into a Microsoft SQL Server database using the Geometry type to store the spatial data. Spatial data contained in any SIS overlay can be exported to Microsoft SQL Server. SIS will export all the geometry features as well as any attributes contained in the overlay schema. The export utility can also create spatial indexes.

Cadcorp recommend that data to be exported to Microsoft SQL Server is first copied to an internal overlay so that the data and the schema columns can be adjusted to conform to the Microsoft SQL Server model.

Table Definitions

Spatial data is exported to Microsoft SQL Server in the following steps:

1. A table is created in the users chosen catalogue. All optional parameters are default values (your database administrator will know the default values for your system). For most users the default options are irrelevant.
   
   
2. Columns definitions default to the following Microsoft SQL Server column types: 
   
   

   Data in SIS	Corresponding column in Microsoft SQL Server
   Text	text
   Float	float
   Integer	int
   Geometry	GEOMETRY
   Date	datetime

   
   Note: When exporting text data to Oracle, PostGIS or Microsoft SQL Server spatial databases the following expression should be added to the schema of the overlay to be exported. The expression should be renamed text_height.

_ Point_ Height * 0.000352777778 * [dataset Scale]

where _ Point_ Height is a system property

dataset Scale is the dataset scale for the overlay being exported.

Requirements

Ensure that a primary key is a part of the table definition.

Naming Convention

Microsoft SQL Server tables and column names must conform to Microsoft's naming conventions. This includes:

Note: SIS only supports the default naming convention. SIS does not support tables created using non default conventions. For example SIS will not open tables that have the following names or columns within the table:

Names in lower case

Names with spaces etc.

See Microsoft SQL documentation for more information.

Changing the Name of a Table

Users should not change the name of a Microsoft SQL Server spatial table. Changing the name of a table containing spatial data causes the following issue:

• A SIS Workspace Definition (SWD) containing Microsoft SQL Server overlays will not be able to retrieve data from the table.

Simple Features Compliant

Microsoft SQL server supports data that conforms to the OpenGIS Simple Features Specification.

Existing Table Overwritten

If Export to Server [File-Export] is used to export a layer and the user specifies a table name of an existing table in the Microsoft SQL Server (whether spatial or not) the existing table will be replaced.

Feature Tables

When exporting data to Microsoft SQL Server ensure that the data has been appropriately feature coded before exporting. Failure to do so will prevent SIS from being able to style (colour) the data to match the original data. SIS will display all the data in a single default colour.  

Top of page

Send comments on this topic.

Click to return to www.cadcorp.com

© Copyright 2000-2017 Computer Aided Development Corporation Limited (Cadcorp).