"Invalid character value for cast specification" for linked 2008 SQL server in 2005 instance

Question

I am attempting to create a linked server from a 2005 to 2008 Microsoft SQL Server. I do this regularly for 2005 instances, but this is the first step of my long journey into SQL 2008. I am able to create the linked server as any other linked server, I receive no errors, however any time I try to use the linked server for anything (a simple "SELECT *" statement, for example) I get this error in SSMS:

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

What do I need to know about creating a linked server to a 2008 instance in a 2005 instance?

Answer 1

I suspect that this may be a collation issue.

Check that the collation is the same at the server, database and table levels.

To check the detault server collation run the following T-SQL:

exec sp_helpsort

To check the Databasea collation do the following:

SELECT DATABASEPROPERTYEX('DatabaseName', 'Collation') SQLCollation;

Answer 2

It's either collation (my first guess), or Unicode conversions (VARCHAR vs NVARCHAR). I'd upvote John, but I don't have enough reputation.

Answer 3

Turns out the tables I kept choosing to test, the most business important tables on the 2008 server, each had fields of the "geography" data type, which is new to 2008. When testing queries on one of the other tables without this datatype the query works correctly.

So...you know... it was...an "Invalid character value for cast specification" after all.

Answer 4

Hi,

I am having some issues while creating Linked server from SQL 2005 to SQL 2008. Following is the error I received. I have searched a lot and haven't found anything on internet where people have successfully done Linked server from 2005 to 2008. You seems to be one of the very few. Can you let me know if there is some configurations I need to change in order to do so.

Regards Deepak