SQL Server 2005 linked to SQL Server 2008 with Geospatial Datatypes

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 ???

Answer 1

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?

Answer 2

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.

Answer 3

If you could post table declarations and queries you are running it would help a lot.

Answer 4

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!

Answer 5

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

Answer 6

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

Answer 7

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.