Help with large text fields please

[medusa]

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:

SELECT TechAnomalies.*,
			lab1.LabRef AS labref1, lab1.LabName AS labname1, lab1.EULabRef AS EULabRef,
			lab2.LabRef AS labref2, lab2.LabName AS labname2,
			u1.UserFullName AS RaisedBy,
			u2.UserFullName AS Inter,
			u3.UserFullName AS SignOffBy,
			u4.UserFullName AS LastEditor,
			u5.UserFullName AS LQM
		FROM		dbo.TechAnomalies
		INNER JOIN	dbo.Labs AS lab1
		ON		lab1.LabID = TechAnomalies.TALabID 
		LEFT OUTER JOIN	dbo.Labs AS lab2
		ON		lab2.LabID = TechAnomalies.TAIntermedLabID 
		LEFT OUTER JOIN	dbo.Users AS u1
		ON		u1.UserID = TechAnomalies.TARaiserUserID
		LEFT OUTER JOIN	dbo.Users AS u2
		ON		u2.UserID = TechAnomalies.TAIntermedUserID 
		LEFT OUTER JOIN	dbo.Users AS u3
		ON		u3.UserID = TechAnomalies.TASignedOffBy 
		LEFT OUTER JOIN	dbo.Users AS u4
		ON		u4.UserID = TechAnomalies.TALastEditedBy 
		LEFT OUTER JOIN	dbo.Users AS u5
		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?

[Taftheman]

You could try using nvarchar