SQL Server Error "Invalid attempt to GetBytes on column"

Question

Little background:

Recently I upgraded "SQL server management studio 2005" by applying the patch from MS link http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=7218

So that, I can access the recently built DB server which are in SQL Server 2008 version.

I have a server link created on the 2008 Db server which connects to a Oracle DB. earlier I didn't faced any issue but after applying the management studio patch whenever I run a query using the server link as below

select top 10 * from [server]..DBNAME.TABLENAME 

OR

select * from [server]..DBNAME.TABLENAME 

I get the error

An error occurred while executing batch. Error message is: Invalid attempt to GetBytes on column colname'. The GetBytes function can only be used on columns of type Text, NText, or Image."

But if I change the query to

select top 10 col1,col2 from [server]..DBNAME.TABLENAME 

OR

select distinct col1, col2, col3 from [server]..DBNAME.TABLENAME 

It runs fine without any issue.

I am not sure but my believe is it's something to do with Management Studio ... some setting in Management Studio probably causing this issue.

Does anyone have any idea Or have faced this issue before. Can you please help.

EDIT: If I run the problem query using sqlcmd it runs fine.

Thanks.

Answer 1

I've got that error before when using SQL 2005 management studio to a SQL 2008 server and querying a table with the DATE datetype in it.

Essentially it means that SSMS doesn't understand one of the column data types coming back to it. You said SELECT * doesn't work but SELECT col1, col2, col3 does. How many columns in the table? Try expanding the select column by column until you find the one that's confusing Management Studio . Then you can use CAST in the select clause to turn it into something Management Studio does understand.

What's probably happening here is that one of the columns is being returned from SQL 2008 as DATE, TIME, DATETIME2 or one of the other new SQL 2008 data types. While Management Studio 2005 can connect to a SQL 2008 server, it doesn't understand the new data types and cannot display them.

It's a limitation of Management Studio.