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

2
votes

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?

4
sqlsql-serversql-server-2005sql-server-2008linked-server
When you say "use the linked server" what exactly are you doing that results in an error being raised i.e. are you executing a particular T-SQL batch or stored procedure? The error would appear to indicate that a cast operation is failing. - John Sansom
I'm not doing any casting in any commands. I'm talking about executing something as simple as 'SELECT * FROM linkedServer.database.dbo.table' returns that OLE DB error. - kscott
In that case can you provide the Link Server definition please. - John Sansom
can you give the build versions (or service pack levels) for your 2005 and 2008 builds? - Kev Riley

4 Answers

2
votes

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.

0
votes

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;
0
votes

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

0
votes

Was there a particular way that you were able to query the table on the linked server that had the geography fields and not get the error?

I have the same issue where I need to query a linked server and some of the tables have geography fields in them and even if I only select a text field I get the error. The only workaround that I can think of would be to split the geography fields off to new tables so that the queries to the tables don't break.