OGR With SrcSQL Problem

[Pascoe,Tim [Burlington]]

I currently have a Virtual Layer defined through OGR, which pulls a series of points from a SQL-Server database using a pre-defined View. However, I would like to make this query dynamic, by employing the technique described in the Virtual Spatial Data wiki entry (bottom of entry). I took the SQL code for the original View, and replaced the SrcLayer entry with an SrcSQL entry, wrapping the SQL text in single quotes as a start. There is no WHERE clause yet so no embeded quotations, but I get an error stating that the SQL failed, or did not return any records. Since this is exactly the same SQL string as is used in the view, I'm not sure where to trouble-shoot.

The CONNECTION data is shown below, minus the login credentials.

CONNECTION
'<OGRVRTDataSource>
    <OGRVRTLayer name="CABINPoints">
        <SrcDataSource>ODBC:****/****@CABIN</SrcDataSource>
        <SrcSQL>'SELECT DISTINCT dbo.tblstudylist.studyid, dbo.tblstudylist.study AS study_name, dbo.tblsite.siteid, dbo.tblsitedetail.sitedetailsid, dbo.tblsite.site, 
                      dbo.tblsitedetail.description AS site_description, dbo.tblsite.name, dbo.tblsite.basin, 
                      dbo.tbllocation.latdegree + dbo.tbllocation.latminute / 60.0 + dbo.tbllocation.latsecond / 3600.0 AS latitude, 
                      (dbo.tbllocation.londegree - dbo.tbllocation.lonminute / 60.0) - dbo.tbllocation.lonsecond / 3600.0 AS longitude, dbo.tblsite.streamorder, dbo.tblecoregion.ecoregion, dbo.tblsamplingdevice.description AS sampling_device, dbo.tblsitedetail.julianday AS julian_day, dbo.tblsitedetail.[year]
                      FROM dbo.tblsitedetail INNER JOIN
                      dbo.tblsite ON dbo.tblsitedetail.site_id = dbo.tblsite.siteid INNER JOIN
                      dbo.tbllocation ON dbo.tblsitedetail.sitedetailsid = dbo.tbllocation.sitedetails_id INNER JOIN
                      dbo.tblstudylist ON dbo.tblsite.study_id = dbo.tblstudylist.studyid INNER JOIN
                      dbo.tblecoregion ON dbo.tblsite.ecoregion_id = dbo.tblecoregion.ecoregionID INNER JOIN
                      dbo.tblsamplingdevice ON dbo.tblsitedetail.device_id = dbo.tblsamplingdevice.deviceid'</SrcSQL>
	  <FID>sitedetailsid</FID>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
    <GeometryField encoding="PointFromColumns" x="longitude" y="latitude"/>
    </OGRVRTLayer>
</OGRVRTDataSource>'

The error returned is:

<?xml version='1.0' encoding="ISO-8859-1" standalone="no" ?>
<!DOCTYPE ServiceExceptionReport SYSTEM "http://schemas.opengeospatial.net/wms/1.1.1/exception_1_1_1.dtd">
<ServiceExceptionReport version="1.1.1">
<ServiceException>
msDrawMap(): Image handling error. Failed to draw layer named 'CABINPoints'.
msOGRFileOpen(): OGR error. Open failed for OGR connection `C:/Inetpub/wwwroot/cabin/Maps/CABINPoints.ovf'.
SQL statement failed, or returned no layer result:
"SELECT DISTINCT dbo.tblstudylist.studyid, dbo.tblstudylist.study AS study_name, dbo.tblsite.siteid, dbo.tblsitedetail.sitedetailsid, dbo.tblsite.site, 
                      dbo.tblsitedetail.description AS site_description, dbo.tblsite.name, dbo.tblsite.basin, 
                      dbo.tbllocation.latdegree + dbo.tbllocation.latminute / 60.0 + dbo.tbllocation.latsecond / 3600.0 AS latitude, 
                      (dbo.tbllocation.londegree - dbo.tbllocation.lonminute / 60.0) - dbo.tbllocation.lonsecond / 3600.0 AS longitude, dbo.tblsite.streamorder, 
                      dbo.tblecoregion.ecoregion, dbo.tblsamplingdevice.description AS sampling_device, dbo.tblsitedetail.julianday AS julian_day, 
                      dbo.tblsitedetail.[year]
                      FROM dbo.tblsitedetail INNER JOIN
                      dbo.tblsite ON dbo.tblsitedetail.site_id = dbo.tblsite.siteid INNER JOIN
                      dbo.tbllocation ON dbo.tblsitedetail.sitedetailsid = dbo.tbllocation.sitedetails_id INNER JOIN
                      dbo.tblstudylist ON dbo.tblsite.study_id = dbo.tblstudylist.studyid INNER JOIN
                      dbo.tblecoregion ON dbo.tblsite.ecoregion_id = dbo.tblecoregion.ecoregionID INNER JOIN
                      dbo.tblsamplingdevice ON dbo.tblsitedetail.device_id = dbo.tblsamplingdevice.deviceid"

</ServiceException>
</ServiceExceptionReport>


Thanks for any suggestions,

Timothy Pascoe
Environmental Systems Scientist

Environment Canada / Environnement Canada
Canada Centre for Inland Waters / Centre Canadien des Eaux Intérieures
867 Lakeshore Road / 867, chemin Lakeshore
Burlington, Ontario / Burlington (Ontario)
L7R 4A6

Tel/Tél: (905) 336-6239
Fax/Téléc: (905) 336-4699
E-mail/C. élec: tim.pascoe at ec.gc.ca
Government of Canada/Gouvernement du Canada
This e-mail represents the opinions and views solely held by its author and in no manner may be considered as representing those of his/her employer.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20060920/b1329ec8/attachment.htm>