Importing Tables from MSSQL to QGIS with no geometry - Point Data X Y

Question

I am trying to import point data from a MSSQL database to QGIS. Is it possible to declare some SQL to create the geometry (X,Y) on the fly? The MSSQL table importer does have an SQL column on the side, but i'm not sure of the correct syntax to get this to work or if its even possible?

('POINT('+convert(nvarchar(24),convert(decimal(18,4), p.y)) + '  ' + convert(nvarchar(24),convert(decimal(18,4), p.x))+')') as geomtext

Answer 1

Couldn't use the MSSQL table importer, but alternatively achieved a similar result with a DSN connection and a VRT file.

1. Add DSN connections using "C:\Windows\SysWOW64\odbcad32.exe"
2. User DSN/Add Tab..
3. "SQL Server Native Client 10.0"
4. Name:
5. Description:
6. Server:
7. Login: "Not all SQL Servers will have Intergrated Windows authentication, so type username and password here if you know it"
8. Change the default database to: "You must chose the database you wish to use, otherwise QGIS gets all the system databases instead"
9. Click Finish and then test the data source "TEST COMPLETED SUCCESSFULLY"
10. You could also create this DSN with a *.bat file using the code below

REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources" /V "<ConnectionName>" /T reg_sz /D "SQL Server Native Client 10.0" /F
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<ConnectionName>" /V "Database" /T reg_sz /D "<SqlDatabaseItem>" /F
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<ConnectionName>" /V "Description" /T reg_sz /D "<ConnectionName>" /F
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<ConnectionName>" /V "Driver" /T reg_sz /D "C:\WINDOWS\SysWOW64\sqlncli10.dll" /F
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<ConnectionName>" /V "LastUser" /T reg_sz /D "" /F
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<ConnectionName>" /V "Server" /T reg_sz /D "<SQLHostName>" /F
REG ADD "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<ConnectionName>" /V "Trusted_Connection" /T reg_sz /D "Yes" /F

12. Now create a VRT file as shown below. Copy the CODE into notepad and save as *.vrt

<OGRVRTDataSource>
<OGRVRTLayer name="--Chose Layer Name--">
    <SrcDataSource>ODBC:"DatabaseName","DatabaseLayer"</SrcDataSource>
<SrcLayer>"DatabaseLayer"</SrcLayer>
<GeometryType>wkbPoint</GeometryType>
    <LayerSRS>"EPSG:23586(CRS or EPSG code)"</LayerSRS>
<GeometryField encoding="PointFromColumns" x="X field Name" y="Y FieldName"/>
</OGRVRTLayer>
</OGRVRTDataSource>

Finally drag in the VRT to QGIS to see your points