- Install Microsof repositories
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/debian/9/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt update
ACCEPT_EULA=Y apt-get install -y mssql-cli unixodbc-dev mssql-tools msodbcsql17 odbcinst libqt4-sql-tds libqt4-sql-odbc unixodbc-dev iodbc freetds-bin libdbd-freetds freetds-dev freetds-common tdsodbc- Test connection to your database with mssql-cli tool
# https://github.com/dbcli/mssql-cli
# https://www.mytecbits.com/microsoft/sql-server/mssql-cli
mssql-cli -S DB_HOST -U DB_USER -P DB_PASSWORD -d DB_DB- Modify ODBC ini file and add sections as described below. It seems the "SQL Server" section is needed for ogr2ogr, and the "FreeTDS" section is needed for QGIS MSSQL native provider
nano /etc/odbcinst.ini # add at the end :
[SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
UsageCount=1
Trace=Yes
TraceFile=/tmp/mssql.log
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
UsageCount=1
[FreeTDS]
Description = tdsodbc
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout = 5
CPReuse = 5- You can also edit the /etc/odbc.ini file
[ODBC Data Sources]
testsqlserver = ODBC Driver 17 for SQL Server
[testsqlserver]
Driver = ODBC Driver 17 for SQL Server
Description = Test SQL Server
Server = mssql
- After that, you may need to add symbolic links so that QT can use the files
ln -s /etc/odbcinst.ini /usr/local/etc/odbcinst.ini
ln -s /etc/odbc.ini /usr/local/etc/odbc.ini- Add path to mssql tools in you path ( see https://github.com/qgis/QGIS/blob/master/.ci/travis/linux/docker-build-test.sh#L128 )
echo 'PATH="$PATH:/opt/mssql-tools/bin"' > /etc/profile.d/mssql-path.shAt present, I think you can only use ogr2ogr to import your data. See for example https://nathanw.net/2011/06/07/opening-ms-sql-server-2008-spatial-tables-in-qgis-correctly/
Using ogr2ogr will help creating and filling in the 2 needed tables geometry_columns and spatial_ref_sys
You can do it for example with this kink of command line:
# Import
ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:DB_HOST;database=DB_DB;uid=DB_USER;pwd=DB_PASSWORD;" test.shp
# Test imported data
ogrinfo -al "MSSQL:server=DB_HOST;database=DB_DB;uid=DB_USER;pwd=DB_PASSWORD;tables=test" -fid 1Sometime you need to set the Driver before running the ogr2ogr command
set driver="SQL Server"
ogrinfo -al "MSSQL:driver=SQL Server;server=DB_HOST;database=DB_DB;uid=DB_USER;pwd=DB_PASSWORD;tables=test" -fid 1Drag and drop of layers to MSSQL works via the QGIS browser.
Here are the recommandations to open data from MSSQL in QGIS
- Your table (or view) must have a unique identifier field INTEGER (not BIGINT)
- It seems your table or view must not have
nvarcharfields. Instead, usetextorvarchar - You should have spatial indexes for your geometries: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-spatial-index-transact-sql?view=sql-server-2017
- Optionnally : for performance, you should have a table
geometry_columnwhich lists the geometry tables or views, a tablespatial_ref_syswith your used projection data
Here are the information about the optionnal geometry_columns and spatial_ref_sys table to create and populate to allow better performances:
- geometry_columns:
CREATE TABLE [dbo].[geometry_columns](
[f_table_catalog] [varchar](50) NULL,
[f_table_schema] [varchar](50) NULL,
[f_table_name] [varchar](100) NULL,
[f_geometry_column] [varchar](50) NULL,
[coord_dimension] [int] NULL,
[srid] [int] NULL,
[geometry_type] [varchar](50) NULL
);with entries like:
f_table_catalog f_table_schema f_table_name f_geometry_column coord_dimension srid geometry_type
GISDB dbo Accuracy SP_GEOMETRY 1 4326 LineString
GISDB dbo AssetAreas SP_GEOMETRY 2 4326 PolygonYou can insert data manually with something like:
INSERT INTO "dbo"."geometry_columns" VALUES ('GISDB', 'dbo', 'AssetAreas', 'SP_GEOMETRY', 2, 4326, 'Polygon');- spatial_ref_sys
CREATE TABLE [dbo].[spatial_ref_sys](
[srid] [int] NOT NULL,
[auth_name] [varchar](256) NULL,
[auth_srid] [int] NULL,
[srtext] [varchar](2048) NULL,
[proj4text] [varchar](2048) NULL
) ON [PRIMARY];with entries like:
srid auth_name auth_srid srtext proj4text
94326 epsg 4326 +proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]Note that you can get these from spatialreference.org, like this: http://spatialreference.org/ref/epsg/4326/postgis/ just modify it for SQL Server syntax.
Install PostgreSQL foreign data wrapper ogr2ogr
apt install postgresql-10-ogr-fdw
See https://github.com/pramsey/pgsql-ogr-fdw for the best reference about ogr_fdw, the spatial swiss army knife for PostgreSQL !
Use it in your database ! If you have no spatial tables, but only geometryless tables to retrieve, specify the config_options with value MSSQLSPATIAL_USE_GEOMETRY_COLUMNS=NO.
-- Create extension
CREATE EXTENSION ogr_fdw;
-- Create server
CREATE SERVER IF NOT EXISTS fdw_mssql_myname
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'MSSQL:driver=SQL Server;server=DB_HOST;database=DB_DB;uid=DB_USER;pwd=DB_PASSWORD;tables=a_test,b_test',
format 'MSSQLSpatial',
config_options 'MSSQLSPATIAL_USE_GEOMETRY_COLUMNS=YES'
);
-- Create schema
DROP SCHEMA IF EXISTS fdw_myname;
CREATE SCHEMA IF NOT EXISTS fdw_myname;
IMPORT FOREIGN SCHEMA ogr_all
FROM SERVER fdw_mssql_myname
INTO fdw_myname
OPTIONS (
launder_table_names 'true',
launder_column_names 'true'
)
;
-- Test
SELECT * FROM fdw_semerap."a_test" LIMIT 1;
FYI: Drag and drop of layers to mssql works via the qgis browser