January 9, 2003 at 11:58 am
As luck would have it I have a deadline, and I'm going to probably turn in the report short of what I wanted, Below is the error that I am getting for a sproc that I wrote, the complicated sproc is joined to a view internally such as
Select Stuff From (Select More stuff) as Sprocs Inner Join vwMYView On VwID = SproID, I added the line that I want it to sort by the contactname in the view... such as
Order By vwC.ContactName
Now I get this problem, I can sort by other fields, but the ContactName is a problem, can I hit the sproc so that I can get around this problem????
thanks 😀
Server: Msg 8626, Level 16, State 1, Procedure stp_OPENxTabCallCenterRpt_Details, Line 3
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.
-Francisco
-Francisco
January 9, 2003 at 12:07 pm
Based on the error message, it seems you are trying to sort by a column of TEXT or NTEXT type. It is not possible. Convert to VARCHAR or NVARCHAR and sort by that:
Order By CAST( vwC.ContactName AS VARCHAR( 8000 ) )
January 9, 2003 at 2:16 pm
As for the qork table thing it is due to the ORDER BY in general. Small tables and sometimes easily fetched larger sets with good indexes will be sorted in memory. But depending on various factores a work table will be generated to accomidate the order by.
What some folks suggested to me once was either select without the text/ntext/image field or do all the other field in a subquery then inner join the text field data in (since I didn't need the text field I dropped it). You can also sometimes see this if the width of the query generates a table larger than 8000bytes wide with an ORDER BY.
January 10, 2003 at 9:25 am
Thanks for posting such helpful replies, I will try casting the column again, but I know for a fact that it IS a varchar type... but I suppose it wouldn't hurt to be explicit right? also how can I check how wide the row is?
Thanks again
-Francisco
-Francisco
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply