MSSQLSpatial - Microsoft SQL Server Spatial Database

This driver implements support for access to spatial tables in Microsoft SQL Server 2008+ which contains the geometry and geography data types to repesent the geometry columns.

Connecting to a database

To connect to a MSSQL datasource, use a connection string specifying the database name, with additional parameters as necessary. The connection strings must be prefixed with 'MSSQL:'.
MSSQL:server=.\MSSQLSERVER2008;database=dbname;trusted_connection=yes
In addition to the standard parameters of the ODBC driver connection string format the following custom parameters can also be used in the following syntax:

The parameter names are not case sensitive in the connection strings.

Specifying the Database parameter is required by the driver in order to select the proper database.

The connection may contain the optional Driver parameter if a custom SQL server driver should be loaded (like FreeTDS). The default is {SQL Server}

Layers

Starting with GDAL 1.11 if the user defines the environment variable MSSQLSPATIAL_LIST_ALL_TABLES=YES (and does not specify Tables= in the connection string), all regular user tables will be treated as layers. This option is useful if you want tables with with no spatial data

By default the MSSQL driver will only look for layers that are registered in the geometry_columns metadata table. Starting with GDAL 1.10 if the user defines the environment variable MSSQLSPATIAL_USE_GEOMETRY_COLUMNS=NO then the driver will look for all user spatial tables found in the system catalog

SQL statements

The MS SQL Spatial driver passes SQL statements directly to MS SQL by default, rather than evaluating them internally when using the ExecuteSQL() call on the OGRDataSource, or the -sql command option to ogr2ogr. Attribute query expressions are also passed directly through to MSSQL. It's also possible to request the OGR MSSQL driver to handle SQL commands with the OGR SQL engine, by passing "OGRSQL" string to the ExecuteSQL() method, as the name of the SQL dialect.

The MSSQL driver in OGR supports the OGRLayer::StartTrasaction(), OGRLayer::CommitTransaction() and OGRLayer::RollbackTransaction() calls in the normal SQL sense.

Creation Issues

This driver doesn't support creating new databases, you might want to use the Microsoft SQL Server Client Tools for this purpose, but it does allow creation of new layers within an existing database.

Layer Creation Options

Spatial Index Creation

By default the MS SQL Spatial driver doesn't add spatial indexes to the tables during the layer creation. However you should create a spatial index by using the following sql option:

create spatial index on schema.table

The spatial index can also be dropped by using the following syntax:

drop spatial index on schema.table

Transaction support (GDAL >= 2.0)

The driver implements transactions at the dataset level, per RFC 54

Examples

Creating a layer from an OGR data source

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=.\MSSQLSERVER2008;database=geodb;trusted_connection=yes" "rivers.tab"

Connecting to a layer and dump the contents

ogrinfo -al "MSSQL:server=.\MSSQLSERVER2008;database=geodb;tables=rivers;trusted_connection=yes"

Creating a spatial index

ogrinfo -sql "create spatial index on rivers" "MSSQL:server=.\MSSQLSERVER2008;database=geodb;trusted_connection=yes"