Help with large text fields please

I have the following problem (MS SQL 2000):
A modest table with several large fields (currently varchar(5000)), plus some datetime and integer fields recording who's done what and when.

Two problems - (1) I now realise that I'm limited to 8060 characters, and (2) users seem to think even 5000 chars might be too small on occasions (the table is for recording laboratory problems, so the amount of text depends on what the local quality manager finds!)

I thought I'd change my varchars to text. However, when I changed just one of them to text, the record set being returned by my stored procedure has lots of empty fields. The query ran OK before the datatype change and STILL runs OK in Enterprise Manager after the change.

The basic query is:

Code: ( text )

1. SELECT TechAnomalies.*,

2. lab1.LabRef AS labref1, lab1.LabName AS labname1, lab1.EULabRef AS EULabRef,

3. lab2.LabRef AS labref2, lab2.LabName AS labname2,

4. u1.UserFullName AS RaisedBy,

5. u2.UserFullName AS Inter,

6. u3.UserFullName AS SignOffBy,

7. u4.UserFullName AS LastEditor,

8. u5.UserFullName AS LQM

9. FROM dbo.TechAnomalies

10. INNER JOIN dbo.Labs AS lab1

11. ON lab1.LabID = TechAnomalies.TALabID

12. LEFT OUTER JOIN dbo.Labs AS lab2

13. ON lab2.LabID = TechAnomalies.TAIntermedLabID

14. LEFT OUTER JOIN dbo.Users AS u1

15. ON u1.UserID = TechAnomalies.TARaiserUserID

16. LEFT OUTER JOIN dbo.Users AS u2

17. ON u2.UserID = TechAnomalies.TAIntermedUserID

18. LEFT OUTER JOIN dbo.Users AS u3

19. ON u3.UserID = TechAnomalies.TASignedOffBy

20. LEFT OUTER JOIN dbo.Users AS u4

21. ON u4.UserID = TechAnomalies.TALastEditedBy

22. LEFT OUTER JOIN dbo.Users AS u5

23. ON u5.UserID = TechAnomalies.TALQMReviewBy

I use it with or without a WHERE clause (passed to the stored procedure as a varchar) to return either a recordset or the details of one record.

Any suggestions please?

You could try using nvarchar