[mapserver-users] OGR & MSSQL Server on specified Port Number fails to connect

[David Martin]

Hi,
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'. <br>
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 my connection fails.

Is there a resolution to this or is there another way to execute stored procedures with MapServer layers?

David
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20121106/3cb0bb02/attachment-0001.html>