Truncate Data Problem With OPENQUERY

Hi,

I have an application that uses a Cache data base. The data is exposed to sql server via a linked server.

In the Cache database they have data columns that can be up to 32395 characters long. When I do a simple select query like this:

select Intervention_text from cwsavpmtest_live..system.Intervention_data

… I get the error message:

Server: Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column '[cwsavpmtest_live]..[system].[Intervention_data].intervention_text' from the OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: Requested conversion is not supported.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].

I figured out that I could get around the problem with an OPENQUERY like this:

SELECT *
FROM OPENQUERY(cwsavpmtest_live, 'select left(Intervention_text,32395) from Intervention_data’)

The error goes away, but I only get the first 255 characters of the data. I don’t really need all 32395 chars (I figure 4000 chars would be acceptable).

This is not a display problem in Query Analyzer. I set the maximum characters per column to 8000 on the Results tab of the Option dialog. Also I have a stored procedure that is called by Reporting Services and it is only sending 255 characters to the report.

The stored procedure has code that looks like this:

CREATE TABLE #Intervention_Data
(ID varchar(254),
FACILITY Int,
INTID Int,
Intervention_Text varchar(4000),
Intervention_Name varchar(50))

SELECT @QueryString =
'INSERT INTO #Intervention_Data SELECT * FROM OPENQUERY(' + @LinkedServer +
',''select ID, FACILITY, INTID, left(intervention_text ,4000), ' +
'Wiley_Intervention_ID from Intervention_Data where facility = ' +
cast(@FACILITY as varchar(10)) + ''')'

EXEC(@QueryString)

I have tried making the Intervention_Text column in the temp table both "text" and "ntext" data type with the same result.

Does anybody have any clues about how I can make this work?

Thanks,

Laurie

Note: When I link to the Cache database using Access, Access interprets the long fields as being of type "Memo" and I can see all of the data.

edited because tap and tab are not the same word.