Load and display a layer from SQL Server using PyQGIS

Question

I'm having a go at using pyqgis from within the QGIS python console to load a layer from SQL Server and display it. I feel like I'm part way there but I'm having a real issue getting the layer to display.

I've loaded the layer, it appears in the layers dock but nothing appears in the map window (even after selecting zoom to layer extent and checking styles). What's also weird is that if I open attributes there is data in there and I can save the layer as a shapefile, reopen it and it appears fine. Is there something I'm missing? Here's my code:

from PyQt4.QtCore import QFileInfo,QSettings
from qgis.core import QgsRasterLayer, QgsCoordinateReferenceSystem
from qgis.gui import *
from PyQt4.QtCore import *

s = QSettings()
oldValidation = str(s.value( "/Projections/defaultBehaviour", "useGlobal" ))
s.setValue( "/Projections/defaultBehaviour", "useGlobal" )

uri = "MSSQL:server=My_Server;database=My_Database;tables=dbo.My_View;trusted_connection=yes"
vlayer = QgsVectorLayer(uri, "my_table", "ogr")

vlayer.setCrs( QgsCoordinateReferenceSystem(4326, QgsCoordinateReferenceSystem.EpsgCrsId) )

s.setValue( "/Projections/defaultBehaviour", oldValidation )

QgsMapLayerRegistry.instance().addMapLayer(vlayer)

Answer 1

Put this end of you code last line:

QgsMapLayerRegistry.instance().addMapLayer(vlayer)

If it also is not working. You probably unchecked the Render off in bottom status bar just after where co-ordinates shown:

Answer 2

I believe the issue is that your table "dbo.My_View" is not defined in a GEOMETRY_COLUMNS table in the MSSQL database. If you use OGR2OGR or Qgis to import a layer into MSSQL, that will automatically create the table for you. Once you have it, fill out the proper values for the table you are trying to add to QGIS. In this case, "dbo.My_View". Once you add those values to GEOMETRY_COLUMNS, your features should start showing up in the map.