- A remote SQL server (2008 R2) that I have access to (But NOT full control of) which contains my source data including OSGB 1936 Geometry [ESPG: 27700]
- In house SQL Server 2008 R2
- A Crystal Report Automator in house that allows me to send multiple variants of a report in PDF format to multiple recipients (500+)
limited comprehension of CLR Package installations!
Because of an anomalous data rationale during the build phase I have a need to connect two tables of spatial data in order to get a full realisation of the required report.
I can't get this done on the on the remote server for a variety of logistical reasons.
I had attempted to build Views (of the simplest
Select * from mytable variety) that were giving me a degree of success in SQL when I invoked the
OPENQUERY option, which had functioned but now are returning me:
The OLE DB provider "SQLNCLI10" for linked server "xx.xxx.xx.xxx\MSSQL2008R2" supplied inconsistent metadata for a column. The column "Geom" (compile-time ordinal 4) of object "SELECT ColimnID, Column2, Column3, Geom, Column4, Column5, Column6, Column7, Column8, Column9, Column10 FROM remotedata.dbo.mytable " was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.
In the absence of being able to perform the joins within SQL directly because of this I'm instead trying to do some of the geo-manipulation in the Crystal context. This however is presenting a secondary set of challenges; what could ordinarily be expressed as
now needs to be
or at least in that construct I can get it to work when entered as a SQL expression field within Crystal.
Unfortunately the really thorny Join that I need to perform
mytable as a INNER JOIN mybasereference AS b ON b.geom.STContains(a.Geom)=1
and I have absolutely come to a dead end as to how (or even IF) this is theoretically possible within Crystal let alone how to make it read correctly for Crystal to process.
I'm agnostic as to where I invoke this join within the entire processing as it only provides me with a reference- but a necessary one
Is there any way that I can overcome the incomplete Schema-Error logic error [and still perform the join that I need]? (The remote server controller has informed me that they have made no changes on their side and that it must be an issue with my server)
Is there any way to perform a spatial join like this in Crystal?