title: Maximum length is 8000 in microsoft sql server management studio
body: <p>So i have an error found in my procedure where my query string part of the <code>OPENQUERY</code> is exceeding the maximum defined limits of 8000 characters. What can I do?</p>
  
  <p><strong>The error</strong></p>
  
  <blockquote>
    <p>The character string that starts with 'select distinct UPPER (case when cast(i.attendantaddress as varchar(30)) is null then i.specialnotes else cast(i.attendantaddress' is too long. Maximum length is 8000.--</p>
  </blockquote>
  
  <p><strong>The stored procedure</strong></p>
  
  <pre><code>CREATE procedure [dbo].[proc_GetFreePatients1_20apr]
  
  as
  
  declare @temp table(CONTROLNO varchar(max), UHID varchar(250),PATIENTNAME varchar(250),AGE INT,GENDER VARCHAR(1),ADRESS varchar(500),CONTACTNUMBER varchar(20),IPID varchar(20),BED varchar(20),REASONFORADMISSION varchar(max),DIAGNOSIS varchar(max),ADMITDATETIME datetime,DOCTOR varchar(150),
  
  SPECIALITY varchar(500),REFERREDDOCTOR varchar(500),RECOMMENDEDBY varchar(500),EMERGENCYCOLDCASE varchar(500),WARD varchar(150))
  
  insert @temp
  
  select * from openquery(ahdelwards,'select distinct UPPER (case when cast(i.attendantaddress as varchar(30)) is null then i.specialnotes else cast(i.attendantaddress as varchar(30)) end) CONTROLNO,pm.registrationno,
  
        rp.firstname||'' ''||rp.middlename||'' ''||rp.lastname as Patientname,rp.age,
  
        (case when rp.gender=71 then ''F''
  
        else ''M'' end ) GENDER,
  
        ad.address1||'',''||ad.address2||'',''||c.cityname||'',''||sm.statename ADRESS,
  
  (case when ad.residencenumber is null then
  
  (case  when ad.emergencynumber is null then ad.mobilenumber
  
  when ad.emergencynumber is not null then ad.emergencynumber
  
    when ad.mobilenumber is null then ad.emergencynumber
  
         when ad.mobilenumber is not null then ad.mobilenumber end) else ad.residencenumber end) CONTACTNUMBER,
  
  i.inpatientno,
  
  bm.bedcode,
  
  I.REASONFORADMISSION,
  
  (select dt.parametervalue
  
      from wards.dischargesummdtls dt
  
     where dt.parammappingid in
  
           (select pm.parammappingid
  
  from wards.parametermapping pm
  
             where pm.parameterid = 1299)
  
       and dt.status = 1
  
       and dt.dischargeno in
  
           (select dy.dischargeno
  
  from wards.dischargesumm_dync dy
  
             where dy.ipno = i.inpatientno)) diagnosis,
  
  i.dateofadmission,
  
         e.FIRSTNAME||'' ''||e.MIDDLENAME||'' ''||e.LASTNAME as doctor,
  
       f_getspeciality(i.admittingdoctor)Speciality,
  
           f_getemployeename(i.referreddoctor)referreddoctor,
  
            UPPER(i.wardnurse) RECOMMENDEDBY,
  
            UPPER(i.housekeeping) EMERGENCYCOLDCASE,
  
  l.leveldetailname
  
    from billing.pateintpolicydetails pd
  
    left join billing.patientpolicymaster pm
  
      on pd.policymasterid = pm.patientpolicymasterid
  
     and pd.policystatus = 1
  
     and pd.aggrementid = 60429
  
    join adt.inpatientmaster i
  
      on i.uhid = pm.registrationno
  
    join registration.patient rp
  
      on rp.uhid = i.uhid
  
      join
  
  registration.addressmaster ad  on rp.registrationid=ad.registrationid and ad.addresstypeid=''2'' and ad.status=''1''
  
  inner join
  
  ehis.countrymaster cm on cm.countrycode=ad.country
  
  inner join
  
  ehis.statemaster sm on sm.statecode=ad.state and sm.status=''1''  
  
  inner join 
  
  ehis.districtmaster dm on dm.districtcode=ad.district and dm.status=''1'' and dm.stateid=sm.stateid
  
  inner join 
  
  ehis.citymaster c on c.citycode=ad.city and  c.status=''1''  and  c.districtid=dm.districtid
  
    join adt.bedmaster bm
  
      on bm.bedid = i.bedid
  
    join hr.employee_main_details e
  
      on e.employeeid = i.admittingdoctor
  
    join adt.leveldetail l
  
      on l.leveldetailid = bm.leveldetailid
  
  where pm.locationid = 10701
  
     and i.locationid = 10701
  
     and i.status not in (7,11) 
  
  union
  
  select distinct UPPER (case when cast(i.attendantaddress as varchar(30)) is null then i.specialnotes else cast(i.attendantaddress as varchar(30)) end) CONTROLNO,pm.registrationno,
  
  rp.firstname||'' ''||rp.middlename||'' ''||rp.lastname as Patientname,rp.age,      (case when rp.gender=71 then ''F''
  
        else ''M'' end ) GENDER,
  
  ad.address1||'',''||ad.address2||'',''||c.cityname||'',''||sm.statename ADRESS,
  
  (case when ad.residencenumber is null then
  
  
  
  (case  when ad.emergencynumber is null then ad.mobilenumber
  
  when ad.emergencynumber is not null then ad.emergencynumber
  
    when ad.mobilenumber is null then ad.emergencynumber
  
         when ad.mobilenumber is not null then ad.mobilenumber end) else ad.residencenumber end) CONTACTNUMBER,i.inpatientno,
  
  bm.bedcode,
  
  I.REASONFORADMISSION,
  
  (select dt.parametervalue
  
      from wards.dischargesummdtls dt
  
     where dt.parammappingid in
  
           (select pm.parammappingid
  
  from wards.parametermapping pm
  
             where pm.parameterid = 1299)
  
       and dt.status = 1
  
       and dt.dischargeno in
  
           (select dy.dischargeno
  
  from wards.dischargesumm_dync dy
  
             where dy.ipno = i.inpatientno)) diagnosis,
  
  i.dateofadmission,
  
  e.FIRSTNAME||'' ''||e.MIDDLENAME||'' ''||e.LASTNAME as doctor,
  
     f_getspeciality(i.admittingdoctor)Speciality,
  
      f_getemployeename(i.referreddoctor)referreddoctor,
  
          UPPER(i.wardnurse) RECOMMENDEDBY,
  
            UPPER(i.housekeeping) EMERGENCYCOLDCASE,
  
  l.leveldetailname
  
    from billing.pateintpolicydetails pd
  
    full outer join billing.patientpolicymaster pm
  
      on pd.policymasterid = pm.patientpolicymasterid
  
     and pd.policystatus = 1
  
    join adt.inpatientmaster i
  
      on i.uhid = pm.registrationno
  
    join registration.patient rp
  
      on rp.uhid = i.uhid
  
  join
  
  registration.addressmaster ad  on rp.registrationid=ad.registrationid and ad.addresstypeid=''2'' and ad.status=''1''
  
  inner join
  
  ehis.countrymaster cm on cm.countrycode=ad.country
  
  inner join
  
  ehis.statemaster sm on sm.statecode=ad.state and sm.status=''1''  
  
  inner join 
  
  ehis.districtmaster dm on dm.districtcode=ad.district and dm.status=''1'' and dm.stateid=sm.stateid
  
  inner join 
  
  ehis.citymaster c on c.citycode=ad.city and  c.status=''1''  and  c.districtid=dm.districtid
  
    join adt.bedmaster bm
  
      on bm.bedid = i.bedid
  
    join hr.employee_main_details e
  
      on e.employeeid = i.admittingdoctor
  
    join adt.leveldetail l
  
      on l.leveldetailid = bm.leveldetailid
  
     and l.leveldetailid not in (221,257)
  
  where i.freepatient = 1
  
     and pm.locationid = 10701
  
     and i.locationid = 10701
  
     and i.status not in (7,11) 
  
  union
  
  select distinct  UPPER (case when cast(i.attendantaddress as varchar(30)) is null then i.specialnotes else cast(i.attendantaddress as varchar(30)) end) CONTROLNO,pm.registrationno,
  
  rp.firstname||'' ''||rp.middlename||'' ''||rp.lastname as Patientname,rp.age, (case when rp.gender=71 then ''F''
  
        else ''M'' end ) GENDER,
  
  
  
  ad.address1||'',''||ad.address2||'',''||c.cityname||'',''||sm.statename ADRESS,
  
  (case when ad.residencenumber is null then
  
  (case  when ad.emergencynumber is null then ad.mobilenumber
  
  when ad.emergencynumber is not null then ad.emergencynumber
  
    when ad.mobilenumber is null then ad.emergencynumber
  
         when ad.mobilenumber is not null then ad.mobilenumber end) else ad.residencenumber end) CONTACTNUMBER,
  
  i.inpatientno,
  
  bm.bedcode,
  
  I.REASONFORADMISSION,
  
  (select dt.parametervalue
  
      from wards.dischargesummdtls dt
  
     where dt.parammappingid in
  
           (select pm.parammappingid
  
  from wards.parametermapping pm
  
             where pm.parameterid = 1299)
  
       and dt.status = 1
  
       and dt.dischargeno in
  
           (select dy.dischargeno
  
  from wards.dischargesumm_dync dy
  
             where dy.ipno = i.inpatientno)) diagnosis,
  
  i.dateofadmission,
  
     e.FIRSTNAME||'' ''||e.MIDDLENAME||'' ''||e.LASTNAME as doctor,
  
        f_getspeciality(i.admittingdoctor)Speciality,
  
        f_getemployeename(i.referreddoctor)referreddoctor,
  
            UPPER(i.wardnurse) RECOMMENDEDBY,
  
            UPPER(i.housekeeping) EMERGENCYCOLDCASE,
  
  l.leveldetailname
  
    from billing.pateintpolicydetails pd
  
    full outer join billing.patientpolicymaster pm
  
      on pd.policymasterid = pm.patientpolicymasterid
  
     and pd.policystatus = 1
  
    join adt.inpatientmaster i
  
      on i.uhid = pm.registrationno
  
    join registration.patient rp
  
      on rp.uhid = i.uhid
  
  join
  
  registration.addressmaster ad  on rp.registrationid=ad.registrationid and ad.addresstypeid=''2'' and ad.status=''1''
  
  inner join
  
  ehis.countrymaster cm on cm.countrycode=ad.country
  
  inner join
  
  ehis.statemaster sm on sm.statecode=ad.state and sm.status=''1''  
  
  inner join 
  
  ehis.districtmaster dm on dm.districtcode=ad.district and dm.status=''1'' and dm.stateid=sm.stateid
  
  inner join 
  
  ehis.citymaster c on c.citycode=ad.city and  c.status=''1''  and  c.districtid=dm.districtid
  
    join adt.bedmaster bm
  
     on bm.bedid = i.bedid
  
    join hr.employee_main_details e
  
      on e.employeeid = i.admittingdoctor
  
    join adt.leveldetail l
  
      on l.leveldetailid = bm.leveldetailid
  
     and l.leveldetailid in (221,257)
  
  where i.freepatient = 1
  
     and pm.locationid = 10701
  
     and i.locationid = 10701
  
     and i.status not in (7,11)
  
   ')
  
  
  
  --pd.aggrementid IN (60429,7140)
  
  
  
  select  distinct * from @temp order by 11 asc
  </code></pre>