<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6618.4">
<TITLE>OGR With SrcSQL Problem</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P><FONT SIZE=2 FACE="Arial">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.</FONT></P>
<P><FONT SIZE=2 FACE="Arial">The CONNECTION data is shown below, minus the login credentials.</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">CONNECTION</FONT>
<BR><FONT SIZE=2 FACE="Courier New">'<OGRVRTDataSource></FONT>
<BR><FONT SIZE=2 FACE="Courier New"> <OGRVRTLayer name="CABINPoints"></FONT>
<BR><FONT SIZE=2 FACE="Courier New"> <SrcDataSource>ODBC:****/****@CABIN</SrcDataSource></FONT>
<BR><FONT SIZE=2 FACE="Courier New"> <SrcSQL>'SELECT DISTINCT dbo.tblstudylist.studyid, dbo.tblstudylist.study AS study_name, dbo.tblsite.siteid, dbo.tblsitedetail.sitedetailsid, dbo.tblsite.site, </FONT></P>
<P><FONT SIZE=2 FACE="Courier New"> dbo.tblsitedetail.description AS site_description, dbo.tblsite.name, dbo.tblsite.basin, </FONT>
<BR><FONT SIZE=2 FACE="Courier New"> dbo.tbllocation.latdegree + dbo.tbllocation.latminute / 60.0 + dbo.tbllocation.latsecond / 3600.0 AS latitude, </FONT></P>
<P><FONT SIZE=2 FACE="Courier New"> (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]</FONT></P>
<P><FONT SIZE=2 FACE="Courier New"> FROM dbo.tblsitedetail INNER JOIN</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> dbo.tblsite ON dbo.tblsitedetail.site_id = dbo.tblsite.siteid INNER JOIN</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> dbo.tbllocation ON dbo.tblsitedetail.sitedetailsid = dbo.tbllocation.sitedetails_id INNER JOIN</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> dbo.tblstudylist ON dbo.tblsite.study_id = dbo.tblstudylist.studyid INNER JOIN</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> dbo.tblecoregion ON dbo.tblsite.ecoregion_id = dbo.tblecoregion.ecoregionID INNER JOIN</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> dbo.tblsamplingdevice ON dbo.tblsitedetail.device_id = dbo.tblsamplingdevice.deviceid'</SrcSQL></FONT>
<BR> <FONT SIZE=2 FACE="Courier New"> <FID>sitedetailsid</FID></FONT>
<BR><FONT SIZE=2 FACE="Courier New"> <GeometryType>wkbPoint</GeometryType></FONT>
<BR><FONT SIZE=2 FACE="Courier New"> <LayerSRS>WGS84</LayerSRS></FONT>
<BR><FONT SIZE=2 FACE="Courier New"> <GeometryField encoding="PointFromColumns" x="longitude" y="latitude"/></FONT>
<BR><FONT SIZE=2 FACE="Courier New"> </OGRVRTLayer></FONT>
<BR><FONT SIZE=2 FACE="Courier New"></OGRVRTDataSource>'</FONT>
</P>
<P><FONT SIZE=2 FACE="Courier New">The error returned is:</FONT>
</P>
<P><FONT SIZE=2 FACE="Courier New"><?xml version='1.0' encoding="ISO-8859-1" standalone="no" ?></FONT>
<BR><FONT SIZE=2 FACE="Courier New"><!DOCTYPE ServiceExceptionReport SYSTEM "</FONT><A HREF="http://schemas.opengeospatial.net/wms/1.1.1/exception_1_1_1.dtd"><U><FONT COLOR="#0000FF" SIZE=2 FACE="Courier New">http://schemas.opengeospatial.net/wms/1.1.1/exception_1_1_1.dtd</FONT></U></A><FONT SIZE=2 FACE="Courier New">"></FONT>
<BR><FONT SIZE=2 FACE="Courier New"><ServiceExceptionReport version="1.1.1"></FONT>
<BR><FONT SIZE=2 FACE="Courier New"><ServiceException></FONT>
<BR><FONT SIZE=2 FACE="Courier New">msDrawMap(): Image handling error. Failed to draw layer named 'CABINPoints'.</FONT>
<BR><FONT SIZE=2 FACE="Courier New">msOGRFileOpen(): OGR error. Open failed for OGR connection `C:/Inetpub/wwwroot/cabin/Maps/CABINPoints.ovf'.</FONT>
<BR><FONT SIZE=2 FACE="Courier New">SQL statement failed, or returned no layer result:</FONT>
<BR><FONT SIZE=2 FACE="Courier New">"SELECT DISTINCT dbo.tblstudylist.studyid, dbo.tblstudylist.study AS study_name, dbo.tblsite.siteid, dbo.tblsitedetail.sitedetailsid, dbo.tblsite.site, </FONT></P>
<P><FONT SIZE=2 FACE="Courier New"> dbo.tblsitedetail.description AS site_description, dbo.tblsite.name, dbo.tblsite.basin, </FONT>
<BR><FONT SIZE=2 FACE="Courier New"> dbo.tbllocation.latdegree + dbo.tbllocation.latminute / 60.0 + dbo.tbllocation.latsecond / 3600.0 AS latitude, </FONT></P>
<P><FONT SIZE=2 FACE="Courier New"> (dbo.tbllocation.londegree - dbo.tbllocation.lonminute / 60.0) - dbo.tbllocation.lonsecond / 3600.0 AS longitude, dbo.tblsite.streamorder, </FONT></P>
<P><FONT SIZE=2 FACE="Courier New"> dbo.tblecoregion.ecoregion, dbo.tblsamplingdevice.description AS sampling_device, dbo.tblsitedetail.julianday AS julian_day, </FONT></P>
<P><FONT SIZE=2 FACE="Courier New"> dbo.tblsitedetail.[year]</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> FROM dbo.tblsitedetail INNER JOIN</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> dbo.tblsite ON dbo.tblsitedetail.site_id = dbo.tblsite.siteid INNER JOIN</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> dbo.tbllocation ON dbo.tblsitedetail.sitedetailsid = dbo.tbllocation.sitedetails_id INNER JOIN</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> dbo.tblstudylist ON dbo.tblsite.study_id = dbo.tblstudylist.studyid INNER JOIN</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> dbo.tblecoregion ON dbo.tblsite.ecoregion_id = dbo.tblecoregion.ecoregionID INNER JOIN</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> dbo.tblsamplingdevice ON dbo.tblsitedetail.device_id = dbo.tblsamplingdevice.deviceid"</FONT>
</P>
<P><FONT SIZE=2 FACE="Courier New"></ServiceException></FONT>
<BR><FONT SIZE=2 FACE="Courier New"></ServiceExceptionReport></FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Courier New">Thanks for any suggestions,</FONT>
</P>
<P><FONT COLOR="#000080" FACE="Comic Sans MS">Timothy Pascoe</FONT>
<BR><FONT COLOR="#000080" SIZE=1 FACE="Comic Sans MS">Environmental Systems Scientist</FONT>
<BR>
<BR><FONT COLOR="#800080" SIZE=2 FACE="Verdana">Environment Canada / Environnement Canada<BR>
Canada Centre for Inland Waters / Centre Canadien des Eaux Intérieures<BR>
867 Lakeshore Road / 867, chemin Lakeshore<BR>
Burlington, Ontario / Burlington (Ontario)</FONT>
<BR><FONT COLOR="#800080" SIZE=2 FACE="Verdana">L7R 4A6<BR>
<BR>
</FONT><FONT COLOR="#008000" SIZE=2 FACE="Trebuchet MS">Tel/Tél: (905) 336-6239<BR>
Fax/</FONT><SPAN LANG="en-ca"><FONT COLOR="#008000" SIZE=2 FACE="Trebuchet MS">Téléc</FONT></SPAN><SPAN LANG="en-us"><FONT COLOR="#008000" SIZE=2 FACE="Trebuchet MS">: (905) 336-4699<BR>
E-mail/C. élec: </FONT></SPAN><A HREF="mailto:tim.pascoe@ec.gc.ca"><SPAN LANG="en-us"><U><FONT COLOR="#0000FF" SIZE=2 FACE="Trebuchet MS">tim.pascoe@ec.gc.ca</FONT></U></SPAN></A><SPAN LANG="en-us"><BR>
</SPAN><SPAN LANG="en-ca"><FONT COLOR="#000080" SIZE=2 FACE="Tahoma">Government of Canada/Gouvernement du Canada</FONT></SPAN>
<BR><I><SPAN LANG="en-us"><FONT COLOR="#808080" SIZE=1 FACE="Comic Sans MS">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.</FONT></SPAN></I><SPAN LANG="en-us"></SPAN></P>
<BR>
</BODY>
</HTML>