R: [UMN_MAPSERVER-USERS] SQLServer 2005 via ovf

[Piero Cavalieri]

Bho, I have a connection like this:

CONNECTIONTYPE OGR
  CONNECTION "<OGRVRTDataSource><OGRVRTLayer
name='poi'><SrcDataSource>ODBC:xxx/xxx at gisdevSql2005</SrcDataSource>
<SrcLayer>top025sc</SrcLayer>
<GeometryField encoding='WKB' field='the_geom'/>
<SrcSQL>SELECT * FROM top025sc WHERE TOP_CL = 'T110' or TOP_CL = 'T115'
</SrcSQL>
</OGRVRTLayer></OGRVRTDataSource>"

And a System DSN and it's working. 

Here is different:
<SrcDataSource>ODBC:xxx/xxx at gisdevSql2005</SrcDataSource>

Where gisdevSql2005 is the DSN, 
And u have:
<SrcDataSource>ODBC:usr/pwd at ourdb,table_name</SrcDataSource>

Which is different syntax. Try as my syntax, maybe it works.

Cheers
 Piero

-----Messaggio originale-----
Da: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] Per conto di Frank Warmerdam
Inviato: giovedì 26 luglio 2007 18.52
A: MAPSERVER-USERS at LISTS.UMN.EDU
Oggetto: Re: [UMN_MAPSERVER-USERS] SQLServer 2005 via ovf

Josh Hevenor wrote:
> I'm trying to connect to a SQLServer 2005 table through OVF with little 
> success...
> 
>  
> 
> My OVF file
> 
>  
> 
>             <OGRVRTDataSource>
> 
>     <OGRVRTLayer name="banner_location">
> 
>         <SrcDataSource>ODBC:usr/pwd at ourdb,table_name</SrcDataSource>
> 
>                   <SRCLayer>table_name</SRCLayer>
> 
>                               <GeometryType>wkbPoint</GeometryType>
> 
>                               <GeometryField encoding="PointFromColumns" 
> x="LONGITUDE" y="LATITUDE"/>
> 
>     </OGRVRTLayer>
> 
> </OGRVRTDataSource>
> 
>  
> 
> Our mapfile just has a background (province) polygon layer and this 
> point layer. When I use shp2img there is no error reported but my points 
> aren't on the image.
> 
>  
> 
>             shp2img -m geoportal.map -o testing.png
> 
> msDrawMap(): Layer 0 (Province), 0.172s
> 
> msDrawMap(): Layer 1 (Major Roads), 0.046s
> 
> msDrawMap(): Layer 2 (our_layer), 7.157s         // the ovf layer
> 
> msDrawMap(): Drawing Label Cache, 0.000s
> 
> msDrawMap() total time: 7.375s 
> 
>  
> 
> I can use mapserv.exe to draw a map when the ovf layer is turned off. 
> When I attempt to connect through mapserver (mapscript, php) I get the 
> following error
> 
>  
> 
>             [MapServer Error]: msOGRFileOpen(): Open failed for OGR 
> connection `..\..\common\data\ovf\our_layer.ovf'.
> 
> Unable to initialize ODBC connection to DSN for usr/pwd at ourdb,table_name,
> 
> [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a 
> connection to SQL Server [1326].
> 
>  
> 
> When I use the 'Sql Server' odbc driver in the connection I get the 
> following for line 3 instead of the Named Pipes stuff
> 
>  
> 
>             [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does 
> not exist or access denied.

Josh,

This seems like permissions problems.  I don't quite "get" the permissions
model under windows, but I'd suggest:

  1) make sure your DSN is a system dsn, not a user dsn.
  2) Investigate what you need to do to let your web user have
     permission to use the DSN.

BTW, 7 seconds to render the points layer with shp2img is pretty
terrible.  Are you sure you want this to work?  There are some
mechanisms to have the spatial query passed down to SQL Server to
be evaluated against the table.  This might improve performance
substantially.  Contact me off list if you can't find enough info
in the OVF howto on mapserver.gis.umn.edu or in the OGR VRT driver
docs.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | President OSGeo, http://osgeo.org