Microsoft SQL Server

The Microsoft SQL Server Data Store is available for Reading and Writing spatial and non-spatial data in 1Integrate.

Read and Write Support

Read	Write
Read	Commit	Copy To

Data Support

Data	Support	Notes
Non-Spatial
2D
Measured
Heighted
3D		For more information about 3D data in 1Integrate, please refer to Full 3D Support.

Key Information

Supports Azure Active Directory authentication.
Supports Type 2 integrated authentication when 1Integrate is running on Windows operating systems.
This Data Store uses the SQL Server JDBC driver to connect to the database.
The Data Store will not create missing database objects such as tables and views. Ensure these are created before trying to write data.

Parameters

Read and Commit

Parameter	Description
URL	The full JDBC connection string for connecting to the SQL Server database. Example: Connection without Windows Authentication: `jdbc:sqlserver://<host>:<port>;databaseName=<database>` Connection with Windows Authentication: `jdbc:sqlserver://<host><port>;integratedSecruity=true; databaseName=<database>`
Username	The username for the SQL Server database.
Password	The password for the SQL Server database.
Sample Size for Dimension Query	Determines the number of records to check when retrieving the number of dimensions of geometries in a table.
Sample Size for SRID Query	Determines the number of records to check when retrieving the SRID of geometries in a table. The SRID that is used the most will be retrieved.
Coordinate Reference System	This parameter can be used to specify a Coordinate Reference System (CRS) code of the input data. The input format will be `SRID=######` for Spatial Reference Identifiers (SRID) and `EPSG=######` for EPSG codes. See Coordinate Reference System for more information on this parameter, and when it may be required. This will not re-project the data, but allows its current CRS to be defined (e.g to enable multiple data stores to open where one does not have a CRS defined). Note: When you override the CRS you must refresh the Data Store's schema before it will take effect. Note: Coordinate Reference System parameters are required in a session that will write data to Oracle.
Allow invalid geometries	Allows features with invalid geometries (e.g. duplicate vertices, self-intersections) to be imported. This is necessary if they need to be imported in order to be validated or corrected. If not ticked then features with invalid geometries are not imported and are logged as feature errors in the open data task.
Fix ring direction	Polygon rings are assumed to be encoded using the right-hand rule, i.e. outer rings are anti-clockwise and inner rings are clockwise. If the service does not guarantee that encoding, then set this flag to have polygon rings automatically oriented on import to avoid later problems when processing the data. Note: This requires a small amount of additional processing so only set if the ring direction is not guaranteed.
For X,Y,Z,M	If you have data that is recorded as X,Y,Z,M, you must opt to ignore either Z or M values by choosing to use X, Y and M or X, Y and Z only. Note: If the default is left on and X,Y,Z,M is uploaded then an exception will be thrown as 1Integrate only supports X,Y,M or X,Y,Z. When consuming data with 3 ordinates, you must also select one of the two options: Select X,Y,Z to read in heighted data. Select X,Y,M to read in measured data.
Force geometry to be written X,Y,Z,M	Select to output a fourth z or m ordinate for objects. Only takes effect if the geometry to be written has 3 ordinates.
For X, Y, Z, M data: Use zero rather than Null or NaN when writing ignored ordinate	Select to write ignored values as zero in place of null/NaN (NaN is used for the PostGIS data store).

Parameter	Description
URL	The full JDBC connection string for connecting to the SQL Server database. Example: Connection without Windows Authentication: `jdbc:sqlserver://<host>:<port>;databaseName=<database>` Connection with Windows Authentication: `jdbc:sqlserver://<host><port>;integratedSecruity=true; databaseName=<database>`
Username	The username for the SQL Server database.
Password	The password for the SQL Server database.
Force geometry to be written X,Y,Z,M	Select to output a fourth z or m ordinate for objects. Only takes effect if the geometry to be written has 3 ordinates.
For X, Y, Z, M data: Use zero rather than Null or NaN when writing ignored ordinate	Select to write ignored values as zero in place of null/NaN (NaN is used for the PostGIS data store).

1Integrate v 5.1

Copyright © 2025 1Spatial