0

I have:

  • 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+)
  • Crystal XI

  • 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

mytable.geom.STX

now needs to be

geometry::STGeomFromText(CONVERT(nvarchar(50),"mytable"."Geom"), 0).STX

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

  1. 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)

  2. Is there any way to perform a spatial join like this in Crystal?

  • 1
    sorry but we can't answer a question that is not there and I doubt that a solution is going to flow out of this question as stated. The explanation is good but please clarify and simplify and state 1 question per question. – Brad Nesom Jul 29 '15 at 16:24
  • It's one question that appears to have 3 possible answers all of which are confounding me? If anyone of you that have deemed this as Off-topic can confirm that any of my suggested pathways are unworkable; that too would be a response and would help me narrow the question down to one solution. There is a CLEAR GIS Topic here because it all rates to a geospatial Join – Richard Colwill Jul 30 '15 at 7:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.