So it appears I have a SQL/ T-SQL problem

Results 1 to 3 of 3

Thread: So it appears I have a SQL/ T-SQL problem

  1. #1
    David Dickens Guest

    Default So it appears I have a SQL/ T-SQL problem

    I was absolutely sure I had a Response.Write problem, but I just did some "testing" of my system and it appears that I have a SQL (or more importantly a T-SQL problem).<BR><BR>It cuts off my varchar(8000) field to 255 when it returns from<BR>SELECT fieldname FROM tablename<BR><BR>Can I add something to my T-SQL statement to have it not truncate the column?

  2. #2
    Join Date
    Dec 1969

    Default Not TSQL...ADO limitation??

    Until just last week, I thought this happened only in Access, but I found out I was wrong...<BR><BR>To use fields longer than 255 characters with ADODB.RecordSet, follow two rules:<BR><BR>(1) Be sure that your long fields are the *last* fields in the RecordSet. If this means you must code "SELECT field1,field7,field3" explicitly instead of "SELECT *", then so be it.<BR><BR>(2) Only "read" the value of each such field *ONE TIME*. That is, code like this WILL NOT WORK:<BR><BR>&#060;%<BR>If RS("longField") &#060;&#062; "" Then<BR>&nbsp; &nbsp; Response.Write RS("longField")<BR>End If<BR>%&#062;<BR><BR>Instead, you *must* do<BR><BR>&#060;%<BR>val = RS("longField")<BR>If val &#060;&#062; "" Then<BR>&nbsp; &nbsp; Response.Write val<BR>End If<BR>%&#062;<BR><BR>****************<BR><BR>NOTE: This has solved problems with Access DBs in the past. No guarantees it will fix them with SQL Server, but it sure can&#039t hurt to try!<BR><BR>

  3. #3
    Scott S Guest

    Default RE: Not TSQL...ADO limitation??

    I know with SQL Server you can only have 8000 characters in a row, strange but I&#039ve heard it from very experienced DBAs. So if you have a table like this:<BR><BR>FIELDONE VARCHAR(2000)<BR>FIELDTWO VARCHAR(8000)<BR><BR>You wouldnt be able to get the full 8000 on the second field, that is assuming that the first field actually contained the full 2000 characters.<BR><BR>Its strange but seems to be true, I think it has to do with a hard coded limit on the Page size for SQL Server (thats just a theory). I usually use TEXT for large strings, the TEXT datatype stores a pointer on the page (so its not governed by that limitation) i think it can be GBs in size. <BR><BR>That may or may not be whats causing the problem though, something to think about when you store VARCHAR(8000) though.<BR><BR>Hope that helps<BR><BR>Scott S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts