Querying Spatial data from SQL 2005 RRS feed

  • Question

  • On one the SQL 2008 R2 machines, we have some Spatial application installed which has created a spatial database with bunch of tables (GISAdmin is the name of schema for the tables). Now the users are trying to query the tables using a linked server from a SQL 2005 machine.

    They are getting below error:

    OLE DB provider "SQLNCLI" for linked server "SQL2008R2" returned message "Invalid character value for cast specification".

    OLE DB provider "SQLNCLI" for linked server "SQL2008R2" returned message "Invalid character value for cast specification".

    Msg 7330, Level 16, State 2, Line 1

    Cannot fetch a row from OLE DB provider "SQLNCLI" for linked server " SQL2008R2 ".

    I got few questions:

    1. I am assuming the error is due to conversion from spatial. 
    2. Can anyone give me some info if querying spatial tables from SQL2005 is possible or it cant be done at all ? 
    3. Does the error mean any other issue?
    4. What is the solution in this case. 

    Any help is really appreciated.

    Monday, January 23, 2012 11:12 AM

All replies

  • 1. Yes

    2. You can't (directly) query spatial data in SQL 2005 - geography and geometry types were only introduced in SQL Server 2008

    3. No

    4. You can (probably) retrieve the Well-Known Text of the column by executing the ToString() method in the query on the linked (2008) server. This will return an nvarchar text string to the (2005) client. But that's not going to be a whole lot of use because you won't be able to do much with it on a 2005 instance other than look at it...

    twitter: @alastaira blog: http://alastaira.wordpress.com/
    • Proposed as answer by Peja Tao Wednesday, January 25, 2012 1:37 AM
    Monday, January 23, 2012 1:20 PM
  • The query fails even if the client tries to retrieve just an integer column in a query from a table with spatial columns. Is it how it supposed to work or expected error I mean ?

    I setup a new linked server on a different machine which is running SQL 2008 R2 (for testing). If I run a query from this server, I get an error (eg: Select * from LinkedServer.Database.Schema.Table  OR Select objectID from LinkedServer.Database.Schema.Table)

    Msg 7325, Level 16, State 1, Line 1

    Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object '"Database"."Schema"."Table"'.

    I modified the query after reading some online material to:
    SELECT * FROM OPENQUERY([LinkedServer], 'SELECT * from [Database].[Schema].[Table]');

    and this does provide an output. Does it mean that the queries always need to use OPENQUERY (or pass-through query)  ?

    Monday, January 23, 2012 11:06 PM