I have made a successful connection with MS SQL Server in QGIS 3. I can see all of the tables. I know with QGIS 2 and PostgreSQL you had to use DB Manager to import a shapefile as a table. Well, DB Manager doesn't have the option for MSSQL… Is there an alternative plugin?
QGIS Shapefile – Import Shapefile as a Table in MS SQL Server Using QGIS 3
qgisshapefile
Related Solutions
I have a script that I use to populate the table, which works, kinda, mostly... but I'm not allowed to post it...
But, it uses the Top 1 from each table, and uses this: SP_GEOMETRY.STGeometryType() to get the geometry type of one entry.
Here's what I use, which works:
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
)
and I have entries like this:
f_table_catalog f_table_schema f_table_name f_geometry_column coord_dimension srid geometry_type
GISDB dbo Accuracy SP_GEOMETRY 1 28355 LineString
GISDB dbo AssetAreas SP_GEOMETRY 2 28355 Polygon
QGIS can read these and gives me a list of our ~50 layers instantly.
I also added my SRID to another table 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]
entry like this:
srid auth_name auth_srid srtext proj4text
28355 epsg 28355 +proj=utm +zone=55 +south +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs PROJCS["GDA94 / MGA zone 55",GEOGCS["GDA94",DATUM["Geocentric_Datum_of_Australia_1994",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6283"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4283"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",147],PARAMETER["scale_factor",0.9996],PARAMETER["false_easting",500000],PARAMETER["false_northing",10000000],AUTHORITY["EPSG","28355"],AXIS["Easting",EAST],AXIS["Northing",NORTH]]
Note that you can get these from spatialreference.org, like this: http://spatialreference.org/ref/epsg/28355/postgis/, just modify it for SQL Server syntax.
Yes, you can edit both spatial and non-spatial PostGIS tables within QGIS.
- Make sure your PostgresSQL DB is PostGIS enabled
- Use the Add PostGIS layers button to first make a connection to a PostgresSQL db
- Next, use the Connect button to connect to DB to add both spatial and non-spatial tables
- Finally, select the layer in the QGIS TOC, then click the Toggle Editing button to start the edit session (you can draw new features, delete features, and open the attribute table to update record values)
Here are some links that give more details:
Best Answer
You can use the in-built browser panel. Just drag and drop a layer (either from your project, or from somewhere else in the browser) onto a schema in the mssql connection to copy the data across.