OGR & MSSQL Server on specified Port Number fails to connect

[itbeyond]

I have a geodata source on SQLServer 2008 and have been using for a number of years the msplugin_mssql2008.dll plugin. I now want to use some stored procedures in my SQL server to render the geo data to MapServer. So I have been working with the OGR driver however I have found what I think is a problem when the SQL server is on a none standard TCP port as the comma in the server definition gets thrown away when rendering the layer using a map file via shp2img. I can use ogrinfo without problem but as soon as I carry the string to the OGR layer in the .map file and run shp2img –all_debug 1 I see that the tcp port of my SQL server is removed and hence the connection fails.

So the working OGR syntax is:

ogrinfo "MSSQL:uid=xxx;pwd=xxx;database=xxx;Integrated Security=false;server=MY_WEB-SqlServer.com,4670;tables=PointPlace_Waypoints(Position)" -summary
INFO: Open of MSSQL:uid=xxx;pwd=xxx;database=xxx;Integrated Security=false;server=MY_WEB-SqlServer.com,4670;tables=PointPlace_Waypoints(Position)' using driver MSSQLSpatial' successful. 1: PointPlace_Waypoints

NOTE: The TCP Port is 4670 – this is the same as used in the mssql2008.dll plug in.

When I use shp2img this is what I get:

shp2img -m latest.map -o test.png -all_debug 1
msDrawMap(): rendering using outputformat named png (AGG/PNG8).
msOGRFileOpen(MSSQL:uid=xxx;pwd=xxx;database=xxx;Integrated Security=false;server=MY_WEB-SqlServer.com,4670)...
OGROPen(MSSQL:uid=xxx;pwd=xxx;database=xxx;Integrated Security=false;server=MY_WEB-SqlServer.com)
msOGRFileOpen(): OGR error. Open failed for OGR connection in layer `latest1'.
Unable to initialize connection to the server for MSSQL:uid=xxx;pwd=xxx;database=xxx;Integrated Security=false;server=MY_WEB-SqlServer.com,
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
msDrawMap(): Image handling error. Failed to draw layer named 'latest1'.
msDrawMap(): Image handling error. Failed to draw layer named 'latest1'.
msOGRFileOpen(): OGR error. Open failed for OGR connection in layer `latest1'.
Unable to initialize connection to the server for MSSQL:uid=xxx;pwd=xxx;database=xxx;Integrated Security=false;server=MY_WEB-SqlServer.com,
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

You will notice that two lines: msOGRFileOpen(MSSQL:uid=xxx;pwd=xxx;database=xxx;Integrated Security=false;server=MY_WEB-SqlServer.com,4670)...
OGROPen(MSSQL:uid=xxx;pwd=xxx;database=xxx;Integrated Security=false;server= MY_WEB-SqlServer.com)

The system is calling msOGRFileOpen with a ,4670 but the OGROPen has removed the comma and tcp port number hence the connection fails.

[tamas]

This issue can be worked around by quoting the connection string in the mapfile, something like:

LAYER

CONNECTIONTYPE OGR CONNECTION '"MSSQL:uid=xxx;pwd=xxx;database=xxx;Integrated Security=false;server=MY_WEB-SqlServer.com,4670;tables=PointPlace_Waypoints(Position)"'

END

This issue appears to be a MapServer related problem anyway.

[tamas]

Closing now