sorting problem with linked server

[js]

I am using SQL Server 2000. I am getting the following error when
executing the following query. The query joins a view
[Server2].dbx.dbo.vwreports that resides on a linked server. I can sort
on fields on D table or R view individually, but some fields take a
long time to sort even they are on non-cluster indexes. I can not sort
on any field in table E, K, and L at all. Also I tried to save this
query as a view in the local server. SQL Server does not allow
creating a view base on this query.

Does anyone know why?

===== Error here =======
Microsoft OLE DB Provider for SQL Server error '80040e14'
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.

===== Query here =======
SELECT D.*, DATEADD(s, R.open_date, '12/31/1969 08:00:00pm') AS
openDate, DATEADD(s, R.close_date, '12/31/1969 08:00:00pm') AS
closeDate, R.c_last_name AS custLastName, R.c_first_name AS
custFirstName, R.description, (DATEDIFF(s, DATEADD(s, R.open_date,
'12/31/1969 08:00:00pm'), getdate()))/(60*60*24) as lapse, E.[LAST
NAME] AS techLastName, E.[FIRST NAME] AS techFirstName, E.EMPLOYEEID AS
techID, K.MAKE, K.MAKEID, L.MODEL, L.MODELID, R.customer AS customer,
R.iorg_name AS org FROM dbo.DellIDI D LEFT OUTER JOIN dbo.EMPLOYEE E on
D.technicianid = E.employeeid LEFT OUTER JOIN dbo.MAIN M ON D.Serial =
M.[S/N] LEFT OUTER JOIN dbo.MAKE K ON M.MAKE = K.MAKEID LEFT OUTER JOIN
dbo.MODEL L ON M.MODEL = L.MODELID INNER JOIN
[Server2].dbx.dbo.vwreports R ON D.RequestID = R.ref_num order by
(DATEDIFF(s, DATEADD(s, R.open_date, '12/31/1969 08:00:00pm'),
getdate()))/(60*60*24)

[Erland Sommarskog]

js (an********@yahoo.com) writes:

I am using SQL Server 2000. I am getting the following error when
executing the following query. The query joins a view
[Server2].dbx.dbo.vwreports that resides on a linked server. I can sort
on fields on D table or R view individually, but some fields take a
long time to sort even they are on non-cluster indexes. I can not sort
on any field in table E, K, and L at all. Also I tried to save this
query as a view in the local server. SQL Server does not allow
creating a view base on this query.

Not having access to your tables and views, and with no information
they look, it is difficult to assess the problem. But judging from
the error messages, there are BLOB columns (text/ntext/image) involved
somewhere, either directly in the query or in the view. If you can,
try to exlucde these columns from the equation, to see if you have
better luck.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp