Baffling Error Message on Mod to View

[shaunk]

SQl Server 2000.
The original view is this:

SELECT  * 
FROM OPENQUERY	( CONNELLY7,
		'select  *
		from 		IMRU_test.dbo.CAxF CAxf 
		inner join 	IMRU_test.dbo.CAxF_ONI ONI on 
				CAxF_Client_Id = ONI_Client_Id
				and CAxF_Creation_Date_Time = ONI_Creation_Date_Time
		
		'
		)CAxF
inner join
      ( SELECT 
		
		max(CAxF_Creation_Date_Time)  as  CAxF_Max_Creation_Date_Time,
		CAxF_Client_Id as CAxF_Max_Client_Id 
	  FROM   
		OPENQUERY(CONNELLY7,'select * from IMRU_test.dbo.CAxF')
	  GROUP BY 
		CAxF_Client_Id ) CAxF_Max 
ON 	CAxF.CAxF_Client_Id = CAxF_Max.CAxF_Max_Client_Id  and 
	CAxF.CAxF_Creation_Date_Time = CAxF_Max.CAxF_Max_Creation_Date_Time

Essentially it picks up the most current CAxF and joins it with the rest of the relevant tables. The view is called by a stored procedure with no problems.

I then add the following bolded code to the view

SELECT  [b] top 1 [/b] * 
FROM OPENQUERY	( CONNELLY7,
		'select  *
		from 		IMRU_test.dbo.CAxF CAxf 
		inner join 	IMRU_test.dbo.CAxF_ONI ONI on 
				CAxF_Client_Id = ONI_Client_Id
				and CAxF_Creation_Date_Time = ONI_Creation_Date_Time
		[b]left join 	IMRU_test.dbo.CAxF_Classification_Table Class_Table on
				ONI_SOR = ONI_Code [/b]		
		'
		)CAxF
inner join
      ( SELECT 
		
		max(CAxF_Creation_Date_Time)  as  CAxF_Max_Creation_Date_Time,
		CAxF_Client_Id as CAxF_Max_Client_Id 
	  FROM   
		OPENQUERY(CONNELLY7,'select * from IMRU_test.dbo.CAxF')
	  GROUP BY 
		CAxF_Client_Id ) CAxF_Max 
ON 	CAxF.CAxF_Client_Id = CAxF_Max.CAxF_Max_Client_Id  and 
	CAxF.CAxF_Creation_Date_Time = CAxF_Max.CAxF_Max_Creation_Date_Time

The additional code calls a reference table that is joined
on fields of type varchar(25).

When the stored procedure is run with the additional code added to the view, the following error message is posted:

'Only text pointers are allowed in work tables, never text, ntext, or image columns. The query processor produced a query plan that required a text, ntext, or image column in a work table.'

The reference table does not have image or text data. The primary table does.
Any ideas appreciated.

 

[amrita418]

Use convert function to change the column(s) that currently have text to varchar field and the query should work fine.

I hope those specific columns are smaller than 8000 characters else try using substring function.

Regards,
AA

 

[shaunk]

Apologies for taking so long to get back to this.

The view works when the Top clause is removed. So the problem is not related to the additional table (Class_Table) added to the query. In any case, that table does not have text or image data.
The image fields I have need to be returned as Image data, not varchar, so I am not sure about your suggestion to convert them to Varchar ?

So the problem remains around the TOP clause ?

Thanks