locked
SQL Server 2005 linked to SQL Server 2008 with Geospatial Datatypes RRS feed

  • Question

  • Hi

    I am trying to create a Linked Server in SQL 2005 to a SQL 2008.

     First i created the linked server using the SQL Server Manager but when i run a query the following message appears

    OLE DB provider "SQLNCLI" for linked server "servergeo" returned message "Invalid character value for cast specification".
    OLE DB provider "SQLNCLI" for linked server "servergeo" 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 "servergeo".


    I also create the linked server using the next sentences

    exec sp_addlinkedserver 'servergeo', '', 'SQLNCLI', NULL, NULL, 'SERVER=192.168.1.,1434', NULL
    exec sp_addlinkedsrvlogin 'servergeo', 'false', NULL, 'sa', 'xxxxxx' 

    also got the same message.

    please somebody ???
    • Edited by Casey Billett Tuesday, October 13, 2009 9:38 PM Renaming title
    • Moved by Casey Billett Tuesday, October 13, 2009 9:39 PM Spatial related question (From:SQL Server Data Access)
    Monday, January 19, 2009 8:11 PM

Answers

  • I believe this is an unsupported scenario.  Moving this thread to the Engine forum for confirmation.

    • Proposed as answer by Casey Billett Tuesday, October 13, 2009 9:37 PM
    • Marked as answer by tanoshimiEditor Wednesday, January 13, 2010 12:00 PM
    Tuesday, October 13, 2009 9:37 PM

All replies

  • Did anyone have any luck with this?  I'm getting this, as well.  The local server's collation is "SQL_Latin1_General_CP1250_CI_AS" and I'm trying to query a database on another server (through a linked server) that's of collation, "SQL_Latin1_General_CP1_CI_AS". 

    How the heck did I get HERE?
    Tuesday, September 29, 2009 9:07 PM
  • Do the tables you are querying in the 2008 database contain either the geography or geometry datatype column types?

    These types were introduced in 2008 and, as much as I can tell, would not be available on 2005.

    Please post back if this is not the case.

    • Proposed as answer by Casey Billett Tuesday, November 3, 2009 10:42 PM
    Tuesday, September 29, 2009 9:46 PM
  • If you could post table declarations and queries you are running it would help a lot.
    Friday, October 2, 2009 3:08 AM
  • I have a similar issue.  The table in 2008 database has a geography column.  When query the table from 2005 database, I got the same error.  Is there a way to fix it?

    Thanks!
    Wednesday, October 7, 2009 1:33 PM
  • I believe this is an unsupported scenario.  Moving this thread to the Engine forum for confirmation.

    • Proposed as answer by Casey Billett Tuesday, October 13, 2009 9:37 PM
    • Marked as answer by tanoshimiEditor Wednesday, January 13, 2010 12:00 PM
    Tuesday, October 13, 2009 9:37 PM
  • As per Oleg's post, can you please post your table structure / query. Are you trying to query the geography column directly, or are you, for example, trying to return the binary representation using STAsBinary()?
    geography and geometry datatypes are new in SQL Server 2008, so I'm not surprised that you can't query it from SQL 2005. However, you might be able to get the serialized binary, the WKT text representation, or the XML (GML) representation. However, you won't be able to do much with these when you get them into SQL Server 2005 unless you're planning implementing your own spatial methods there... what are you trying to achieve here?
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, October 14, 2009 7:45 AM
    Answerer
  • Have you tried setting it up to use OPENQUERY instead of querying the data directly? As several people have noted, you can't query spatial data from 2005. You could use the pass-through mechanism to run the query and use the .STAsText function to set the spatial data to a text field for return to 2005.
    Wednesday, October 14, 2009 1:23 PM
  • Create a view for the table that only returns types that exist in the SQL version of the other database/server. I use geography's ".ToString()" method in my view and it works great!
    Friday, March 16, 2018 5:20 PM