Limit to length of string displayed in SELECT statement

Limit to length of string displayed in SELECT statement

Post by Dan Newlan » Fri, 12 Jul 2002 22:05:27



Hi.

I am concatonating a number of different fields together
to create an email message for use with xp_sendmail.  

When the length of a column string reaches 255 (256?)
characters, the remainder of the string is lost.  Is
there any way to get string values longer than this to
display?  I have worked around this problem by creating a
new SELECT statement every few lines, but this is hardly
an elegant solution...

I am just using a normal SELECT, with default datatypes.  
Putting in a DECLARE and setting a different or longer
datatype has not seemed to work, but I may just be using
the wrong type.  

Any suggestions on this?

Thanks in advance,

Dan

 
 
 

Limit to length of string displayed in SELECT statement

Post by Narayana Vyas Kondredd » Fri, 12 Jul 2002 22:29:27


See if this helps: http://vyaskn.tripod.com/sql_server_tools_faq.htm#q1

--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


Hi.

I am concatonating a number of different fields together
to create an email message for use with xp_sendmail.

When the length of a column string reaches 255 (256?)
characters, the remainder of the string is lost.  Is
there any way to get string values longer than this to
display?  I have worked around this problem by creating a
new SELECT statement every few lines, but this is hardly
an elegant solution...

I am just using a normal SELECT, with default datatypes.
Putting in a DECLARE and setting a different or longer
datatype has not seemed to work, but I may just be using
the wrong type.

Any suggestions on this?

Thanks in advance,

Dan

 
 
 

Limit to length of string displayed in SELECT statement

Post by Dan Newlan » Fri, 12 Jul 2002 22:42:11


That is the odd thing.  

I turned on that option in Query Analyzer when I was
originally designing the SELECT statement, and it seemed
everything ran fine.  

When I then tried to run the SELECT in either a Stored
Procedure or a SQL Job, though, the 256 character limit
returned, because of course I was no longer using the
Query Analyzer interface.  Somehow, I need to find a way
to turn that option on in the script, so that it will
duplicate the functionality of the "Maximum characters
per column" option.  

Cheers,

Dan

 
 
 

Limit to length of string displayed in SELECT statement

Post by Michael Abraha » Sat, 13 Jul 2002 00:06:58


Please post your code so we can look at it - there is no obvious reason why
the code in an SP would truncate character fields.

Mike Abraham

Quote:> That is the odd thing.

> I turned on that option in Query Analyzer when I was
> originally designing the SELECT statement, and it seemed
> everything ran fine.

> When I then tried to run the SELECT in either a Stored
> Procedure or a SQL Job, though, the 256 character limit
> returned, because of course I was no longer using the
> Query Analyzer interface.  Somehow, I need to find a way
> to turn that option on in the script, so that it will
> duplicate the functionality of the "Maximum characters
> per column" option.

> Cheers,

> Dan

 
 
 

1. String length limits hit in SQL statement

I think this is what is happening. I am executing or trying to execute
very long SQL statements in my VB app but now I am running into the
physical limits of VB's String type. Does anyone know a work around?

Here are some of my code segments:

sReturn = "SELECT BillPeriod, BilledNumber, CallDate, ConnectTime, " & _

        "RateClass, FromNumber, FromPlace, CalledNumber, " & _
        "CalledPlace, CallDuration, Minutes, " & _
        "CallCount, Amount, DataFileName as 'Source' " & _
        "FROM Data "
...
sReturn = sReturn + "WHERE BillPeriod = '" & sBillingPeriod & "'"
...
sReturn = sReturn + " AND (CallDate BETWEEN #" & Format(tbFromDate.Text,
"mm/dd/yy") & "# AND #" & Format(tbToDate.Text, "mm/dd/yy") & "#)"

If I use a SELECT * in place of the long SELECT statement shown above
everything works fine but when I try to use the SELECT statement shown
above my SQL statement gets cut off.

Thanks,

Dave

2. Using compiled codes...

3. 128 character SQL String length limit??

4. 櫓??????

5. SQL string length limit in VB3.0????

6. Use of exec() for SQL statements

7. SQL string length limit in VB 4?

8. Long Island - Oracle/Sybase on NT and Unix clients

9. limit on length of strings passed to dbms_sql?

10. Long Transaction and String length limit

11. FW: Long Transaction and String length limit

12. ODBC SQL Statement length limit of 64k???

13. SQL statement length limit?