Exporting to Microsoft SQL Server

SIS Desktop can 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

Cadcorp SIS Desktop 9.1 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 Cadcorp SIS Desktop 9.1 overlay can be exported to Microsoft SQL Server.

Cadcorp SIS Desktop 9.1 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 Cadcorp SIS Desktop 9.1

    Corresponding column in Microsoft SQL Server

    Text

    text

    Float

    float

    Integer

    int

    Geometry

    GEOMETRY

    Date

    datetime


  3. 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:

  • names should be less than 31 characters
  • names should start with an alphabetic character
  • should not contain reserved words such as: "select" or contain spaces or certain characters

Note: SIS only supports the default naming convention. SIS Desktop 9.1 does not support tables created using non default conventions. For example SIS Desktop 9.1 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:

Simple Features Compliant

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

Existing Table Overwritten

If Export to Server 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 Desktop 9.1 from being able to style (colour) the data to match the original data. SIS Desktop 9.1 will display all the data in a single default colour.