By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,636 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Help with large text fields please

P: 2
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:

Expand|Select|Wrap|Line Numbers
  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?
Jun 7 '06 #1
Share this Question
Share on Google+
1 Reply


P: 93
You could try using nvarchar
Nov 6 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.