Max characters in Subreport on ADP

[Drew]

I am using ADP to build reports for some SQL Server apps we have. I am
having an issue with a subreport truncating the text returned from the
database. In our interfaces, we aren't having this problem, in SQL Server
Enterprise Manager, no problems, but in our ADP it truncates it. The
datatype of the column is varchar(7500), but it only shows ~4000 characters
of the field. Is this a limitation of Access?

Thanks,
Drew

 

[Sylvain Lafontaine]

Hum, maybe the SET TEXTSIZE option is causing you trouble. Try setting it
to a bigger value in your SP or queries.

You can also try using a DLookup for these text fields and, very important
for some older versions, you should put any text fields at the end of the
select statement; ie:

Select a, b, c, textfield from ...

instead of:

Select a, textfield, b, c from ...

I will try to make some tests later on this issue.

 

[Drew]

I tried a little test of my own, I setup an ASP page to return the same
Stored Proc and it returned all of the data, so I am sure that the problem
lies in the ADP. I think it is odd that there isn't a concrete maximum, for
one of the columns that is varchar(5000) the column actually takes up 4034
characters, for the other column that is varchar(7500), the column actually
takes up 4047 characters. I would have thought that if there were a limit
this would be consistent.

Thanks,
Drew

 

[Sylvain Lafontaine]

With SQL-Server, there is a limitation of 8080 bytes per record for the sum
of all fields. Access use Unicode, so this should give us something like a
limit of 4000 caracters per record but this number will not be exact and
will change because of the presence of the other fields.

However, I don't have any ADP project at this moment with a field this
large, so I cannot tell you for sure what's happening here. I will try to
make some tests on my side but I'm already busy with my own problems ;-)

Which version of ADP you are using?

 

[Drew]

I now see what you are saying, I will try to pull a total character count
for the record and see. I am currently using Access 2002 SP2.

Thanks,
Drew

 

[Sylvain Lafontaine]

Did you try placing the text field at the end of the Select statement?

 

[Sylvain Lafontaine]

Oups, sorry, I made a small error: the limitation of the total number of
bytes for a record is 8080 but because of the overhead, the effective
limitation for the user is 8060 bytes.

 

[Drew]

Yes I did, there is only 1 text field and it is at the end of the statement.

Thanks,
Drew

 

[Drew]

The total characters returned using the SP is 38350, but they are broken
into separate tables that do not exceed 8000 bytes.

Thanks,
Drew

 

[Drew]

I think I have found the reason, ADP will not allow for a Varchar to be
bigger than 4000 characters, but if the column is changed to text,
everything is fine.

I will just change mine to text and it should be good.

Thanks,
Drew

 

[Sylvain Lafontaine]

Thanks for the info!

Myself, I usually use nvarchar instead of varchar, so I'm already limited to
a length of 4000 caracters on the SQL-Server side before going to ntext.
However, I would have tought that Access would have make a better job for
accessing a varchar field of a length greater than 4000; even knowing that
Access use Unicode internally.

Have you made the test to see if this problem exists only with reports and
subreports or if it's still there for forms and subforms too?

 

[Drew]

I have not tested the forms or subforms at all, just the reports. I only
use ADP for reporting, I use ASP on our local intranet for the interfaces of
the database. If I were a betting man, I would bet that this holds true for
the forms also, but as I said I have not tested this (and I have already
deleted my test sps and test ADP).

Thanks for your help!
Drew