QGIS Python Console SQL Server Layer Incorrect Attributes

Question

I am using the QGIS python console to load a SQL Server layer in QGIS using the follow code:

uri = "MSSQL:{MYSERVER};database={MYDATABASE};tables= MYTABLE};trusted_connection=yes"
lpBatch = QgsVectorLayer(uri_lp, "PREM_LP_Batch", "ogr")
QgsProject.instance().addMapLayer(lpBatch)

It loads fine, and I can browse the layer fine. However, upon attempting to save any changes made to the geometries, I get the following error:

Layer PREM_INT_Batch: OGR error setting feature 170: Error updating feature with FID:170, [22001][Microsoft][SQL Server Native Client 11.0]String data, right truncation(0)

I have found the data types/lengths of the attributes are incorrect. I believe this is leading to the truncation error, as it thinks the values of the fields are too long.

If I load the layer using the UI, the data types/lengths of the attributes are as they should be, and I can save any changes I make to the geometries fine.

Any idea how I can load the attributes correctly through the python console?

Answer 1

I have found a solution. Loading the layer using QgsDataSourceUri loads the layer using the correct attribute table, and allows editing and saving of geometries:

uri = QgsDataSourceUri()
uri.setConnection({HOST},{PORT}, {DB}, {UID}, {PWD})
uri.setUseEstimatedMetadata(1)
uri.setSrid("27700")
uri.setWkbType(QgsWkbTypes.Polygon)
uri.setDataSource({SCHEMA},{TABLE},{GEOMETRY COLUMN})
intFull = QgsVectorLayer(uri.uri(), "Azure_INT_FULL", "mssql")
QgsProject.instance().addMapLayer(intFull)