Mysterious truncation

I have a SP that retrives several fields on a table containing cariage
returns in the data (IE Office Address) Im storing the address data in a
temo table and then returning the resuls of the temp table.
The issue is that many of the addresses seem to get truncated even though
the field in the temp table is defined as varchar(1000) - more then enough
space to hold the data. The fields in the table where the data is coming
from are defined as text(16). I cant use text data type in my SP temp table
b/c I need the DISTINCT ability. However, Im not convined the problem lies
with the table being text type and the SP temp table being varchar(1000). An
y
Ideas as to the problem anyone. Most of the addresses are probably not even
500 characters long. Does the SP npt like cariage returns in the data? I
didnt design the orginal table. Im trying to work around it.
--
JP
.NET Software DevelperAre you basing your analysis on ouput from Query Analyzer? If so, there's a
trick in Query Analyzer - It defaults to truncate all column output to a max
of 256 characters. To change that, go to menu option
Tools, Options, Results, and in middle vertically, you will see
Maximum Characters per column set to 256... Change it to 8000
"JP" wrote:

> I have a SP that retrives several fields on a table containing cariage
> returns in the data (IE Office Address) Im storing the address data in a
> temo table and then returning the resuls of the temp table.
> The issue is that many of the addresses seem to get truncated even though
> the field in the temp table is defined as varchar(1000) - more then enough
> space to hold the data. The fields in the table where the data is coming
> from are defined as text(16). I cant use text data type in my SP temp tabl
e
> b/c I need the DISTINCT ability. However, Im not convined the problem lies
> with the table being text type and the SP temp table being varchar(1000).
Any
> Ideas as to the problem anyone. Most of the addresses are probably not eve
n
> 500 characters long. Does the SP npt like cariage returns in the data? I
> didnt design the orginal table. Im trying to work around it.
> --
> JP
> .NET Software Develper|||This did not solve the problem. It still resulting the truncated data even
though its in the table. If the SP results would have been truncated SQL
would have resulted in an error code b/c data would have been truncated.
"CBretana" wrote:
> Are you basing your analysis on ouput from Query Analyzer? If so, there's
a
> trick in Query Analyzer - It defaults to truncate all column output to a m
ax
> of 256 characters. To change that, go to menu option
> Tools, Options, Results, and in middle vertically, you will see
> Maximum Characters per column set to 256... Change it to 8000
> "JP" wrote:
>|||Then is there some pattern to the truncations? Is the length always the
same? i.e., is it always truncating a tthe same character position? Or is it
always truncating just before the Carriage return Line feed? Look at the
ouput, and try to discern some pattern. That will help steer you in the
right direction to debug this...
"JP" wrote:
> This did not solve the problem. It still resulting the truncated data even
> though its in the table. If the SP results would have been truncated SQL
> would have resulted in an error code b/c data would have been truncated.
> "CBretana" wrote:
>|||First step in an operation like this, run profiler and see what commands are
being sent to the server. This will likely show you your problem.
Otherwise post more information like the DDL of your tables and some data.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"JP" <JP@.discussions.microsoft.com> wrote in message
news:1503FA3A-0FFC-48EB-ADBD-D8CF183C93A3@.microsoft.com...
>I have a SP that retrives several fields on a table containing cariage
> returns in the data (IE Office Address) Im storing the address data in a
> temo table and then returning the resuls of the temp table.
> The issue is that many of the addresses seem to get truncated even though
> the field in the temp table is defined as varchar(1000) - more then enough
> space to hold the data. The fields in the table where the data is coming
> from are defined as text(16). I cant use text data type in my SP temp
> table
> b/c I need the DISTINCT ability. However, Im not convined the problem lies
> with the table being text type and the SP temp table being varchar(1000).
> Any
> Ideas as to the problem anyone. Most of the addresses are probably not
> even
> 500 characters long. Does the SP npt like cariage returns in the data? I
> didnt design the orginal table. Im trying to work around it.
> --
> JP
> .NET Software Develper